Duplicate records in split database

[Polarbear]

I am working on a shared split database that has an inventory list with
purchase and invoicing tables and forms. The inventory items are entered as a
specific number with a description. When purchases are made I enter the total
cost and quantity. This generates the unit price. If a purchase of, for
example, paper #20005 is made for a quantity of 10 for one location and
another purchase of a quantity of 30 is made to be delivered to another
location then this causes a duplicate record for the paper in the inventory
query. When the monthly stock report is generated the paper is shown doubled.
I was told to delete the extra record in the query which fixes it. I need to
know the process of why this is happening and what prevents it.

If anyone can help me with this it would be greatly appreciated.

 

[Beetle]

Maybe I'm missing something here, but since those would be two
different purchase records they both *should* show up in your query.
If you don't want the Paper name to show up more than once in your
report, then you add a grouping level to your report, you don't
delete a record from your query.

 

[Polarbear]

I meant to say the extra record showed up in the "Inventory table" not the
query.

 

[Beetle]

You said you, for example, enter a purchase of 10 for one location and
a purchase of 30 for a different location. Are you saying that each of
these records is being duplicated, so you end up with four new records
in the table (instead of two - which would be expected)?

 

[Polarbear]

The problem is that a duplicate record is made in the inventory supplies
table, for which there should only be one entry for each numbered inventory
item. When the stock report is generated a duplicate entry for that inventory
item is made therefore, the form indicates that the customer bought two of
that item instead of just one. I hope this is a little clearer, it's very
hard to explain unless you actually see what's happening.

Thanks,
D

 

[Armen Stein]

The problem is that a duplicate record is made in the inventory supplies
table, for which there should only be one entry for each numbered inventory
item. When the stock report is generated a duplicate entry for that inventory
item is made therefore, the form indicates that the customer bought two of
that item instead of just one. I hope this is a little clearer, it's very
hard to explain unless you actually see what's happening.

Usually when "duplicate" records appear, it's because a query join is
causing the "one" side to repeat in order to show all the records on
the "many" side.

What you seem to be saying is that you are seeing duplicate records in
the Inventory *table* - not a query or report.

If that's true, then somewhere in your code you are actually inserting
an extra record into that table. It can't just happen on its own.

If you open the Inventory *table* and do not see the duplicates, then
you have the more common problem of seeing repeating records because
of the way your joins are set up. In that case, you need to fix your
queries or reports to group on each Inventory item, as Beetle
indicated.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com