Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Help with Forms based on Many to Many relationship

hyperpau's Avatar
Expert
  
Join Date: Jun 2007
Posts: 177
#1: Jul 23 '07
Hi guys. I'm not sure If we are allowed to post questions here. Am sorry if this is not the right place.

I just really need help.

I have a database with Forms and subforms that are bound to a many to many relationship.

How could I import an excel file and automatically enter those
values in the corresponding tables?

NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#2: Jul 24 '07

re: Help with Forms based on Many to Many relationship

This isn't the place to start a technical thread really Hyperpau, but you can post another thread here with a pointer to this one after I've moved it to the forum if you like.
I like the idea that the "staff" should expect the highest level of help from their "colleagues". I'm subscribed now but it's late, so i'll try to look at this again tomorrow.
In the mean-time, try to explain your data structure using this example template for the table Meta-Data :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#3: Jul 24 '07

re: Help with Forms based on Many to Many relationship

Don't bother with the other (pointer) thread. I left a pointer there anyway when I moved the thread :)
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,875
#4: Jul 24 '07

re: Help with Forms based on Many to Many relationship

In truth, I've done this a few times in the past and the only thing that really works is using recordsets. The logic can get a bit complicated but once you work that out its the easiest way to accomplish it.

e.g. if you were dealing with two tables then you would open a recordset as follows:
  • Excel spreadsheet, either imported to a table or linked.
Open a loop for the recordset and read in the first record.

Add field values appropriate to table1 using docmd.runsql on an appropriate insert statement. If your ID key is an autonumber on the table then you will have to somehow retrieve that value after inserting the record maybe using DMax on the autonumber.

Then create an insert statement to add your data to table2. Assuming ID is again an autonumber you would retrieve it as above.

Lastly you would create an insert statement to add the composite primary key of the two primary keys from tables 1 and 2.

Problem here will be working out the logic of how to manage all three logically to deal with the many to many relationship.

Without seeing the excel spreadsheet its hard to say how you would do this.


Mary
hyperpau's Avatar
Expert
  
Join Date: Jun 2007
Posts: 177
#5: Jul 26 '07

re: Help with Forms based on Many to Many relationship

Thanks guys. sorry about posting it in the wrong thread.

Here is the structure.

Expand|Select|Wrap|Line Numbers
  1. Table:  tblTransfer
  2.  
  3. TransferID  ;  Autonumber ; PK
  4. TransferDate; Date
  5. LineItems:  -     This is on a subdatasheet.
  6.        ItemID ; Autonumber:  FK   -  lookup to tblReceive
  7.  
  8.  
  9. Table:  tblLineItems   (Linking table)
  10.  
  11. LineItemsID ; Autonumber ; PK
  12. TransferID ; Numeric ; FK
  13. ReceiveID ; Numberic ; FK
  14.  
  15.  
  16. Table:  tblReceive
  17.  
  18. ReceiveID ; Autonumber ; PK
  19. ReceiveDate ; Date 
  20. Description ; Numeric ; FK    - lookup to tblDescription
  21. Remarks ; String
  22.  
  23.  
  24. Table:  tblDescription
  25.  
  26. DescriptionID ; Autonumber ; PK
  27. Description ; String
  28.  
  29.  
As you see here, tblTransfer and tblReceive are table on a many to many
relationship. Therefore, the tblLineItems is used to link them together.

The excel file that I would like to import looks like this.

Expand|Select|Wrap|Line Numbers
  1. ReceiveDate   Description   Remarks   TransferDate
  2. 01/01/07        Laptop          HP            02/01/07
  3. 01/02/07        Desktop        Dell           02/01/07
  4. 01/20/07        Laptop          IBM           03/01/07
  5. 02/01/07        Printer           Epson       03/10/07
  6. 02/01/07        Desktop        HP            02/01/07
what i want to do is when i import this, all items will be
entered in the tblReceive but all items with the same
TransferDate, should be imported to tblTransfer with their
respective ReceiveIDs imported to tblLineItems.

Hense the table structure in my DB would look like this

