Help with query where clause.

[Gazza2]

Hi all,

I have the following sql statement in a query:

SELECT TblCustomers.Account, TblProducts.BinNo, "" AS ColumnC, "" AS ColumnD, [Listprice]-([ListPrice]*[Discount])-[Monetary] AS NettPrice
FROM ((TblSalesGroup INNER JOIN TblDiscountGroup ON TblSalesGroup.[SalesGroupID] = TblDiscountGroup.[SalesGroupID]) INNER JOIN TblCustomers ON TblDiscountGroup.[DiscountGroupID] = TblCustomers.[DiscountGroupID]) INNER JOIN TblProducts ON TblSalesGroup.SalesGroupID = TblProducts.SalesGroupID
WHERE (((TblCustomers.DiscountGroupID)>1) AND ((TblCustomers.RebateGroupID)>1));

the problem is it gives each account the same DiscountGroupID and the same RebateGroupID for all customers.

I am not sure how to write in the where clause to look at the DiscountGroupID and the RebateGroupID for that particular account.

I know it is something like the following :

Dlookup("DiscountGroupID","TblCustomers","Account=" & "'" & "Account" & "'")

But I don`t know how to add this to the sql statement or in the query designer.

Any help is much appreciated

Thanks
Gazza

 

[jdraw]

Do you have a clear, simple plain English description of what you are trying to do.
You showed us How you have done something that isn't working as expected.

 

[Gazza2]

Ok here is what I am trying to do.

I have 4 tables :

TblCustomers
Account
DiscountGroupID
RebateGroupID

TblProducts
PartNo
Description
ListPrice

TblDiscounts
DiscountGroupID
DiscountGroup
Discount
Monetary

TblRebates
RebateGroupID
RebateGroup
Rebate

Each customer belongs to a certain discount group and a certain rebate group giving each customer a different price for different products.

I need to get a .csv file with a list of all the customers discounted prices.

I can export to .csv file for a single customer but I can't figure out how to specify the different discount and rebate relating to a specific customer to get a full list for all customers.

Thanks
Gazza

 

[jdraw]

I think you are missing some tables but I'm guessing about your business. See if the attached makes any sense.

 

[Gazza2]

Thanks for the help jdraw,

Just needed to create 2 queries. One as the custRebateDiscount and the other as the partCostForCustomer and put them together in another query and all is good.

Thanks again
Gazza

 

[jdraw]

Happy to help.
Good luck with your project