Access stock control query

[JR2913]

I have been using the query below to try to provide an accurate stock level from two tables tbl_Delivery and tbl_Sale. I can produce results from 2 queries (qry_DelivertTotals and qry_SalesTotals) that summarise how many of each product have been delivered and how many have been sold. Whilst I can get stock level data for products that have sales recorded against them, the resulting query won't provide any data for products that have been delivered but for which there have been no sales.

SELECT tbl_StockItems.RefNo, tbl_StockItems.[Model No], tbl_StockItems.Description, qry_DeliveryTotals.SumOfNumber, qry_SalesTotals.SumOfNumber, [qry_DeliveryTotals.SumOfNumber]-[qry_SalesTotals.SumOfNumber] AS in_stock
FROM (qry_SalesTotals INNER JOIN qry_DeliveryTotals ON qry_SalesTotals.[Model No] = qry_DeliveryTotals.[Model No]) INNER JOIN tbl_StockItems ON qry_DeliveryTotals.[Model No] = tbl_StockItems.[Model No]
ORDER BY tbl_StockItems.[Model No];

Could someone please suggest a modification to this script that will include products for which there have been no sales?

Regards

JohnR

 

[PWise]

SELECT tbl_StockItems.RefNo, tbl_StockItems.[Model No], tbl_StockItems.Description, qry_DeliveryTotals.SumOfNumber, qry_SalesTotals.SumOfNumber, [qry_DeliveryTotals.SumOfNumber]-[qry_SalesTotals.SumOfNumber] AS in_stock
FROM (tbl_StockItems
INNER JOIN qry_DeliveryTotals ON tbl_StockItems
.[Model No] = qry_DeliveryTotals.[Model No])
left JOIN qry_SalesTotals
ON tbl_StockItems.[Model No] = tbl_StockItems.[Model No]
ORDER BY tbl_StockItems.[Model No];

 

[JR2913]

Many thanks, but this script generates an error:

The SELECT statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect.

I modified left to LEFT and the query runs but still doesn't include products where there have been no sales.

What I find odd is that this query produces 2847 records whereas the qry_SalesTotals generates 2938 records and qry_DeliveryTotals generates 3156 records. If this query was working correctly I would expect to see 3156 records also.

Regards

JohnR

 

[PWise]

not relly sure how to put the prentices () i do most of my work in sqlserver where you dont have to use then

what i would do is recreate the query
start with tbl_StockItems
inner join to qry_DeliveryTotals
left join to qry_SalesTotals

also change the in stock field to
nz([qry_DeliveryTotals.SumOfNumber],0)-nz([qry_SalesTotals.SumOfNumber],0)


Well how many records in tbl_StockItems

 

[JR2913]

There are 3797 records in tbl_StockItems.

I've tried changing the criteria, as suggested above, for the variable in_stock but all I now get is:

The field is too small to accept the amount of data to attempted to add. Try inserting or pasting less data.

Problem now is that, even if I revert to the original criteria, I still keep getting the same message and can't run the report I need at all!!

 

[JR2913]

Well, I've now given up on trying to get that to work. I have now generated two queries as below:

Query1_Delivery
SELECT tbl_StockItems.[Model No], tbl_StockItems.Description, Nz([SumOfNumber],0) AS No_Delivered
FROM qry_DeliveryTotals RIGHT JOIN tbl_StockItems ON qry_DeliveryTotals.[Model No] = tbl_StockItems.[Model No]
ORDER BY tbl_StockItems.[Model No];

and

Query2_Sales
SELECT tbl_StockItems.[Model No], tbl_StockItems.Description, Nz([SumOfNumber],0) AS No_Sold
FROM tbl_StockItems LEFT JOIN qry_SalesTotals ON tbl_StockItems.[Model No] = qry_SalesTotals.[Model No]
ORDER BY tbl_StockItems.[Model No];


and had hope to be able to generate a third query from the data produced but so far no success, so have resorted to copying the resulting data tables into Excel, which gives me some flexibility in the display.

 

[PWise]

The field is too small to accept the amount of data to attempted to add. Try inserting or pasting less data

is this query a append query or a select

 

[JR2913]

Select

 

[jedraw]

When I see a M$oft variable SumOfNumber, I think you have a field in one of your tables called NUMBER.

Could be wrong, but that's what I'm seeing. NUMBER is a reserved word in Access.

 

[JR2913]

SumOfNumber is a generated field from other queries which count the total number of a product code, whether delivered (qry_DeliveryTotals) or sold (qry_SalesTotals).

 

[JR2913]

Having checked through the data tables, there are, indeed, fields labelled NUMBER, but this hasn't affected the creation of or results in other queries which use data in this field.