Showing multiple queries results in one form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have multiple tables with products of which I would like to see the
movement of during a period. I have created queries to count the products I
am interested in.
Each query consists of two fields : "product" and the "movement count".
Now I would like to display the count result of each query in a text box on
one form.
If I combine the queries into one, as suggested by Microsoft online help, my
first product's result gets displayed and not the rest and everything gets
multiplied.
Any suggestions will be appreciated.
 
I'm afraid your problem starts with a poor design. Why do you need to
have multiple tables with the same structure? Is there any compelling
reason for this? A single table would make a much more ronust design,
and your life a lot easier. Whatever information is conveyed by the
table itself under your current design, could most likely be stored in
an added field in a single table. Do you want to give us some more details?

HTH,
Nikos
 
Dunmarie

You really should look at your database design as suggested by Nikos.
However you may be able to get the results You want using a Union Query.

Without knowing what your datastructure or data looks like it is difficult
to give a relevant example, but in general a Union query can be used to
bring the results of two (or more) queries together in one result set.
Basically the rules are each query must contain the same number of columns
in the output and they must be in the same order. So....

SELECT City, State, Zip, 'Cust' as Customer
FROM Customers
WHERE Region = 'West'
UNION
SELECT City, St, ZipCode, 'Vend' as Vendor
FROM Vendors
WHERE St In('OR', 'WA', 'CA')
ORDER BY State, City

Would combine the City state and Zip codes of Customers and Vendors into a
single homogeneous output (without duplicates) even though the data for each
group of addresses comes from different tables. BTW I would also consider
this schema poor database design as this information could easily be stored
in the same table with another column describing what type of entity each
record belonged to.

You can find additional information for Union queries in Access help and by
doing a search in Google.

Ron W
 
Back
Top