By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Help with Forms based on Many to Many relationship

hyperpau
Expert 100+
P: 184
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?
Jul 23 '07 #1
Share this Question
Share on Google+
20 Replies


NeoPa
Expert Mod 15k+
P: 31,307
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
Jul 24 '07 #2

NeoPa
Expert Mod 15k+
P: 31,307
Don't bother with the other (pointer) thread. I left a pointer there anyway when I moved the thread :)
Jul 24 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jul 24 '07 #4

hyperpau
Expert 100+
P: 184
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?
Jul 26 '07 #5

hyperpau
Expert 100+
P: 184
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. :)
Aug 2 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Aug 2 '07 #7

NeoPa
Expert Mod 15k+
P: 31,307
Mary? I would like to recall this post. :)
What do you mean hyperpau, do you want it deleted?
Aug 2 '07 #8

hyperpau
Expert 100+
P: 184
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. :)
Aug 4 '07 #9

hyperpau
Expert 100+
P: 184
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. :)
Aug 4 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Aug 4 '07 #11

puppydogbuddy
Expert 100+
P: 1,923
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.
Aug 4 '07 #12

puppydogbuddy
Expert 100+
P: 1,923
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.
Aug 4 '07 #13

Rabbit
Expert Mod 10K+
P: 12,341
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.  
Aug 4 '07 #14

hyperpau
Expert 100+
P: 184
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. :)
Aug 6 '07 #15

hyperpau
Expert 100+
P: 184
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. :)
Aug 6 '07 #16

hyperpau
Expert 100+
P: 184
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. :)
Aug 6 '07 #17

puppydogbuddy
Expert 100+
P: 1,923
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.
Aug 6 '07 #18

NeoPa
Expert Mod 15k+
P: 31,307
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.
Aug 6 '07 #19

hyperpau
Expert 100+
P: 184
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. :)
Aug 7 '07 #20

NeoPa
Expert Mod 15k+
P: 31,307
I'm reading this as a sign-off. If you need further help then please feel free to continue.
Aug 20 '07 #21

Post your reply

Sign in to post your reply or Sign up for a free account.