Composite Primary Keys

[Goater]

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

[Tom van Stiphout]

Re: Composite Primary Keys

On 10 May 2006 09:25:20 -0700, "Goater" <feadthegoat@ho tmail.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.

[color=blue]
>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[/color]

[salad]

Re: Composite Primary Keys

Goater wrote:
[color=blue]
> 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
>[/color]
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.

[Goater]

Re: Composite Primary Keys

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

[Tom van Stiphout]

Re: Composite Primary Keys

On 11 May 2006 02:10:42 -0700, "Goater" <feadthegoat@ho tmail.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.

[color=blue]
>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[/color]

[Goater]

Re: Composite Primary Keys


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