Results 1 to 6 of 6
  1. #1
    sdmikejr is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2014
    Posts
    3

    Design appears correct, but now to create correct query to make report output needed...

    Okay, All tables and relationships are setup. All data is in tables. Now to the fun part...setting up the queries to create the output in the reports.



    The objective of this database is to basically have a 6-12-view of each products' purchases orders, receipts, sales (POs s/b received within the same month placed. As a result, any qty not received is a backorder and will be added to following month's Receipts). Finally, the M/E inventory from prior month + Receipts-Sales=Inventory, which is what I was going to have calculated.

    I have tried to use crosstab query for each of the 3 variables that has months on top and products on side, with quantity in values-then have a report use all 3 crosstab queries as the source. . . yeah-so I learned report can only have one query source. I am stumped...please please please help me.

    I have attached a snapshot of the relationships for reference, and will show below what I am trying to acheieve in the format of a report. If any MVP guru can be obliged to help someone learning from the ground up and needs a boost that would totally awesome!! (and bring you good luck)

    (assuming no inventory in DEC)

    REPORT OUTPUT:

    JAN FEB MAR
    Product A
    Purch Order Qty 12 10 15
    Received Qty 10 12 15
    MIT/BackOrder 2
    Sales 8 4 30
    *calc M/E Invent. 2 20 5


    Seems conceptually easy, but execution to get the row headings aligned with the months (in 1 report) is essentially what I am struggling with.

    Thanks for any help in advance.
    Attached Thumbnails Attached Thumbnails screen shot.JPG  

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I typically do this one of two ways, with a crosstab, or with a formula. I don't really like using crosstabs because they can be a pain to run reports on so try a formula like:

    Jan: iif(cint(datepart("m", [PurchaseDate])) = 1, [POQuantity], 0)

    This is saying if the month of the date you're interested in is 1 count the quantity, otherwise count it as 0

    You would then extrapolate this formula for each of the 12 months of the year.

  3. #3
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Hmm, making a suggestion... Goods received would be better served attached to Purchase Order Line Items since it could happen that not all Products are received at the same time. Potentially you could incorporate into that table as really no need for a separate table if with Line Items. Just a thought...

  4. #4
    sdmikejr is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2014
    Posts
    3
    Okay, this works for the Purchase Order section, thanks! Now, how can I total the same columns for what was received within the same months? Just in case the initial request was confusing I will try to explain again: I need to calculate inventory monthly, but going out 6 months.
    I am currently doing this in excel as shown below, by product, by month:

    Product APR MAY JUN
    productA PO 10 20 20
    Rcvd 5 25 20
    BkOrder 5 0 0
    Sales 5 5 10
    M/E Qty 0 20 30

    I go out 6-months and have multiple products. Using your idea, I have the first row "PO" setup in the format I need. Would I now build the same type of query for each row heading "Rcvd", "BkOrder", and "Sales" and then build a query that brings it all together for 1 month, and then do that for all 6-months??, or is there another method to going about this? I soo appreciate your help and support in helping me learn. I have kind of been told if I cannot make this happen it, (I) may need to be outsourced. Any advice will be much appreciated.

  5. #5
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    I can't tell by your response who you are saying "Okay, this works for the Purchase Order section...". If you are directing that to me, please upload a new image of your table layout so we can provide a query using the correct field names. Also, need to see the layout to know which fields to include.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I'm not sure to whom the original poster is responding, but if it's to my post, if you want received, backorder and sales to all be available for reporting purposes then yes, you'd need a different formula for each category for each month. You could them add them together on the report itself rather than in a query.

    If you are also trying to look at 6 months only and you want to have the same query be 'runnable' regardless of what time period (or starting date) you enter that's also possible, but then you are likely looking at creating 3 separate crosstabs then linking them together in a 'final' query and doing some magic with setting up a dynamic report based on the final query.

    I posted an example of a dynamic report based on a crosstab query here:

    https://www.accessforums.net/sample-...ery-45032.html

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-19-2012, 10:54 AM
  2. Replies: 12
    Last Post: 06-08-2012, 02:37 AM
  3. Replies: 4
    Last Post: 03-05-2012, 10:20 AM
  4. Replies: 3
    Last Post: 12-19-2011, 12:46 PM
  5. Replies: 9
    Last Post: 06-26-2011, 09:14 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums