Query - Table Join Problem

[jgs]

I know I am doing something stupid and can't put my finger on it.



I am running a query to figure a sales rebate.

Table 1 (simplified) is my customer sales (CS):

Customer name
Sale Date
INV #
ITEM #
Qty Ship
UOM
Alt Qty Ship
Alt UOM
Sale Price

Table 2 (simplified)is an inventory file with only the items that have a rebate amount (REBATE):

ITEM #
Price
Rebate Amount

I want a query that:

Only pulls only records from the sales file that match items in the inventory file - as they are are only ones with a rebate. There are many other items in the customer sales file.
Pulls by specific date
Pulls by specific customer
Pulls the quantity shipped
Pulls the rebate amount

I have linked:

REBATE ----->CS using the ITEM # as the link

CRITERIA:
Item # (because I am triying to identify the problem and it helps to narrow it down to a specific item)
Date: between xx/xx/xxxx and zz/zz/zzzz
SOLD NAME: Like XXXXXXX*


The results I get are duplicating sales. If there is one sale for the customer and item in the time frame - it gives me 5 results that are exactly the same (IE: INV #1235 Qty 8 EA)

There are 4 or 5 sales for the item in the CS file - but outside the timeframe and with different quantities and invoice #'s.

I feel that it is a JOIN problem. I can search either of the tables separately with no problem. I have tried putting the JOINS each way and it doesn't seem to make a difference.

I can alter the tables slightly (add a field if it would correct the join) if needed.

Thanks for your help

[kd2017]

1) Make sure that your table names, query names, field names, etc. all don't use spaces are special characters. For example [ITEM #] could be ItemNum instead.

2) Can you post the sql that is returning duplicate records?

3) Better still, a zipped copy of the database?

PARAMETERS [Customer Name Here] Text ( 255 ), [After Date] DateTime, [Before Date] DateTime;
SELECT CS.[Qty Ship], REBATE.[Rebate Amount]
FROM CS INNER JOIN REBATE ON CS.[ITEM #] = REBATE.[ITEM #]
WHERE (((CS.[Customer Name])=[Customer Name Here]) AND ((CS.[Sales Date]) Between [After Date] And [Before Date]));

[Gicu]

Is the ITEM # duplicated in the inventory file (with different Price and or Rebate)? Try to edit the query and add DISTINCT after SELECT.

Cheers,
Vlad

[jgs]

OK SELECT DISTINCT worked to eliminate the duplicate.

BUT - it created another problem: IF I sold say 1 CTN and 1 PC on the same invoice - it is eliminating one of them.

SQL =
SELECT DISTINCT [SALES].Date, [SALE].[SOLD TO], [SALE].xref, [SALE].[INV #], [SALE].QTY, [SALE].UOM, [SALE].[ALT QTY], [SALE].ALT, [REBATE].CREDIT, [SALE].[ITEM DESC], [REBATE].[alt credit], [REBATE].[ALT UOM]
FROM [REBATE] LEFT JOIN [SALE] ON [REBATE].[Item Number] = [SALE].xref
WHERE ((([SALE].Date) Between #7/1/2020# And #7/31/2020#) AND (([SALE].[SOLD TO]) Like "BROC*"));

xref and Item Number are the same - just named differently in the data bases.

Thanks,

[Gicu]

Could you upload a small sample of your tables and query, no sensitive data, just a few records to show us the problem. Are CTN and PC different Items (#)? Why do you use left join if you want to restrinc the records to the ones having a rebate?

Cheers,
Vlad