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

Form with record source from two tables

P: 25
Any help would be very appreciated!!!

I have two tables: Main Data and Archived Data.
Each table has the same fields (about 50 or so fields).
Main data contains records that are updated every week.
Archived data contains historical data that once was in "main data".
Each record can be distinguished by 3 different fields (There is no PK!):Invoice No, Amount, Date.
Some records can be in both tables, some can have the same invoice number (that's why I identify unique record by three different fields). Some can be in the same table with the same Invoice number and so on...

I need to create a form with the following functionality:
1. User enters invoice number.
2. If such record exist and is unique in only one table, then display that record.
if no record found - nothing is displayed
if multiple records exist (either within the same table or different ones) some kind of option should be displayed to specify (or verify) the amount and date to make sure the desired record is displayed.
3. User should be able to modify displayed fields.

The problem I'm having with such design is how to determine how to pull out record from correct table. The design I thought of did NOT seem to be reliable.
Any suggestions will be appreciated and let me know if more info needed about table or data...
Thanks a lot
Jan 15 '07 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,634
Any help would be very appreciated!!!

I have two tables: Main Data and Archived Data.
Each table has the same fields (about 50 or so fields).
Main data contains records that are updated every week.
Archived data contains historical data that once was in "main data".
Each record can be distinguished by 3 different fields (There is no PK!):Invoice No, Amount, Date.
Some records can be in both tables, some can have the same invoice number (that's why I identify unique record by three different fields). Some can be in the same table with the same Invoice number and so on...

I need to create a form with the following functionality:
1. User enters invoice number.
2. If such record exist and is unique in only one table, then display that record.
if no record found - nothing is displayed
if multiple records exist (either within the same table or different ones) some kind of option should be displayed to specify (or verify) the amount and date to make sure the desired record is displayed.
3. User should be able to modify displayed fields.

The problem I'm having with such design is how to determine how to pull out record from correct table. The design I thought of did NOT seem to be reliable.
Any suggestions will be appreciated and let me know if more info needed about table or data...
Thanks a lot
If each record can be distinguished by 3 different fields, namely, [Invoice No], [Amount], and [Date] and no Primary Key exists on either Table, I see absolutely no reason why you would not want to create a Composite Primary Key on these Fields. It would eliminate data duplication, as well as simplify the required coding to accomplish what you desire. Is there any particular reason why you do not wish to do this?
Jan 15 '07 #2

P: 25
If each record can be distinguished by 3 different fields, namely, [Invoice No], [Amount], and [Date] and no Primary Key exists on either Table, I see absolutely no reason why you would not want to create a Composite Primary Key on these Fields. It would eliminate data duplication, as well as simplify the required coding to accomplish what you desire. Is there any particular reason why you do not wish to do this?

Data in "Main Data" table gets overwriten every week. Seems to me that if I create a composite PK it be rather useless because for example:

week1:
trans ID | Composite PK
A 1
B 2
C 3

I would add those 3 transaction into "Archived Data" table that would be like "Main Data" at current instance.

Week2:
trans ID | Composite PK
A 1
B 2
D 3

For me to store those transactions into "Archived Data" I would still have to check if they already exist by [Invoce No], Date, Amount. Because new transaction D got assigned to composite PK 3 that was previously assigned to transaction C.
Of course I dont like the design without PK myself. Perhaps I could use it and I'm just missing something... If you think that composite PK would make the task easier please suggest.
Thanks for replying.
Jan 15 '07 #3

NeoPa
Expert Mod 15k+
P: 31,487
You could consider a UNION query for showing the records but they would not be updatable so that could be a stumbling block.
Jan 16 '07 #4

P: 25
You could consider a UNION query for showing the records but they would not be updatable so that could be a stumbling block.
I thought of UNION query which would be the best solution but the user must be able to modify data...
Jan 16 '07 #5

Post your reply

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