Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Multiple queries into one report or table

Discussion in 'Information Technology' started by arznwildcat@gmail.com, Jul 28, 2006.

  1. arznwildcat@gmail.com

    arznwildcat@gmail.com
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    <arznwildcat@gmail.com> wrote in message
    news:1153951259.750539.305410@b28g2000cwb.googlegroups.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]
    > ---------------------------------------------------------------------------------------------------------
    > 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
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    <arznwildcat@gmail.com> wrote in message
    news:1153951259.750539.305410@b28g2000cwb.googlegroups.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]
    > ---------------------------------------------------------------------------------------------------------
    > 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.
    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
     
  5. tedb

    tedb
    Expand Collapse
    Guest

    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!!!
     

Share This Page