Use multiple queries to display information on a form

[matthewemiclea]

I am trying to display information in Access on a form that takes
information from many different queries and puts them together on a
spreadsheet or chart.

Some specific info: The information I am displaying consists of a work
order schedule(for the current date), work orders completed , work
orders not completed, a past due schedule, past due completed, and past
due not completed. These are grouped by workcenters. Therefore, one
could see on this chart, on any given day, how many orders are
scheduled, how many past due orders remain, how many scheduled
orders/past due orders have been completed, and how many remain by
workcenter.

So... Scheduled orders = Scheduled complete + Scheduled Not Complete

And

Past Due Orders = Past Due complete + Past Due Not Complete

Currently, I have built a separate query that shows each instance. One
query to show all orders that are scheduled for each workcenter, one
query to show how many scheduled work orders have been completed, etc.,
leading to 6 different queries. Is there a way to combine these
queries into one chart or spreadsheet that I can display on a form???
Hope that made sense.

Thanks,

Matt

[Steven]

I have done this kind of thing a number of times with Access. The most
reliable approach is to: For each or your Querys
1) change to a Make Table Query, give some table name and run
2) Make changes to the design of the created table where necessary,
such as, adding a primary key, change default text size to appropriate
value, etc.
3) Change query from a Make Table query to an Append Query (using same
table name)
4) Creat a new query that will delete all records from that same table

when finished, then create a Macro or VB process that will, for each of
your original queries, one at a time:
turn off warnings - as first step of macro.
a) delete any records from the table used by the query
b) run the append quiery to add the latest data to the table
turn on warnings as last step of macro.

Now using your form, display the data you need from the tables, not
from the queries.
Note this process allows you to computer intermediate results (after
data is extraced but before the results are displayed) using data in
the tables in ways that are difficulte/impossible to do using straight
queries.

matthewemic...@ eaton.com wrote:

I am trying to display information in Access on a form that takes
information from many different queries and puts them together on a
spreadsheet or chart.

Some specific info: The information I am displaying consists of a work order schedule(for the current date), work orders completed , work
orders not completed, a past due schedule, past due completed, and past due not completed. These are grouped by workcenters. Therefore, one
could see on this chart, on any given day, how many orders are
scheduled, how many past due orders remain, how many scheduled
orders/past due orders have been completed, and how many remain by
workcenter.

So... Scheduled orders = Scheduled complete + Scheduled Not Complete

And

Past Due Orders = Past Due complete + Past Due Not Complete

Currently, I have built a separate query that shows each instance. One query to show all orders that are scheduled for each workcenter, one
query to show how many scheduled work orders have been completed, etc., leading to 6 different queries. Is there a way to combine these
queries into one chart or spreadsheet that I can display on a form???
Hope that made sense.

Thanks,

Matt

[Matthew Miclea]

I just tried posting a message and it may have deleted it, so if I am
just repeating what I just said, sorry... anyways, I created the append
queries which append each query into a different table. So now I have 6
different tables all with a "workcenter " column, and each with one extra
column with the specific data it is finding. I created the macro to
delete each table and then append the new data. How do I put this all
together into one table now?? You said to show this all on the form,
however, I am not quite sure what you mean by that. One piece of info:
Each workcenter will not necessarily have information in each query
depending on the schedule.

Thanks,

Matt

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steven]

You have several choices here: - all can achieve the same result.
1) [Elegant] use a UNION query - which can be done in Access but is not
supported by the GUI - to direct the database engine to run serveral
queries and combine the results in a single table. This can be a bit
tricky - I will have to direct you to the HELP Index under UNION
operation for specific details - please note the the first query has to
name all the resulting columns, and you can follow all the queries with
a sort command.
2) Create a single query with a series of left or right outer joins (on
the workcenter column). This is risky because it may work with your
test data - but may inadvertently leave out some data at a future time
because of a missing workcenter
3) [Recommended] Create a new query which does an outer join of just
the workcenter values of all the other tables, Use the properties to
set it to be "unique values" only - then append this to a new table
(cleaned out each time like before) - this table should end up with
every possible workcenter used in each of the other queries - then you
can use an outer join between this table and all of the others tables
to be sure that all of your actual values are included. Create your
Form/Report from this last query - Whether you want to direct the
results of this query to a table first is only a matter of performance.