Inventory System Reporting

[blyxx86]

Hey everyone out there...

I'm not too sure where to start with this one, so... Let me see if I can put some verbs to the flame.

I am need of some guidance when it comes to inventory control using an access database. (Or any database really.)

I am able to keep systemic balances using full cycle counts, receipts and shipments and adjustments. These numbers are accurate for what I use them for, but I want to report based on them and I'm not sure how to do that.

What I'm trying to do requires reporting information based on the last cycle count date (stored in the database) using new cycle count information. How can I create some sort of query that would generate this information for me? I do not need guidance on how to setup a query (maybe a little help with the subquery, if any).

Also, perhaps with the knowledge in this forum someone can direct me to what would make sense to be on this inventory report. (ie. customer, product name, previous cycle count, previous cycle date, new cycle count, new cycle date, difference, etc...)

Suggestions and guidance extremely welcome. Thank you.

[Rabbit]

Hey everyone out there...

I'm not too sure where to start with this one, so... Let me see if I can put some verbs to the flame.

I am need of some guidance when it comes to inventory control using an access database. (Or any database really.)

I am able to keep systemic balances using full cycle counts, receipts and shipments and adjustments. These numbers are accurate for what I use them for, but I want to report based on them and I'm not sure how to do that.

What I'm trying to do requires reporting information based on the last cycle count date (stored in the database) using new cycle count information. How can I create some sort of query that would generate this information for me? I do not need guidance on how to setup a query (maybe a little help with the subquery, if any).

Also, perhaps with the knowledge in this forum someone can direct me to what would make sense to be on this inventory report. (ie. customer, product name, previous cycle count, previous cycle date, new cycle count, new cycle date, difference, etc...)

Suggestions and guidance extremely welcome. Thank you.

What's a cycle count?
Could you provide some pseudo data and the results you're looking for?

[blyxx86]

Sure thing...

A cycle count, a stock take. Is basically a 'full physical count of inventory'.. Perhaps it could just be called an inventory count.

Anyways... Here is the data I have...

Field; Type; IndexInfo
StockTakeID; AutoNumber; PK
Model; String;
StockTakeDate; Date/Time;
Quantity; Number;
 

IE...
StockTakeID: 1
Model: PENCIL
StockTakeDate: 11/2/2007
Quantity: 100
...then...
StockTakeID: 18
Model: PENCIL
StockTakeDate: 11/7/2007
Quantity: 150

I want to somehow show that at the previous stock take that there were only 100, but there are now 150.. Showing a variance of +50 since the last stock take.

So... something like...
MODEL | Previous STDate | Previous STCount | New STDate | New STCount | Variance
PENCIL | 11/2/2007 | 100 | 11/7/2007 | 150 | +50

However I need to do this on a big scale, so a query would be helpful.

Let me know...

[Rabbit]

So bascially you want to take the most recent stock date info and have it next to the second most recent stock date info? Have you ever used subqueries?

[blyxx86]

Only just barely... I managed to make one to pull information, but how would I use this subquery to generate this kind of data?

This is the WHERE part of my main query (with sub)

WHERE (((tblRequest.RequestID) In (SELECT tblRequest.RequestID FROM tblRequest INNER JOIN tblRequestDetail ON tblRequest.RequestID = tblRequestDetail.RequestID WHERE (((tblRequestDetail.EnteredDate)=[Enter Request Date])) OR (((tblRequest.EnteredDate)=[Enter Request Date]));)));

So.. Do I understand how to use them? Yes, but only a little.

[blyxx86]

So bascially you want to take the most recent stock date info and have it next to the second most recent stock date info? Have you ever used subqueries?

Yes, that is correct... I want to display the second set of information next to the current... I believe it will have something to do with a 'LAST' aggregate function, but still a lot to learn.

[Rabbit]

This won't be exactly what you need but it should be enough to get you started:

SELECT x.IDField, x.MaxEventDate, (SELECT TOP 1 EventDate FROM Table1 WHERE IDField = x.IDField AND EventDate < x.EventDate ORDER BY EventDate DESC) AS PriorEventDate
FROM (SELECT IDField, Max(EventDate) FROM Table1 GROUP BY IDField) AS x;
 

Just think of everything after the main WHERE clause as another query that selects the most recent EventDate per IDField and that this query uses the first query as it's source.

[blyxx86]

This won't be exactly what you need but it should be enough to get you started:

SELECT x.IDField, x.MaxEventDate, (SELECT TOP 1 EventDate FROM Table1 WHERE IDField = x.IDField AND EventDate < x.EventDate ORDER BY EventDate DESC) AS PriorEventDate
FROM (SELECT IDField, Max(EventDate) FROM Table1 GROUP BY IDField) AS x;
 

Just think of everything after the main WHERE clause as another query that selects the most recent EventDate per IDField and that this query uses the first query as it's source.

This kind of confused me.. I'll have to read up on subqueries some more because while I understand their simplicity, I get confused on the logic to use in them. I sort of understand, but I sort of don't. :) I'm just a walking antonym today.

[Rabbit]

This is what it means.

Query1 - Select most recent EventDate by IDField

SELECT IDField, Max(EventDate)
FROM Table1
GROUP BY IDField;
 

Query2 - Select second most recent EventDate by IDField using a subquery.

SELECT outerQuery.IDField, outerQuery.MaxEventDate, 
 
(SELECT TOP 1 innerQuery.EventDate
FROM Table1 AS innerQuery
WHERE innerQuery.IDField = outerQuery.IDField AND innerQuery.EventDate < outerQuery.EventDate
ORDER BY innerQuery.EventDate DESC) AS PriorEventDate
 
FROM Query1 AS outerQuery;