Multiple queries into one report or table

[arznwildcat]

I am very new to the whole Access program. Over the past couple of
weeks I have managed to create a very nice front end for a somewhat
complex database. I probably did it backwards, but now I am in need of
help with the raw data.
I will do my best to explain and then clarify if need be.
I am creating a Work Order Time Line tracking program. The Work
Orders each have a unique number (used as the Primary Key). These Work
Order numbers are kept on a Master Work Order table. The Work Orders
are able to fall into eight different Work Sequences (some have more or
less steps to get to the final process). Each of these Sequences has
its own table, which are all linked back to the Master table via the
Work Order number. Each of these Sequence tables has criteria that has
to be entered by the user and is specific to that Sequence table/type.
Each table has a query that is built to return dates specific to
that sequence type. A query can return anything from one date for a
Work Order to 6 dates, again depending on the type of sequence that it
is designed for. So this creates eight unique qeries also. I have
created unique reports that match the different queries.
All of this is working great so far. However; my goal is to be able
to generate a report, or table that I could use to see all of the Work
Orders along with the information that is generated by their respective
sequence query.
I'll try to illustrate it now using only 2 examples:

tblMasterWR:
[WorkOrder(Primary Key)] [Designer] [Area]
---------------------------------------------------------------------------------------------------------
tblOverheadOnly:
[WorkOrder(linked to master)] [StartDate] [DayToOffset]

qryOverheadOnly:
[WorkOrder(linked to master)] [OffsetDate] [CompletionDate]
---------------------------------------------------------------------------------------------------------
tblOverheadPay:
[WorkOrder(linked to master)] [Start Date] [DaysToPay]
[DaysToOffset]

qryOverheadPay:
[WorkOrder(linked to master)] [PayReceiveDate] [OffsetDate]
[CompletionDate]

etc...

It is built so that each table and query cannot create a duplicate Work
Order number. I don't think creating a master query and choosing
criteria in there is an answer, because each sequence varies in its
underlying math to find the dates that it returns.
I think that maybe a report with eight subreports might work, but
it seems way to cumbersome and I currently have no idea how I would set
one up. Also, I think a subreport would segregate the information too
much.
I hope that I have given enough information. These forums have been
a HUGE help in getting me to this point, hopefully anyone can help me
get past this.

Thanks!
-Ted

 

[Pieter Wijnen]

I'm sorry to say this but you have to learn more about relational databases,
me thinks.
there's no reason I can think of for having seperate Sequence tables.
you should instead make a SequenceSteps table, linked to the Sequence table,
that identifies the steps needed.
that way you don't have to play around with a lot of (what) if's

Sorry if I seem gruff

Pieter

I am very new to the whole Access program. Over the past couple of
weeks I have managed to create a very nice front end for a somewhat
complex database. I probably did it backwards, but now I am in need of
help with the raw data.
I will do my best to explain and then clarify if need be.
I am creating a Work Order Time Line tracking program. The Work
Orders each have a unique number (used as the Primary Key). These Work
Order numbers are kept on a Master Work Order table. The Work Orders
are able to fall into eight different Work Sequences (some have more or
less steps to get to the final process). Each of these Sequences has
its own table, which are all linked back to the Master table via the
Work Order number. Each of these Sequence tables has criteria that has
to be entered by the user and is specific to that Sequence table/type.
Each table has a query that is built to return dates specific to
that sequence type. A query can return anything from one date for a
Work Order to 6 dates, again depending on the type of sequence that it
is designed for. So this creates eight unique qeries also. I have
created unique reports that match the different queries.
All of this is working great so far. However; my goal is to be able
to generate a report, or table that I could use to see all of the Work
Orders along with the information that is generated by their respective
sequence query.
I'll try to illustrate it now using only 2 examples:

tblMasterWR:
[WorkOrder(Primary Key)] [Designer] [Area]
---------------------------------------------------------------------------------------------------------
tblOverheadOnly:
[WorkOrder(linked to master)] [StartDate] [DayToOffset]

