1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Temp storage for query results??

Discussion in 'Business Applications' started by Shlaga, Nov 12, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. Shlaga

    Shlaga Thread Starter

    Joined:
    Oct 23, 2007
    Messages:
    41
    Hi,

    I have a query in access that outputs several part numbers with different shipment dates. I have same shipment date for different part types and same part types with different shipment dates. The query asks the user to enter the shipment date. I want to output all the same part types with their different shipment dates in temporary tables or a temporary location.

    My ultimate goal is to graph this information by part type, (the query also outputs the precent rejected for the part type for e/ shipment date)..

    Does anyone have an idea how to do this or somewhere where i can find some information??

    Thanks!
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    When you enter a date, your query shows all the parts that have to be shipped on that date, is that right? Yet it sounds like what you want is a report that shows all of a part type with the different shipping date - so you would be wanting a query that asks for a part type. Alternatively, you could just pull all of your data into a report grouped by part types and sorted by dates - using the report wizard makes this very easy.
     
  3. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Change the query type to a make-table query. You can always delete the table whenever you're done with it. Open the query in design view, then hit Query-->Make table query.
     
  4. Shlaga

    Shlaga Thread Starter

    Joined:
    Oct 23, 2007
    Messages:
    41
    Hey,


    Thanks for the suggestions..!!

    The table idea doesn't work well because it jus outputs all the information in one table...the report idea, is better but i am sort of new to this area, so some help did be much appreciated. Can we include graphs in the report as well and if so how? Also the queries are linked to a user form and i want it so that with one click of a bttn the report be automatically generated for the user...is this possible?

    the query is as follows:

    SELECT t1.[Item Num], t1.[Shipment Date], (t1.SumNumDefected/t1.SumBatchSize)*100 AS PercentRejected
    FROM qryGetSummaryStaub AS t1;

    and the output is something like this (a part of it):
    Item Num Shipment Percent
    Date Rejected
    BE180-1 08/23/07 4.8
    BE180-1 08/30/07 0
    BE180-1 09/06/07 0
    BE180-1 09/13/07 0
    BE180-1 09/20/07 0
    BE180-1 09/27/07 0
    BE180-1 10/04/07 0
    BE180-1 10/11/07 0
    BE181-1 08/16/07 0.273972602739726
    BE181-1 08/23/07 0
    BE181-1 08/30/07 0
    BE181-1 09/06/07 0
    BE181-1 09/13/07 0
    BE181-1 09/20/07 0


    My goal is to graph the percent rejected Vs. the shipment date for each distinct part number, there are about 20-30 distinct part numbers.
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    If you could zip and post your file and be certain to explain in detail what you want to be able to do, someone will work on it and help you out. As for your question about generating a report with a click of a button - that is very easily done. Graphs are also very doable.
     
  6. Shlaga

    Shlaga Thread Starter

    Joined:
    Oct 23, 2007
    Messages:
    41
    Unfortunately enough i can't do that as the file is simply too big even if i zipped it and data in the databases is confidential.

    I sought of figured it out though, i ouputted the query results to a table and created a report from the table grouped by the part numbers, but when i use the insert->chart->line chart, i can't seem to change the axis to what i want to, take a look at the .pdf file attached which shows the snapshot of the chart wizard.

    Instead of 'shipment date by month' i jus want to take the date from the table for the x-axis and instead of 'SumofPercentRejected' i just simply want it to show the 'Percent rejected' from the table... Is that possible or can access chart wizard not do something like that??
     

    Attached Files:

  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    It can do both those things - however, it would be far easier if you made a copy of your database, deleted the actual data and inserted several rows of similar - but fake - data. Any tables with client names, etc., should be changed also. I would strongly recommend you do this, however, even though you may initially think it time-consuming, for you will get a build here doing everything you want faster than you will be able to work it out...(personal experience!!!!) :)
     
  8. Shlaga

    Shlaga Thread Starter

    Joined:
    Oct 23, 2007
    Messages:
    41
    Okay, so here is the fake access file. I just made a table of the type of output i would expect and also the type of query i am currently using that takes info from the 'fake table' and outputs it in the other table , this table ('Table from Query used in Report) is to used in making the report.

    Basically i want a graph for each Distinct Item Num , so one graph for BE183-1, one graph for BE184-1 so on and so forth
    this graph should display the percentrejected vs the shipment date. I don't want the percent rejected to be summarized i.e (sum, avg, min, max or count).


    Hopefully you can solve my dilemma...:(
     

    Attached Files:

  9. Shlaga

    Shlaga Thread Starter

    Joined:
    Oct 23, 2007
    Messages:
    41
    oh also, i made a report, this report basically is what i want, if you open the report in design view all anyone would have to do is add the graph...and group it by the 'Item Num' so that it displays a graph for each Item num...check it out

    Thanks
     
  10. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    dang - no rar file opener on my machine - are you able to save it in zip format?
     
  11. Shlaga

    Shlaga Thread Starter

    Joined:
    Oct 23, 2007
    Messages:
    41
    lemme check
     
  12. Shlaga

    Shlaga Thread Starter

    Joined:
    Oct 23, 2007
    Messages:
    41
    There you go, the one below is in zip format.
     

    Attached Files:

  13. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Sorry, not yet - boss came in and dumped a bunch of work on my desk.... :(
     
  14. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    You might not know it, but you are looking for an MS Access pivottable. Open the query results and select "View" from the menu bar. From here select "pivottable." Add the Part Number field for the rows on the left, the date field for columns and the percentage rejected as the data field smack dab in the middle. Let me know if you like this and it works out for you. You can also do something similar to this is a Crosstab query as well.
     
  15. Shlaga

    Shlaga Thread Starter

    Joined:
    Oct 23, 2007
    Messages:
    41
    i want to create a graph though and i tried using the pivotchart and it still want to summarize the percent rejected to like a (sum, avg, min, max, count) i just want to graph the individual values against the shipment date for each part type.:(
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Temp storage
  1. Tamekay25
    Replies:
    5
    Views:
    213
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/651042

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice