Query help with receipts

StealthRT · #1 (**permalink**) 09-15-09, 00:02

Hey all I am in need of some help with my query. I am testing this out with 2 users. However, it only seems to show the first user and never the other one. Here is my query string.

Code:

SELECT productr.id, staybridgedb.productr.itemPrice, productr.itemName,

SUM(productr.itemsSold) AS ITEMSSOLD,
SUM(productr.itemPriceTotal) AS PRICETOTAL,
SUM(productr.transactTotal) AS TRANSTOTAL,
SUM(productr.itemTax) AS TAX,
SUM(productr.transactTotalTax) AS TOTALWITHTAX,

productr.empID, staybridgedb.productr.transTime,
productr.itemRUDate, staybridgedb.empid.UniqueID,
empid.FName, staybridgedb.empid.LName
FROM productr, empid
WHERE productr.empid = empid.UniqueID
AND itemRUDate >= '2009/09/03'
GROUP BY itemName DESC

It displays fine for the first user but never shows anything for the second user. And i've also checked to make sure the "UniqueID" is different for both of them and that it has the same dates "2009/09/03" when looking through the receipts.

Any help would be great!

David

r937 · #2 (**permalink**) 09-15-09, 02:49

your problem is due to improper grouping

you did not describe your tables, or how/why the employee is related to the product

therefore it's not possible to guess whether you wanted product totals for each employee or employee totals for each product

StealthRT · #3 (**permalink**) 09-15-09, 18:56

Ive solved it by just using the ID in both cases to insure it would go through.

Code:

SELECT itemname, itemsSold, empid, itemRUDate, itemPrice, itemPriceTotal, recpType, itemTransID,
transTime, staybridgedb.empid.FName, staybridgedb.empid.LName
From staybridgedb.productr, staybridgedb.empid
WHERE productr.empid = '" & theUID & "'
AND staybridgedb.empid.UniqueID = '" & theUID & "'
AND itemRUDate = '2009/09/03'
ORDER BY itemTransID, itemName ASC

But thanks for the pointer!

David

Internet Services

Web Development

Digital Marketing

Consumer Tech