Expand|Select|Wrap|Line Numbers
  1. TransferID   TransferDate
  2. 01               02/01/07
  3.                            LineItems:
  4.                            ReceiveID       Description    Remarks
  5.                                01                  Laptop          HP
  6.                                02                  Desktop        Dell
  7.                                05                  Desktop        HP
  8.  
  9. 02              03/01/07
  10.           LineItems:
  11.                                ReceiveID        Description        Remarks
  12.                                03                   Laptop              IBM
  13.  
  14. 03             03/10/07
  15.                            LineItems:
  16.                            ReceiveID         Description        Remarks
  17.                                 04                    Printer               Epson   
Does this make any sense to you guys?
hyperpau's Avatar
Expert
  
Join Date: Jun 2007
Posts: 177
#6: Aug 2 '07

re: Help with Forms based on Many to Many relationship

Quote:

Originally Posted by hyperpau

Thanks guys. sorry about posting it in the wrong thread.

Here is the structure.

Expand|Select|Wrap|Line Numbers
  1. Table:  tblTransfer
  2.  
  3. TransferID  ;  Autonumber ; PK
  4. TransferDate; Date
  5. LineItems:  -     This is on a subdatasheet.
  6.        ItemID ; Autonumber:  FK   -  lookup to tblReceive
  7.  
  8.  
  9. Table:  tblLineItems   (Linking table)
  10.  
  11. LineItemsID ; Autonumber ; PK
  12. TransferID ; Numeric ; FK
  13. ReceiveID ; Numberic ; FK
  14.  
  15.  
  16. Table:  tblReceive
  17.  
  18. ReceiveID ; Autonumber ; PK
  19. ReceiveDate ; Date 
  20. Description ; Numeric ; FK    - lookup to tblDescription
  21. Remarks ; String
  22.  
  23.  
  24. Table:  tblDescription
  25.  
  26. DescriptionID ; Autonumber ; PK
  27. Description ; String
  28.  
  29.  
As you see here, tblTransfer and tblReceive are table on a many to many
relationship. Therefore, the tblLineItems is used to link them together.

The excel file that I would like to import looks like this.

Expand|Select|Wrap|Line Numbers
  1. ReceiveDate   Description   Remarks   TransferDate
  2. 01/01/07        Laptop          HP            02/01/07
  3. 01/02/07        Desktop        Dell           02/01/07
  4. 01/20/07        Laptop          IBM           03/01/07
  5. 02/01/07        Printer           Epson       03/10/07
  6. 02/01/07        Desktop        HP            02/01/07
what i want to do is when i import this, all items will be
entered in the tblReceive but all items with the same
TransferDate, should be imported to tblTransfer with their
respective ReceiveIDs imported to tblLineItems.

Hense the table structure in my DB would look like this

Expand|Select|Wrap|Line Numbers
  1. TransferID   TransferDate
  2. 01               02/01/07
  3.                            LineItems:
  4.                            ReceiveID       Description    Remarks
  5.                                01                  Laptop          HP
  6.                                02                  Desktop        Dell
  7.                                05                  Desktop        HP
  8.  
  9. 02              03/01/07
  10.           LineItems:
  11.                                ReceiveID        Description        Remarks
  12.                                03                   Laptop              IBM
  13.  
  14. 03             03/10/07
  15.                            LineItems:
  16.                            ReceiveID         Description        Remarks
  17.                                 04                    Printer               Epson   
Does this make any sense to you guys?


Mary? I would like to recall this post. :)
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,875
#7: Aug 2 '07

re: Help with Forms based on Many to Many relationship

Quote:

Originally Posted by hyperpau

Mary? I would like to recall this post. :)

Do you mean you want to delete it?

Can I ask why, as this is not common practice.

Mary
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#8: Aug 3 '07

re: Help with Forms based on Many to Many relationship

Quote:

Originally Posted by hyperpau

Mary? I would like to recall this post. :)

What do you mean hyperpau, do you want it deleted?
hyperpau's Avatar
Expert
  
Join Date: Jun 2007
Posts: 177
#9: Aug 4 '07

re: Help with Forms based on Many to Many relationship

