Really stumped newbie here

[alwittlich]

Hi folks. New to access. Like super new, so please be nice. Here's what I'm trying to do...



I work in manufacturing and am trying to track the weight and cost of steel that is bought for one project and used on another.

I've been fooling around and have been able to set up a simple database to do this. Just used a few entries so I can play around and figure stuff out. Basically, I have the following fields: Purchased For. Transferred To. Size. Quantity. Weight. Invoiced Price.

I have been able to sort and make reports and even do fancy stuff like have the report show the sum of the quantity and weight and give total weight for the size of material, but here is what I ultimately want to do.

Say I have a bunch of entries for material purchased for project "A" that show material being transferred to project "B". I need to find a way to report all of the data for A and also for B. By this I mean I want access to see that in a row of data for project A there is also data from project B and to pull data from this line and put it in the totals for project B as well as project A.

Like for the totals on project A it would somehow give me the data not only for the lines showing material transferred out of A, but also the lines for material transferred in to A. I've grouped on "purchased for" and grouped on "transferred to" in two different reports thinking maybe there is a way to pull this data per project and make a nice report that finds all of the project numbers and can sum it all up. I need a report that will show me everything transferred in and out of a project without me having to look for them manually. Jeez, this is really hard to explain. Sorry. ANY help will be appreciated. I've got a lot of folks wanting to know where there money went.

Thank you.

[June7]

The suggestion I have requires that you have a master table of all projects. Then do two saved queries grouped on each the PurchasedFor and TransferredTo fields (like the RecordSources for the two reports you built). Now do a query that joins these three objects -the two queries to the Projects table. The jointype on each join will be 'show all records from Projects and only those from...'

Not sure this is what you really want. This will show what has come and gone from each project but not specifically where. So try this - a single query but GroupBy on both PurchasedFor and TransferredTo fields.
SELECT PurchasedFor, TransferredTo, Sum(Size) AS SumOfSize, Sum(Quantity) AS SumOfQuantity, Sum(Weight) AS SumOfWeight, Sum(IvoicedPrice) AS SumOfIvoicedPrice
FROM table name
GROUP BY PurchasedFor, TransferredTo;

[alwittlich]

Hey, Thanks! I think the second option looks closer to what I'm trying to accomplish.

So When I do this and try to run the query it asks for me to enter "parameter values" for each of the filds that I entered with SumOf in front of them. Am I maybe doing something incorrectly or does this sound right so far?

If so, what do I enter in the parameter values? Is there a way to select all of the materials? I tried typing in the data for one size of material and it gave me back a table with all of the project numbers for the purchased fors and transferred tos, but all of the other values came back the same, i.e. for the one size of material that I entered in as the parameter.

Thanks again for your help.

[June7]

Create a grouping query with the query designer. Select table and drag fields to the grid. While in DesignView (the design grid) click the Totals button (looks like Sigma). Then in the grid Total row select either GroupBy or Sum. This is essentially what the Grouping and Sorting features in the report designer do for you.

[alwittlich]

I was able to do that, thank you for the clear instructions, but when I try to run it or make a table out of it, it is asking for parameter values. I guess I don't really understand what those are. What do you think I should be entering for parameters? Thank you.

[June7]

The parameter prompts are popping up because the query engine does not recognize some field names in the query and treats them as a user input prompt. If you built the query in the designer by drag/drop fields, this should not happen. Post the SQL statement for analysis.

Why would you make a table?