How to generate report to manage inventory dynamicaly?

[huskerfan]

Hello all!



This is my first post, so bear with me.

I need to create a report that will track the inventory of some pieces of equipment I have. The report must only track the Part Number, Location, and quantity.

Problem is, Part A can have Qty. 2 in location A, Qty. 4 in location B, and Qty. 0 in location C.

Also, Location A may have 2 of Part A and 3 of Part B but 0 of Part C.

Basically what I want to do is generate a report based off of an active table that will give me the part number and location of every "asset".

From this I would like the report to be in somewhat of the following format:

 
Part Number | Location A | Location B | Location C | etc
1234567890  |      1     |      4     |     0      | ...
0987645321  |      0     |      3     |     2      | ...
7894561230  |      4     |      0     |     2      | ...

I can generate the report I want by using "hard-coded" labels and using the following formula for the numbers inside of the report:

=sum(iif([MODEL]=1234567890,iif([LOCATION]="A",1,0),0))

However, I want to do that by not putting in every model number and every location because they can change, and I would have to re "hard-code" my report everytime I edited a location or added a part number.

Any help would be greatly appreciated.

Thanks

[June7]

Is source table set up with fields like Model, Location, Quantity? Try this query:
SELECT Model, Sum(IIf([Location]="A",[Quantity],0)) AS LocA, Sum(IIf([Location]="B",[Quantity],0)) AS LocB, Sum(IIf([Location]="C",[Quantity],0)) AS LocC
FROM Table1
GROUP BY Model;

[huskerfan]

The source table is set up as an asset database. Meaning that each record is one individual asset.

For instance:
Asset 0001 may be part A, location B
Asset 0002 may be part A, location A
Asset 0003 may be part B, location A
Asset 0004 may be part A, location A

So I need to figure out how many of part A's are in location A, how many in location B, how many in location C... etc, for each Part A thru Part etc...

So really the only thing I have to work with is a query that I have right now that generates a list of every part of the subset I am interested in (I don't care about all of the parts in the large table, just the subset in my query) and it's location.

I need some way to count the number of times a specific part number appears in each location, with each record being its own individual asset

[June7]

Okay, instead of Sum on Quantity, Sum 1 or 0:
SELECT Part, Sum(IIf([Location]="A",1,0)) AS LocA, Sum(IIf([Location]="B",1,0)) AS LocB, Sum(IIf([Location]="C",1,0)) AS LocC
FROM Table1
GROUP BY Part;

This will output summary data. If you want to view the detail records that contribute to the sums then omit the Sum function, keep the IIF expressions, and omit GROUP BY and use this query as the RecordSource for a report. Then use the report Grouping & Sorting with summary calcs to show both details and summary info.

How many locations? If the locations are too many or variable, then this will not work. Need consistency. Otherwise, just do a report with grouping and sorting on the raw data.

[huskerfan]

I tried the sql statement that you posted with just a few locations... works perfectly!

What is the variable limit for an sql statement?

I have about 20 locations that I could possibly have... however if that is too many I could cut down the locations to only the ones I really need to keep track of which would be about 10 locations.


Also, Is there a way to make Access generate this without me typing in the physical location names? For instance using your SQL statement, instead of IIf([Location]="A",1,0)) AS LocA, make it to where access finds the next location without me putting in the locations names in the quotes. Something like sum(IIf([Location]=First encountered location,1,0)) AS firstlocation, sum(IIf([Location]=Second encountered location,1,0)) AS secondlocation?

Or is that even possible?

[June7]

There is a limit to how many fields a table or query can have (256), so 20 locations is not a problem for the query, but displaying in a report is another issue. Would require a landscape orientation. However, there is also a string length limit for an SQL statement. Don't know if you will hit it.

As for the dynamic field names, this would require elaborate VBA code to construct the SQL statement and then save it as a table or query object or set the RecordSource property of a form/report. And then you would have to modify form/report every time to fit the revised field names, which I think could also be done programmatically (I never have).

A crosstab query should be able to produce the summary output dynamically but there would not be record details. And again, would have to modify report to reflect the varying field names. Because locations with no parts will not show in the crosstab query unless the detail table is first joined to a master table of all possible locations and then the data is transformed.

[huskerfan]

That seems very logical to me.

I may attempt the VBA code later, just to see how it's done, but for now the SQL statement with the locations is fine, the locations will very rarely change.

Thanks for all your help June7, you gave me exactly what I wanted!