Quote:

Originally Posted by mmccarthy

Do you mean you want to delete it?

Can I ask why, as this is not common practice.

Mary

Oh no. sorry. I mean I want to let you guys know that I already made a new post. And may be you forgot about my question. :)
hyperpau's Avatar
Expert
  
Join Date: Jun 2007
Posts: 177
#10: Aug 4 '07

re: Help with Forms based on Many to Many relationship

Quote:

Originally Posted by NeoPa

What do you mean hyperpau, do you want it deleted?

Oh no. sorry. I mean I want to let you guys know that I already made a new post. And may be you forgot about my question. :)
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,875
#11: Aug 4 '07

re: Help with Forms based on Many to Many relationship

LOL!

Sorry hyperpau

I get distracted with Admin duties sometimes and questions can get away from me. Unfortunately I can't give the same attention to the Access forum nowadays as I used to.

If this happens just "bump" the thread and ask for help generally. As I will not always be available to look at it.

Thanks

Mary
puppydogbuddy's Avatar
Expert
  
Join Date: May 2007
Location: Florida
Posts: 1,915
#12: Aug 4 '07

re: Help with Forms based on Many to Many relationship

Quote:

Originally Posted by hyperpau

Hi guys. I'm not sure If we are allowed to post questions here. Am sorry if this is not the right place.

I just really need help.

I have a database with Forms and subforms that are bound to a many to many relationship.

How could I import an excel file and automatically enter those
values in the corresponding tables?

Hyperpau,
It isn't clear from your post whether you are doing this as a one-time conversion of an excel flat file system or whether you need to do this on a recurring basis. Regardless, the link below provides two invaluable tips that will facilitate a one time or recurring transfer from excel in view of your structure:
1. Create an access table for use as a working table that receives the entire excel "dump" and parse your data out to the various tables from this working Access table.
2. Use union queries to facilitate the parsing process.

http://www.fabalou.com/Access/Genera...tion_Howto.asp


Hope this helps.
puppydogbuddy's Avatar
Expert
  
Join Date: May 2007
Location: Florida
Posts: 1,915
#13: Aug 4 '07

re: Help with Forms based on Many to Many relationship

PS: Forgot to mention that you don't need any excel automation to do this. You can use the DoCmd.TransferDatabase method to transfer the excel file to the working table in Access.
Rabbit's Avatar
Expert
  
Join Date: Jan 2007
Location: California
Posts: 3,835
#14: Aug 4 '07

re: Help with Forms based on Many to Many relationship

Why can't you just design the tables like so..
Expand|Select|Wrap|Line Numbers
  1. Table:  tblReceive
  2. ReceiveID ; Autonumber ; PK
  3. ReceiveDate ; Date
  4. TransferDate ; Date
  5. Description ; Numeric ; FK    - lookup to tblDescription
  6. Remarks ; String
  7.  
  8. Table:  tblDescription
  9. DescriptionID ; Autonumber ; PK
  10. Description ; String
  11.  
hyperpau's Avatar
Expert
  
Join Date: Jun 2007
Posts: 177
#15: Aug 6 '07

re: Help with Forms based on Many to Many relationship

Quote:

Originally Posted by Rabbit

Why can't you just design the tables like so..

Expand|Select|Wrap|Line Numbers
  1. Table:  tblReceive
  2. ReceiveID ; Autonumber ; PK
  3. ReceiveDate ; Date
  4. TransferDate ; Date
  5. Description ; Numeric ; FK    - lookup to tblDescription
  6. Remarks ; String
  7.  
  8. Table:  tblDescription
  9. DescriptionID ; Autonumber ; PK
  10. Description ; String
  11.  


thanks. but that's how i designed it first. but it did not meet the qualifications because i needed to assing different transaction numbers for receiving, transferign, returning and releasing individually. :)
hyperpau's Avatar
Expert
  
Join Date: Jun 2007
Posts: 177
#16: Aug 6 '07

re: Help with Forms based on Many to Many relationship

Quote:

Originally Posted by mmccarthy

LOL!

Sorry hyperpau

