Multiple queries into one report or table

#1 (**permalink**) 28-Jul-2006, 08:42 AM

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

#2 (**permalink**) 28-Jul-2006, 08:42 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14008
> 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]
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14008
>
> 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
>

#3 (**permalink**) 28-Jul-2006, 08:42 AM

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...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14008
>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]
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14008
>
> 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.
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

#4 (**permalink**) 28-Jul-2006, 08:42 AM

The reason that I made separate queries with unique calculations, is
because I have never seen or come across anything that looked like a
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14008
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14008
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!!!