i would like to know an SQL statement that display sum totals, item id and item Name

[gerryis2000]

Hi guys, i have purchases table where purchase items are selected and a daily purchase report generated. i use SQL to select item ID and Date of purchase then sum all the daily purchases.

since i have multiple items with unique IDs,like 'B001' FOR COCA-COLA, 'F001' FOR Fanta and so on, i would like When i select an item e.g 'B001' the name should also appear in some field after pressing run instead of only totals so that i can have 'fanta opening stock'or 'coca-cola opening stock' based on my input. the folloing is my current SQL query.

SELECT Count(*) AS [NUMBER OF PURCHASES], Sum([UNITS PURCHASED]) AS [OPENING STOCK]
FROM PURCHASES
WHERE (((PURCHASES.[Item ID]) Like [ENTER ITEM CODE:] & "*") AND ((PURCHASES.[Date Purchased]) Like [ENTER DATE OF PURCHASE:] & "*"));

currently i only get two fiedls[number of purchase] and [opening stock] i want item ID and Name to also appear on the query. thanks

[NeoPa]

Do you have the name of the item stored in [PURCHASES] as well as the [Item ID]?

If so, then you shouldn't really (See Normalisation and Table structures).

However, if this is your structure then you can do it, albeit with a good expectation of confusing data if anything is not exactly as it should be.

SELECT   [Item ID]
        ,[NameField]
        ,Count(*) AS [NUMBER OF PURCHASES]
        ,Sum([UNITS PURCHASED]) AS [OPENING STOCK]
FROM     [PURCHASES]
WHERE    ((PURCHASES.[Item ID] Like [ENTER ITEM CODE:] & '*')
  AND    (PURCHASES.[Date Purchased] Like [ENTER DATE OF PURCHASE:] & '*'))
GROUP BY [Item ID]
        ,[NameField]

[NeoPa]

A better way to handle getting the correct name for something like this would be to store the data in an Item or Product table which you link into the [PURCHASES] table by storing the Primary Key of the Product table in the [PURCHASES] records.

[gerryis2000]

Thanks Neopa, indeed i have atable called purchases with primary key and another table called products with a primary key. i.ve linked the two tables and created a form called purchases. but then on the report i need to see each i tem seperately on a single page. like total coca cola purchases and fanta purchases summary. since my report is created from the purchases query, the reason why i decided to sum all purchases. thanks

[NeoPa]

In that case your SQL would need to include a JOIN to the [PRODUCTS] table in th FROM clause.

Glad this has helped & Welcome to Bytes!