Report data based on complex query in VBA code?

[Michael Watts]

I am looking for guidance on how to accomplish the
following that I will try my best to explain. I have a
database that has orders in it. I have an "order" table
that has a person's state, id number, order number, etc
in it. I also have a "order details" table that has an
order detail id, order number, product number, quantity
etc. Each person may have multiple orders entered for
them. But each person can only get one package.

I am currently trying to make the "pick ticket" report
and I am trying to figure out how to use nested queries
and variables to get the info I need then to display this
information on the report. I want to aggregate all the
items together( 2 of item a on order 4f and 3 of item a
on order 8w will list out as 5 of item a on the final
pick ticket.

There will be about four nested select statements where
info from the current record is used as criteria in the
next most nested select statement.

I will end up with about maybe 6 fields to display per
item and there is 48 possible items that the person can
order.


Is there any way to do this besides having 48 rows of
hidden fields with about 6 fields for each row?

Michael Watts
(e-mail address removed)

 

[Steve Schapel]

Michael,

To be honest, I am having some difficulty in relating the first part
of your post with the second. I think I understand the structure of
your tables. And then you want to produce a report which will show
all goods ordered by each person. This is a simple Totals query.
What makes you think this report will involve hidden fields, difficult
nested select statements and variables? Or have I missed something?
Using the fields and tables you mentioned, this is what it looks like
to me...
SELECT [Person ID], State, [Product Number], Sum([Quantity]) AS
NumberOrdered
FROM Order INNER JOIN [Order Details] ON Order.[Order Number] = [Order
Details].[Order Number]
GROUP BY [Person ID], State, [Product Number]

Presumably you will also possibly need to join this with a Person
table and/or a Product table, in order to get stuff like person's
address, product price, etc, but this doesn't really make it much more
complicated.

- Steve Schapel, Microsoft Access MVP