duplicated data

E

Elsie

hi,

if 1 item has 2 transactions with different vendors and different prices,

eg: item A is bought from vendor A at $100, as well as from vendor B at
$110.

but my query gives:

item A with vendor A at $100
item A with vendor A at $110
item A with vendor B at $100
item A with vendor B at $110

why is this so?

Elsie
 
J

Jackie L

Elsie,
It would have to do with the way your tables are joined. Since I do not know your table or query structure I would just suggest you check that or send further information on the underlying query so we can give you better help.

Thanks.
 
J

John Vinson

hi,

if 1 item has 2 transactions with different vendors and different prices,

eg: item A is bought from vendor A at $100, as well as from vendor B at
$110.

but my query gives:

item A with vendor A at $100
item A with vendor A at $110
item A with vendor B at $100
item A with vendor B at $110

why is this so?

Elsie
Click to expand...

You've gotten three answers which you have ignored, so just in case
you misunderstand how these newsgroups work I'm posting a reply and
copying it to EMail - please reply *to the newsgroup* rather than to
the email.

When you create a Query joining two tables, every possible valid
combination of records is returned. You have not posted your table
structure nor have you posted the SQL of the query, so we're having to
guess - but let's say you have a table of Items with an ItemID; a
table of Vendors who sell that item; and a table of Transactions with
prices. If you have:

Items
ItemA

Transactions
ItemA
$110

ItemA
$100

Vendors
ItemA
VendorA

ItemA
VendorB

and create a query joining the three tables, you will get all four
possible combinations.

To get just a single record per transaction, you need to have a
VendorID field in the transactions table indicating which vendor was
involved *in this particular transaction*, rather than just having a
list of vendors who sell this item.

If your table structure is different than I've assumed, and if this
example doesn't help, please post back with details.
 
E

Elsie

Hi, I did not ignore those 3 answers, I can't find the messages from my
previous post. Using Outlook Express 6 to read this newsgroup, the older
posts were removed, hence can't reply.

query as below:

SELECT [PUB_po-rcpt].[rcvd-date], [Year_Month_data_M].[Month] & "/" &
[Year_Month_data_M].[Year] AS CM, [PUB_po-rcpt].[vend-num],
PUB_vendaddr.country, PUB_vendaddr.name, [PUB_itemloc].[qty-on-hand]*1 AS
Qty, [PUB_itemloc].[qty-on-hand]*[PUB_item].[unit-cost] AS Amt,
[PUB_itemloc].[qty-on-hand]*[PUB_item].[unit-cost]*0.17 AS Overhd
FROM Year_Month_data_M, (([PUB_po-rcpt] INNER JOIN (PUB_item INNER JOIN
PUB_poitem ON PUB_item.item = PUB_poitem.item) ON ([PUB_po-rcpt].[po-line] =
PUB_poitem.[po-line]) AND ([PUB_po-rcpt].[po-num] = PUB_poitem.[po-num]))
INNER JOIN PUB_itemloc ON PUB_item.item = PUB_itemloc.item) INNER JOIN
PUB_vendaddr ON [PUB_po-rcpt].[vend-num] = PUB_vendaddr.[vend-num]
GROUP BY [PUB_po-rcpt].[rcvd-date], [Year_Month_data_M].[Month] & "/" &
[Year_Month_data_M].[Year], [PUB_po-rcpt].[vend-num], PUB_vendaddr.country,
PUB_vendaddr.name, [PUB_itemloc].[qty-on-hand]*1,
[PUB_itemloc].[qty-on-hand]*[PUB_item].[unit-cost],
[PUB_itemloc].[qty-on-hand]*[PUB_item].[unit-cost]*0.17,
PUB_item.description, PUB_item.[product-code], Int([PUB_item].[unit-cost]),
PUB_poitem.item
HAVING ((([PUB_po-rcpt].[rcvd-date])>=#5/1/2004# And
([PUB_po-rcpt].[rcvd-date])<=#5/31/2004#) AND ((PUB_item.[product-code]) Not
Like "D*"))
ORDER BY [PUB_po-rcpt].[vend-num], PUB_poitem.item;

Objective: to find those stock in inventory which are purchased, their
quantities at the end of the month and the correponding vendors.

PUB_po-rcpts: table which lists the receiving of items from vendors.
PUB_item-loc: gives the on-hand quantity of the inventory as of now.
PUB_vend-num: to link the vendor number to the vendor name.

Hope this is clear....

Elsie
 
J

John Vinson

Ok, let's see if we can make sens of this. Parsing the FROM clause
(which I'm pretty sure is the base of the problem):

FROM Year_Month_data_M,
(
([PUB_po-rcpt]
INNER JOIN
(PUB_item
INNER JOIN PUB_poitem ON PUB_item.item = PUB_poitem.item
) ON ([PUB_po-rcpt].[po-line] = PUB_poitem.[po-line])
AND ([PUB_po-rcpt].[po-num] = PUB_poitem.[po-num])
)
INNER JOIN
PUB_itemloc ON PUB_item.item = PUB_itemloc.item
)
INNER JOIN PUB_vendaddr
ON [PUB_po-rcpt].[vend-num] = PUB_vendaddr.[vend-num]

You have the table Year_Month_Data_M in the query but it's not joined
to ANYTHING - so you're getting a Cartesian join of every record in
Year_Month_Data_M with every record in the remaining three-table
query, giving you duplicates if there are two records in
Year_Month_Data_M.
 
Top