Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Stumped on creating a tough query (Access 2003, Win XP)

    I'm having trouble figuring out the correct SQL statement (or creating in design view) to generate the type of query I want. I have 2 tables, tblJobList and tblUtilProd. JobList shows a job name, #, and a description and (bid) quantity for Phase Codes 1-60 (some may be null). UtilProd shows job # (1 to many relationship with JobList), date, Phase Code (the number, not the description), and quantity (for the day). I'm trying to figure out a query (which will become a report) which will display (for a given job #):

    First column: Phase Code descriptions (from JobList)
    Second column: Bid Quantity (from JobList)
    Columns 3-....: One column for each day of the month, with daily quantities for each Phase Code going down.
    Last column: Total accross the rows

    (if this is only possible by switching my ideal results from rows to columns and vice versa, that would be fine too)

    Any help would be sincerely appreciated. See attached file as well!

    Much obliged!

    B. Drake

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stumped on creating a tough query (Access 2003, Win XP)

    The design of tblJoblist makes it very hard to do anything with it. You should have three tables instead:

    1. tblJoblist with just the fields Job# (primary key) and JobName. I would rename Job# to JobNo, the # will require you to enclose the field name in brackets [ ] in a lot of places, causing extra work.

    2. tblPhCodes, with two fields: PhCodeNo (autonumber, primary key) and PhCode (text). This table contains unique phase codes.

    3. tblJobPhases, with four fields: JobNo (number, integer; linked to JobNo in tblJoblist), PhaseNo (number, long integer), PhCodeNo (number, long integer; linked to PhCodeNo in tblPhcodes, and BidQty (number, long integer - don't use Decimal). The primary key in this table is on the combination of JobNo and PhaseNo. If there are 37 phases to a job, there will be 37 records for this job in tblJobPhases.

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stumped on creating a tough query (Access 2003, Win XP)

    I figured you might say something like that. What makes it really tough is that the Phase Codes are different for each job number. For example, P.C. 1 for job 5003 might be 8" RCP, but P.C. 1 for job 5010 might be 6" Main. Do you think it would work using your suggestion, but having a different tblPhCodes for each job number? Also, where in your example would be the best place to include the dates and the qty of each phase completed on a given day?

    Phases is really a bad word for that field, but of course that's not my call. They really shoud be called 'items' So a job with 40 Phase Codes really just has 40 different types of items involved in construction.

    Thanks, as always, you guys are the best!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stumped on creating a tough query (Access 2003, Win XP)

    If there is a fixed list of items (phase codes, whatever) such as 18" RCP, it makes sense to put them in a separate table.

    You would still use the tblUtilProd table to keep track of dates and quantities. What I don't understand, however, is the relationship between the various quantities. You now have several records for the same JobNo, PhaseCode and Date in tblUtilProd. Do they relate to BidQty in tblJobList?

    The screenshot below is probably incorrect for the above reason.

  5. #5
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stumped on creating a tough query (Access 2003, Win XP)

    BidQty is more just an FYI for the big boss man. Every PhaseCode (or 'Item') has an inital bid quantity, and the foremen keep track of their daily production on any given Item. In the end, I suppose all I'm really trying to create is a report for the boss man to look at. Ideally, he would like it in this format.

    A report only covers one job, so for example, job #5003:


    5003 - Job Name
    May 2005
    | 5/1/05 | 5/2/05 | 5/3/05 | 5/4/05 | 5/5/05 | --------------->
    PhaseCode | Description (of PhaseCode) | BidQty |
    ---------------------------------------------------------------------
    | | |
    | | |
    V V V
    22 | 18" RCP Water | 10 | | 3 | 2 | | |
    24 | 6" Main Sewer | 765 | 230 | | 160 | | 80 |

    (or the same design rotated 90 degress)

    Thanks again Hans!


    I just realized it didn't preserve the spacing on my pathetic illustration.

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stumped on creating a tough query (Access 2003, Win XP)

    <hr>I just realized it didn't preserve the spacing on my pathetic illustration.<hr>

    I've done that more than a few times myself <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stumped on creating a tough query (Access 2003, Win XP)

    The report design comes later. It would probably be a crosstab report, which is fairly complicated, but you'll have to get the table design right first.

  8. #8
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stumped on creating a tough query (Access 2003, Win XP)

    I created a crosstab query that almost gives me what I want. First, I created a table for one specific job with 3 columns, PhaseCode (AutoNumber), PhaseCodeDescrip, and BidQty.
    These three columns in this table are acting as row headings in my crosstab query. I have dates accross the top as column headings, and daily quantities (Qty from tblUtilProd) as the Value. This is almost exactly what I need. The last thing I'm trying to figure out is how to get it to show every PhaseCode down the left-most column, regardless of whether any Qtys have been entered in tblUtilProd or not.

    Thanks again!

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stumped on creating a tough query (Access 2003, Win XP)

    If you have a table listing all phase codes (60, wasn't it?), you can create a new query based on this table and on the crosstab query, linked on phase code. Double click the join line and specify that you want to return ALL records from the table. Add the phase code from the table, and the other fields from the crosstab query.

    (But it's going to be difficult if new dates are added)

  10. #10
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stumped on creating a tough query (Access 2003, Win XP)

    You're right.
    It works great, but as you said, adding dates throws a kink in there. Unfortunately, that will be the case, as dates will be added for an unknown amount of time until the job is completed. Is there a way to automate the process? Realistically, dates will probably all be added once a week, with a week's worth of dates being added.

    Appreciate as always kind sir!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stumped on creating a tough query (Access 2003, Win XP)

    Do a search for dynamic crosstab report in this forum. There are many threads about this subject, several of them have demo databases attached.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •