Multiple queries into one report or table

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]
---------------------------------------------------------------------------------------------------------
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/14008-multiple-queries-into-one-report-table.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14008
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

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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

wrote in message
news:1153951259.750539.305410@b28g2000cwb.googlegr oups.com...
>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14008
> 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]
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14008
> ---------------------------------------------------------------------------------------------------------
> 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
>

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

wrote in message
news:1153951259.750539.305410@b28g2000cwb.googlegr oups.com...
>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]
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14008
> ---------------------------------------------------------------------------------------------------------
> 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
>



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14008
It has removed 4367 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

The reason that I made separate queries with unique calculations, is
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14008
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14008
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!!!