I get distracted with Admin duties sometimes and questions can get away from me. Unfortunately I can't give the same attention to the Access forum nowadays as I used to.

If this happens just "bump" the thread and ask for help generally. As I will not always be available to look at it.

Thanks

Mary

OK. no problem. Thank you so much. I am actually trying to do what you suggested for the append queries with the dmax. :)
hyperpau's Avatar
Expert
  
Join Date: Jun 2007
Posts: 177
#17: Aug 6 '07

re: Help with Forms based on Many to Many relationship

Quote:

Originally Posted by puppydogbuddy

Hyperpau,
It isn't clear from your post whether you are doing this as a one-time conversion of an excel flat file system or whether you need to do this on a recurring basis. Regardless, the link below provides two invaluable tips that will facilitate a one time or recurring transfer from excel in view of your structure:
1. Create an access table for use as a working table that receives the entire excel "dump" and parse your data out to the various tables from this working Access table.
2. Use union queries to facilitate the parsing process.

http://www.fabalou.com/Access/Genera...tion_Howto.asp


Hope this helps.


Thank you for this. I am fully aware of this normalization process.
How ever I am already done with that. My database is fully functional and done already. I have normalized the tables and created the relationships.

All i need to do now is to put the values from the excel file so that it can be used already. But since it is already normalized, I am having a hard time figuring out a way to import the flat file since my tables are normalized. :)

But no worries, I am doing what mary suggested and I am starting to see light on it. :)
puppydogbuddy's Avatar
Expert
  
Join Date: May 2007
Location: Florida
Posts: 1,915
#18: Aug 6 '07

re: Help with Forms based on Many to Many relationship

Quote:

Originally Posted by hyperpau

Thank you for this. I am fully aware of this normalization process.
How ever I am already done with that. My database is fully functional and done already. I have normalized the tables and created the relationships.

All i need to do now is to put the values from the excel file so that it can be used already. But since it is already normalized, I am having a hard time figuring out a way to import the flat file since my tables are normalized. :)

But no worries, I am doing what mary suggested and I am starting to see light on it. :)

No problems if Mary's solution works for you. However, from your response above, you did not understand what I was trying to convey.......that the easiest way to import the spreadsheet to Access, in view of your table structure, is to import the entire excel spreadsheet to one Access temp table, then parse the data from the temp table out to your normalized tables using union queries like the ones illustrated in the link I provided you.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#19: Aug 6 '07

re: Help with Forms based on Many to Many relationship

Quote:

Originally Posted by puppydogbuddy

No problems if Mary's solution works for you. However, from your response above, you did not understand what I was trying to convey.......that the easiest way to import the spreadsheet to Access, in view of your table structure, is to import the entire excel spreadsheet to one Access temp table, then parse the data from the temp table out to your normalized tables using union queries like the ones illustrated in the link I provided you.

Hyperpau,
There are two basic ways to do this. As Mary proposes, with code, or as PDB proposes, with SQL queries. Both should work for you and both proposers understand that are using a normalised database.
Different people prefer different ways, so it's up to you to find the way that fits you best. I'm sure we can help you, if you get stuck, on either approach.

-NeoPa.
hyperpau's Avatar
Expert
  
Join Date: Jun 2007
Posts: 177
#20: Aug 7 '07

re: Help with Forms based on Many to Many relationship

Quote:

Originally Posted by NeoPa

Hyperpau,
There are two basic ways to do this. As Mary proposes, with code, or as PDB proposes, with SQL queries. Both should work for you and both proposers understand that are using a normalised database.
Different people prefer different ways, so it's up to you to find the way that fits you best. I'm sure we can help you, if you get stuck, on either approach.

-NeoPa.


Thank you so much for all your help guys.

Mary, you did great!!!
The append query really works!!! :)
I needed to do append queries for each specific table. It takes
time but it works really great. Thank you so much!
thank as well neopa. I would now prefer using access' queries to do this. :)
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#21: Aug 20 '07

re: Help with Forms based on Many to Many relationship

I'm reading this as a sign-off. If you need further help then please feel free to continue.
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,419 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved