Urgent help with SQL statement

[Silvester]

Hi,

I need a billing-based summary report based on a query linked to a table
tblItemsUsed with fields :

CheckoutDate
Item1
Item2
Item3
.....upto
Item20
CheckoutTime
Cashier

Only the Item Names are stored in Item1, Item2, etc. Further details about
the details are found in a separate table tblItemDetails.

For each ItemName used how can I get the ItemNumber, Manufacturer, totalSold
from tblItemDetails and base a summary report of all items sold with all
these details on it.
ItemName
ItemNumber
CheckoutDate
CheckoutTime
TotalSold

I urgently need help with the sql for this.
I am open to using sql to create querydefs and a query on the fly and delete
* from this query the next time it is run.

Please help.

 

[Dirk Goldgar]

Hi,

I need a billing-based summary report based on a query linked to a
table tblItemsUsed with fields :

CheckoutDate
Item1
Item2
Item3
....upto
Item20
CheckoutTime
Cashier

Only the Item Names are stored in Item1, Item2, etc. Further details
about the details are found in a separate table tblItemDetails.

For each ItemName used how can I get the ItemNumber, Manufacturer,
totalSold from tblItemDetails and base a summary report of all items
sold with all these details on it.
ItemName
ItemNumber
CheckoutDate
CheckoutTime
TotalSold

I urgently need help with the sql for this.
I am open to using sql to create querydefs and a query on the fly and
delete * from this query the next time it is run.

Please help.

This is an awful table design! Do you have any control over it at all?
Can you redesign tblItemsUsed to normalize it?

 

[Van T. Dinh]

You have repeating groups of one Field in each group (Item1, Item 2,
etc...). This means that your Table is not normalised properly and you have
a lot more problems later. For example:

* What if you need to store more than 20 Items in a Record in the
tblItemsUsed?

* What if you want to find out which Records from tblItemsUsed that actually
has Item "X"?

Suggest you read up on the Relational Database Design Theory and Database
Normalization and modify the Table accordingly.

 

[Silvester]

Sorry gentlemen. Not my design. I have to work out a quickfix for someone
else.

 

[Dirk Goldgar]

Sorry gentlemen. Not my design. I have to work out a quickfix for
someone else.

You're going to need a "normalizing query" for tblItemsUsed. Create a
query with this SQL and save it as "qryItemsUsed":

---- start of SQL ----
SELECT Item1 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item1 Is Not Null
UNION ALL
SELECT Item2 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item2 Is Not Null
UNION ALL
SELECT Item3 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item3 Is Not Null
UNION ALL
SELECT Item4 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item4 Is Not Null
UNION ALL
SELECT Item4 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item4 Is Not Null
UNION ALL
SELECT Item5 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item5 Is Not Null
UNION ALL
SELECT Item6 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item6 Is Not Null
UNION ALL
SELECT Item7 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item7 Is Not Null
UNION ALL
SELECT Item8 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item9 Is Not Null
UNION ALL
SELECT Item10 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item10 Is Not Null
UNION ALL
SELECT Item11 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item11 Is Not Null
UNION ALL
SELECT Item12 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item12 Is Not Null
UNION ALL
SELECT Item13 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item13 Is Not Null
UNION ALL
SELECT Item14 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item14 Is Not Null
UNION ALL
SELECT Item14 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item14 Is Not Null
UNION ALL
SELECT Item15 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item15 Is Not Null
UNION ALL
SELECT Item16 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item16 Is Not Null
UNION ALL
SELECT Item17 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item17 Is Not Null
UNION ALL
SELECT Item18 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item19 Is Not Null
UNION ALL
SELECT Item20 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item20 Is Not Null;

---- end of SQL ----

This query returns rows with these fields:

ItemName
CheckoutDate
CheckoutTime
Cashier

Now you can use this query in another query, joining it to
tblItemDetails on the ItemName field they have in common, picking up the
detail information from tblItemDetails, grouping by ItemName,
ItemNumber, CheckoutDate, and CheckoutTime, and using a Count()
expression to give you the calculated field, TotalSold.

 

[Van T. Dinh]

.... and Dirk's SQL String answers my second question.

You can see the hoops you have to go through ...

If you have a properly normalized Table, it is as easy as:

SELECT *
FROM YourTable
WHERE [Item] = "X"

Tell whoever the "someone" that there will be other problems awaiting ...
It is advisable to fix the Table Structure.

 

[Silvester]

Thank you very much Dirk.

When a MS-MVP is willing to take the trouble to get down and help the
ordinary man with dirty coding, it is so highly appreciated. Thanks again.

... and Dirk's SQL String answers my second question.

You can see the hoops you have to go through ...

If you have a properly normalized Table, it is as easy as:

SELECT *
FROM YourTable
WHERE [Item] = "X"

Tell whoever the "someone" that there will be other problems awaiting ...
It is advisable to fix the Table Structure.

--
HTH
Van T. Dinh
MVP (Access)

You're going to need a "normalizing query" for tblItemsUsed. Create a
query with this SQL and save it as "qryItemsUsed":

---- start of SQL ----
SELECT Item1 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item1 Is Not Null
UNION ALL
SELECT Item2 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item2 Is Not Null
UNION ALL
SELECT Item3 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item3 Is Not Null
UNION ALL
SELECT Item4 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item4 Is Not Null
UNION ALL
SELECT Item4 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item4 Is Not Null
UNION ALL
SELECT Item5 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item5 Is Not Null
UNION ALL
SELECT Item6 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item6 Is Not Null
UNION ALL
SELECT Item7 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item7 Is Not Null
UNION ALL
SELECT Item8 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item9 Is Not Null
UNION ALL
SELECT Item10 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item10 Is Not Null
UNION ALL
SELECT Item11 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item11 Is Not Null
UNION ALL
SELECT Item12 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item12 Is Not Null
UNION ALL
SELECT Item13 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item13 Is Not Null
UNION ALL
SELECT Item14 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item14 Is Not Null
UNION ALL
SELECT Item14 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item14 Is Not Null
UNION ALL
SELECT Item15 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item15 Is Not Null
UNION ALL
SELECT Item16 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item16 Is Not Null
UNION ALL
SELECT Item17 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item17 Is Not Null
UNION ALL
SELECT Item18 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item19 Is Not Null
UNION ALL
SELECT Item20 AS ItemName, CheckoutDate, CheckoutTime, Cashier
FROM tblItemsUsed
WHERE Item20 Is Not Null;

---- end of SQL ----

This query returns rows with these fields:

ItemName
CheckoutDate
CheckoutTime
Cashier

Now you can use this query in another query, joining it to
tblItemDetails on the ItemName field they have in common, picking up the
detail information from tblItemDetails, grouping by ItemName,
ItemNumber, CheckoutDate, and CheckoutTime, and using a Count()
expression to give you the calculated field, TotalSold.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[zfidjili]

This is an awful table design! Do you have any control over it at all?
Can you redesign tblItemsUsed to normalize it?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)