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

Composite Primary Keys

P: n/a
Hi,

This is my first time posting, but from reading previous posts this
seems to be the place to go for any Access problems.

I am currently making a Duplicate invoice checker. I have one master
table and one checked table. When an invoice is checked it is appended
to the checked table. The problem is that we have sequential updates,
so none of the tables have primary keys (to enable the use of a Union
query to update). I have a query that shows duplicate invoice payments
within the master file but I want this to also check if the data
returned is in the checked table. Since there is no link between the
two tables I am a bit stuck.

I need some help on a query that checks if three attributes in the
master file are present in the same row of the checked file. Is this
possible?

Any help will be greatly appreciated as I have been scratching my head
over this for the last couple of weeks.

Thanks in advance
Pete

May 10 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On 10 May 2006 09:25:20 -0700, "Goater" <fe*********@hotmail.com>
wrote:

Welcome!

I have a few questions to understand your issue better:
What are "sequential updates"?
Why does this make it impossible to have primary keys?
What do you mean by "union query to update"? Union queries are NEVER
updatable.
Why did you choose a db structure with two tables, rather than a
Yes/No field for Checked?

-Tom.

Hi,

This is my first time posting, but from reading previous posts this
seems to be the place to go for any Access problems.

I am currently making a Duplicate invoice checker. I have one master
table and one checked table. When an invoice is checked it is appended
to the checked table. The problem is that we have sequential updates,
so none of the tables have primary keys (to enable the use of a Union
query to update). I have a query that shows duplicate invoice payments
within the master file but I want this to also check if the data
returned is in the checked table. Since there is no link between the
two tables I am a bit stuck.

I need some help on a query that checks if three attributes in the
master file are present in the same row of the checked file. Is this
possible?

Any help will be greatly appreciated as I have been scratching my head
over this for the last couple of weeks.

Thanks in advance
Pete


May 11 '06 #2

P: n/a
Goater wrote:
Hi,

This is my first time posting, but from reading previous posts this
seems to be the place to go for any Access problems.

I am currently making a Duplicate invoice checker. I have one master
table and one checked table. When an invoice is checked it is appended
to the checked table. The problem is that we have sequential updates,
so none of the tables have primary keys (to enable the use of a Union
query to update). I have a query that shows duplicate invoice payments
within the master file but I want this to also check if the data
returned is in the checked table. Since there is no link between the
two tables I am a bit stuck.

I need some help on a query that checks if three attributes in the
master file are present in the same row of the checked file. Is this
possible?

Any help will be greatly appreciated as I have been scratching my head
over this for the last couple of weeks.

Thanks in advance
Pete

If you go to the Queries tab and click New, there is a Find Unmatched
Records wizard. You might want to try it out on a test table first.
Find a table that has a autonumber. Make a copy of it. Now open the
copy and delete a few records. Now run the wizard to find unmatched
records. Look at the design. View the SQL code. If you understand it,
you should be able to write you own if the wizard doesn't like your
existing table structure.
May 11 '06 #3

P: n/a
Thanks for the reply, I was not being particulally clear in the last
posting so will try again.

Every so often downloads are taken from our payment system. So for
example the August download will include all payments in August as well
as the data from previous months. As I allready have the data from
previous months I union this download with a master table that has all
the previous information. This union then becomes the master table.
This should mean I don't get any duplicate data, i.e March data twice.
To be able to make this union the data stored in the master file must
not have had anything added (autonumber), else the union will include
duplicate entries as (1,Invoice details) is not the same as (Invoice
details), so the new table will include Marches data twice.

I have run an duplicate wizard to find duplicates in the master file,
but also want to check if the invoice is present in the checked table.
Unless there is a better way.

The left join proposal, would work if I could set a link that comprised
of two attributes but this does not seem to be possible with the
wizard.

Thanks for the replys,
Pete

May 11 '06 #4

P: n/a
On 11 May 2006 02:10:42 -0700, "Goater" <fe*********@hotmail.com>
wrote:

I would approach this differently. I would import the monthly download
in a separate MonthlyDownload table, and then run an Append query to
add only those records not found in Master to the Master table.

Salad suggested you STUDY the results of the Duplicate Wizard. Indeed
it doesn't work on more than one field. One solution is to combine the
fields to one before you run wizard-like sql-statement. For example if
you had a query:
select FN & MI & LN as FullName, * from Customers
then you can use the wizard to find duplicate fullnames.

-Tom.

Thanks for the reply, I was not being particulally clear in the last
posting so will try again.

Every so often downloads are taken from our payment system. So for
example the August download will include all payments in August as well
as the data from previous months. As I allready have the data from
previous months I union this download with a master table that has all
the previous information. This union then becomes the master table.
This should mean I don't get any duplicate data, i.e March data twice.
To be able to make this union the data stored in the master file must
not have had anything added (autonumber), else the union will include
duplicate entries as (1,Invoice details) is not the same as (Invoice
details), so the new table will include Marches data twice.

I have run an duplicate wizard to find duplicates in the master file,
but also want to check if the invoice is present in the checked table.
Unless there is a better way.

The left join proposal, would work if I could set a link that comprised
of two attributes but this does not seem to be possible with the
wizard.

Thanks for the replys,
Pete


May 11 '06 #5

P: n/a

Thanks for the help. Have done it by combining fields in the inital
query to formulate a primary key before updating to the master file.
Then using the find duplicates wizard on a left join query to display
only those invoices that have not been checked. Seems to have done the
trick. Thanks once again for all the assistance.

Pete

May 11 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.