Tyberius Tyberius - 4 months ago 11
SQL Question

How to disperse values to lines in access

We are building a cycle counting app in MS Access (no other option for now) for my company.

The premises is that you have lines that state where a part is and the quantity. The same part can be duplicated multiple time because they are separated by date. We could like to tell the worker to count the total items , enter that in the system and then the system would disperse the amount across the lines starting with the first (ordered by date) and giving the rest to the next line if possible)

For example the desired output is table 3. please note how the earliest gets the count till it is full then goes to the next line if there is any remaining.

Table 1: item inventory

| Part | Date entered| expected quantity|
|:-----------|------------:|:------------:|
| x | 9 | 1
| x | 10 | 3
| x | 11 | 2
| y | 9 | 1
| y | 10 | 3
| y | 11 | 1
| z | 12 | 1
| z | 13 | 1
| z | 14 | 1


Table 2: item count by employee

| Part | Counted | expected quantity by item|
|:-----------|------------:|:------------------------:|
| x | 3 | 5
| y | 5 | 4
| z | 1 | 2


Table 3: item inventory with counts

| Part | Date entered| expected quantity| Count| Variance
|:-----------|------------:|:----------------:|------|---------|
| x | 9 | 1 | 1 |0
| x | 10 | 3 | 3 |0
| x | 11 | 2 | 1 |1
| y | 9 | 1 | 1 |0
| y | 10 | 3 | 3 |0
| y | 11 | 1 | 0 |1
| z | 12 | 1 | 1 |0
| z | 13 | 1 | 0 |1
| z | 14 | 1 | 0 |1


I know how generate the second table and the input but do not know how to loop through the previous rows to generate the count on the third column.

Preferably this would be via a query.

Answer
SELECT [Item inventory].Part, [Item inventory].[Date entered], [Item inventory].[Expected quantity], [Item count by employee].Counted, (select sum([expected quantity]) from [item inventory] T where t.part=[item inventory].part and t.[date entered]<=[item inventory].[date entered])-[expected quantity] AS [Prior], IIf([prior]>[counted],0,IIf([expected quantity]<[counted]-[prior],[expected quantity],[counted]-[prior])) AS [Current], [current]-[expected quantity] AS Variance
FROM [Item inventory] INNER JOIN [Item count by employee] ON [Item inventory].Part = [Item count by employee].Part
ORDER BY [Item inventory].Part, [Item inventory].[Date entered];

This provides what you asked for, I think - but a few more comments:

  • I think your Table 3 may be wrong, since for part X you expected 6, counted 3 but only showed a variance of 1
  • This query won't show a variance if the total counted is greater that the number expected, which for a cycle count you may need to know
  • Just for my own curiosity - why do you need to allocate the actual counts to dates? When I've done cycle counts, it's only the total and the variance that mattered