473,320 Members | 2,024 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Form with record source from two tables

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
4 2706
ADezii
8,834 Expert 8TB
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
is49460
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
32,556 Expert Mod 16PB
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
is49460
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

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

Similar topics

1
by: Robert Neville | last post by:
I would like to add filter functionality to my database whether through the Main form or the subform. This question may be rudimentary, yet I have not less experience with filtering data outside...
4
by: Skully Matjas | last post by:
I am using the following code (created by the wizard) to allow to bring my form to a particular entery. But when I edit the entery (ex: put new information into a blank cell), it puts that record...
5
by: Newbee | last post by:
Hi, I would like to have form with Tab Control with 3 pages. Each page in tab control would contain text boxes from different sources (tables). On first page would be text boxes from tblOne,...
4
by: Krzysztof Bartosiewicz | last post by:
Hi! I haven't been using Access for a very long time and I forgot everything :) I will be very greatful for help since I have been fighting with this problem for a few hours... I have three...
1
by: Bill | last post by:
Problem: Combo box data disappears from view when a requery is done See "Background" below for details on tables, forms & controls On a form, I want to use the setting of bound combo box C1...
4
by: john | last post by:
I created a form via the wizard with 1 main table and 2 one-on-one tables. As i couldn't add a field to the form (a field that I added to the table after creating the form), I googled out that the...
7
by: Susan Mackay | last post by:
I have a data table that is connected to a database table with a data adapter in the 'standard' manner. However I want to be able to remove selected rows from the data table (i.e. no longer...
17
by: radio1 | last post by:
Configuration: Access 2002 and SQL Server 2000 using a .ADP Project. I would VERY MUCH appreciate anyone's input into this problem I'm having. I have a form in Access that does not permit...
15
beacon
by: beacon | last post by:
Hi everybody, Using Access 2003. I'm trying to dynamically set the record source for the current form and set the control source for a text box to input data to one of three tables. I have a...
10
by: susan | last post by:
Hi, Is it (in Access2003) possible yo link from a form in db1 to a form in db2? Thanks, Susan
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.