qryOverheadOnly:
[WorkOrder(linked to master)] [OffsetDate] [CompletionDate]
---------------------------------------------------------------------------------------------------------
tblOverheadPay:
[WorkOrder(linked to master)] [Start Date] [DaysToPay]
[DaysToOffset]

qryOverheadPay:
[WorkOrder(linked to master)] [PayReceiveDate] [OffsetDate]
[CompletionDate]

etc...

It is built so that each table and query cannot create a duplicate Work
Order number. I don't think creating a master query and choosing
criteria in there is an answer, because each sequence varies in its
underlying math to find the dates that it returns.
I think that maybe a report with eight subreports might work, but
it seems way to cumbersome and I currently have no idea how I would set
one up. Also, I think a subreport would segregate the information too
much.
I hope that I have given enough information. These forums have been
a HUGE help in getting me to this point, hopefully anyone can help me
get past this.

Thanks!
-Ted

 

[Pieter Wijnen]

I'm sorry to say this but you have to learn more about relational databases,
me thinks.
there's no reason I can think of for having seperate Sequence tables.
you should instead make a SequenceSteps table, linked to the Sequence table,
that identifies the steps needed.
that way you don't have to play around with a lot of (what) if's

Sorry if I seem gruff

Pieter

I am very new to the whole Access program. Over the past couple of
weeks I have managed to create a very nice front end for a somewhat
complex database. I probably did it backwards, but now I am in need of
help with the raw data.
I will do my best to explain and then clarify if need be.
I am creating a Work Order Time Line tracking program. The Work
Orders each have a unique number (used as the Primary Key). These Work
Order numbers are kept on a Master Work Order table. The Work Orders
are able to fall into eight different Work Sequences (some have more or
less steps to get to the final process). Each of these Sequences has
its own table, which are all linked back to the Master table via the
Work Order number. Each of these Sequence tables has criteria that has
to be entered by the user and is specific to that Sequence table/type.
Each table has a query that is built to return dates specific to
that sequence type. A query can return anything from one date for a
Work Order to 6 dates, again depending on the type of sequence that it
is designed for. So this creates eight unique qeries also. I have
created unique reports that match the different queries.
All of this is working great so far. However; my goal is to be able
to generate a report, or table that I could use to see all of the Work
Orders along with the information that is generated by their respective
sequence query.
I'll try to illustrate it now using only 2 examples:

tblMasterWR:
[WorkOrder(Primary Key)] [Designer] [Area]
---------------------------------------------------------------------------------------------------------
tblOverheadOnly:
[WorkOrder(linked to master)] [StartDate] [DayToOffset]

qryOverheadOnly:
[WorkOrder(linked to master)] [OffsetDate] [CompletionDate]
---------------------------------------------------------------------------------------------------------
tblOverheadPay:
[WorkOrder(linked to master)] [Start Date] [DaysToPay]
[DaysToOffset]

qryOverheadPay:
[WorkOrder(linked to master)] [PayReceiveDate] [OffsetDate]
[CompletionDate]

etc...

It is built so that each table and query cannot create a duplicate Work
Order number. I don't think creating a master query and choosing
criteria in there is an answer, because each sequence varies in its
underlying math to find the dates that it returns.
I think that maybe a report with eight subreports might work, but
it seems way to cumbersome and I currently have no idea how I would set
one up. Also, I think a subreport would segregate the information too
much.
I hope that I have given enough information. These forums have been
a HUGE help in getting me to this point, hopefully anyone can help me
get past this.

Thanks!
-Ted

--

 

[tedb]

The reason that I made separate queries with unique calculations, is
because I have never seen or come across anything that looked like a
query that different criteria could be used depending on how the main
item needed to be handled. If there is such a thing that is easy to
create, manuever, and will get me to where I want to be, I am all for
it. Please let me know how I can create something like this.

I still would like to know if anybody has other ideas to use existing
data.

Thanks for any help!!!