Having trouble joining a table and query

M

Mel

I am trying to join a table and a query. The qryJulysales gives manager,
acct, itemrange, sales, disconinv. The table has acct, item range,
discoffinv. This is what I need to accomplish:
Manager, acct, itemrange, sales, disconinv, discoffinv, totaldiscount
(=sales-disconinv-discoffinv).

I have tried to join the acct and itemrange with “a relationship.†However,
if a discoffinv is entered in July but there are no sales against the
itemrange, then the discoffinv won’t appear. For instance:

Query: Acct: Kitchens & More, ItemRange: Toaster, Sales: 420.00, Disconinv:
20.00
Table: Acct: Kitchens & More, Item Range: Toaster, Discoffinv: 20.00
Table: Acct: Kitchens & More, Item Range: Spoons, Discoffinv: 40.00

Result:
Acct: Kitchens & More, ItemRange: Toaster, Sales: 420.00, Disconinv: 20.00,
Discoffinv: 20.00, TotalDisc: $380.00
The Spoons will not show up.

This is what I would need to see:

Acct: Kitchens & More, ItemRange: Toaster, Sales: 420.00, Disconinv: 20.00,
Discoffinv: 20.00, TotalDisc: $380.00
Kitchens & More, ItemRange: Spoons, Sales: 0.00, Disconinv: 0.00,
Discoffinv: 20.00, TotalDisc: -20.00.

Is this possible?

Thanks,
Mel
 
J

Jeff Boyce

Mel

Not sure I'm following, but have you modified the property of the join to
show all of your itemranges, and any sales info?

Regards

Jeff Boyce
<Access MVP>
 
M

Mel

Hi Jeff,

I did a join from itemranges and acct from the query to the table. It said
to list all information in the query and only items that matched in the
table. This is why the "Spoons" example would not show up.

Thanks,

Mel
 
M

Mel

SELECT tbladdressbook.acct, tblJuly05Data.[Manager], tblbrands.itemRange,
tblJuly05Data.Brand, tblJuly05Data.[Sales - Disconinv], tblJuly05Data.[A&P -
Act], tblJuly05Data.[NUS Discount A], tblJuly05Data.[NUS Discount B]
FROM (tbladdressbook RIGHT JOIN tblJuly05Data ON tbladdressbook.acct =
tblJuly05Data.acct) LEFT JOIN tblbrands ON tblJuly05Data.Brand =
tblbrands.Brand;

Thanks,

Mel
 
Top