Query Miscounting

[kolbep]

Hi Guys.
Me again.

I am busy with a stock control database.
I have several tables, the ones that I am having a problem with are as
follows:
tbl_Stock - Stock Descriptions, reorder levels, etc
tbl_Invoicing - When items are booked out of stock
tbl_GrvItems - The specific items that are received
tbl_GrvSuppliers - Lists what supplier, their Inv num, etc.

I Have a query that is supposed to give me the current stock levels
(Here is the SQL)
---------
SELECT tbl_Stock.Description, Sum(tbl_GrvItems.Qty) AS GRV,
Sum(tbl_Invoicing.Qty) AS Inv,
Sum(Nz([tbl_GrvItems].[Qty])-nz([tbl_Invoicing].[Qty])) AS Onhand
FROM tbl_GrvItems RIGHT JOIN (tbl_Invoicing RIGHT JOIN tbl_Stock ON
tbl_Invoicing.StockItem = tbl_Stock.StockID) ON tbl_GrvItems.StockItem =
tbl_Stock.StockID
GROUP BY tbl_Stock.Description;
---------

But now it seems I have a problem when I have Invoiced out say 2 of an Item,
and then later 1 of that item. The query sums get totally wrong the Inv:Qty
does its sums wrong and says 4 of that item have been booked out.

Please Help

I will send any more info you need

Thanks
Peter




--- news://freenews.netfront.net/ - complaints: (e-mail address removed) ---

 

[Tom van Stiphout]

I have a more fundamental comment. It seems you are saying:
Onhand = Received - Sold
In most companies it is more complicated than that. For example it is
common to occasionally perform a Stock Take where each item is counted
as to current levels. The formula then becomes:
Onhand = Latest Stock Take + Received since then - Sold since then
As you bring up, returned merchandize needs to be accounted for, and
there probably are a few more.

Design before you implement!

-Tom.
Microsoft Access MVP

 

[Peter Kolbe]

Hi Tom.
Perhaps background will be better here.
I am an Electrician working in a (Mostly) Family business (+-10people).

The Company used to use Revelation accounting software just to control their
stock, which was a bit cumbersome as it is a full accounting package. There
was a situation where about 5 months of information on this program was
overwritten (I know, I know , backups!).
Anyway the boss decided he wanted an easier system that could be tailored to
how they operate. A week before we opened up again, after the dec hols, they
decided to do a stock take, and did not want to run on the old system
anymore.

As such, I asked them what their requirements are (and from my own
observations / functions performed), and suggested that I could throw it
together in Ms Access (As I did use a couple of years before as a Church
management database enviroment).

A basic search on the net mentioned that you can use the tables in the way I
have, with one for Receiving goods (GRV), and one for Booking Out
(Invoicing). And that you just have a query sum it as I am trying to do.

What I was planning to do is as follows:

1) Invoice comes from Supplier
2) Add New stock Items to Stock Table (For Descriptions, etc)
3) GRV gets done, that puts the Stock Item PK in the GRVITEMS table, which
has a FK in it for the Supplier Info (Another Table with Invoice No, etc)
4) If Item gets Returned to Supplier, then a Minus Quantity is put as a new
GRV

5) When an item gets booked out to a job, it adds record in Invoicing Table
(+ve quantity)
6) If not all of the item is used on the job, it adds another record in the
Invoicing Table (-ve quantity)
7) Once job is done, a Summing report is done for that specific job, and
Printed out, reflecting stock items and costs.

8) A stocktake report is printed that says how many of an item is supposed
to be there (+grv - invoiced)
9) If any shrinkage, then it can get invoiced out to a shrinkage 'Job'

Please let me know if my reasoning is sound, or any comments/ snags you may
have..

I have also split the database, with the tables seperate to the rest of the
d.b., and the Tables are on the Main Server z:\stockdata., and the rest of
the d.b. is on each machine (under my documents) and has tables linked to
z:\stockdata.

Ps I will still need to sort the sum problem out, even if I do use a
StockTakes Table (Should just be another Table.Field to add to the query)

Thanks
Peter

I have a more fundamental comment. It seems you are saying:
Onhand = Received - Sold
In most companies it is more complicated than that. For example it is
common to occasionally perform a Stock Take where each item is counted
as to current levels. The formula then becomes:
Onhand = Latest Stock Take + Received since then - Sold since then
As you bring up, returned merchandize needs to be accounted for, and
there probably are a few more.

Design before you implement!

-Tom.
Microsoft Access MVP

Hi Guys.
Me again.

I am busy with a stock control database.
I have several tables, the ones that I am having a problem with are as
follows:
tbl_Stock - Stock Descriptions, reorder levels, etc
tbl_Invoicing - When items are booked out of stock
tbl_GrvItems - The specific items that are received
tbl_GrvSuppliers - Lists what supplier, their Inv num, etc.

I Have a query that is supposed to give me the current stock levels
(Here is the SQL)
---------
SELECT tbl_Stock.Description, Sum(tbl_GrvItems.Qty) AS GRV,
Sum(tbl_Invoicing.Qty) AS Inv,
Sum(Nz([tbl_GrvItems].[Qty])-nz([tbl_Invoicing].[Qty])) AS Onhand
FROM tbl_GrvItems RIGHT JOIN (tbl_Invoicing RIGHT JOIN tbl_Stock ON
tbl_Invoicing.StockItem = tbl_Stock.StockID) ON tbl_GrvItems.StockItem =
tbl_Stock.StockID
GROUP BY tbl_Stock.Description;
---------

But now it seems I have a problem when I have Invoiced out say 2 of an
Item,
and then later 1 of that item. The query sums get totally wrong the
Inv:Qty
does its sums wrong and says 4 of that item have been booked out.

Please Help

I will send any more info you need

Thanks
Peter




--- news://freenews.netfront.net/ - complaints: (e-mail address removed) ---

--- news://freenews.netfront.net/ - complaints: (e-mail address removed) ---

 

[Peter Kolbe]

Anybody have a clue as to why this summing is messing around.

Or do I need to do 3 queries, one to group the Invoicing Stock Items, and
one to group the GRV items, then a third to sum the both?

Hi Guys.
Me again.

I am busy with a stock control database.
I have several tables, the ones that I am having a problem with are as
follows:
tbl_Stock - Stock Descriptions, reorder levels, etc
tbl_Invoicing - When items are booked out of stock
tbl_GrvItems - The specific items that are received
tbl_GrvSuppliers - Lists what supplier, their Inv num, etc.

I Have a query that is supposed to give me the current stock levels
(Here is the SQL)
---------
SELECT tbl_Stock.Description, Sum(tbl_GrvItems.Qty) AS GRV,
Sum(tbl_Invoicing.Qty) AS Inv,
Sum(Nz([tbl_GrvItems].[Qty])-nz([tbl_Invoicing].[Qty])) AS Onhand
FROM tbl_GrvItems RIGHT JOIN (tbl_Invoicing RIGHT JOIN tbl_Stock ON
tbl_Invoicing.StockItem = tbl_Stock.StockID) ON tbl_GrvItems.StockItem =
tbl_Stock.StockID
GROUP BY tbl_Stock.Description;
---------

But now it seems I have a problem when I have Invoiced out say 2 of an
Item, and then later 1 of that item. The query sums get totally wrong the
Inv:Qty does its sums wrong and says 4 of that item have been booked out.

Please Help

I will send any more info you need

Thanks
Peter




--- news://freenews.netfront.net/ - complaints: (e-mail address removed) ---

--- news://freenews.netfront.net/ - complaints: (e-mail address removed) ---

 

[kolbep]

I finally managed to do it.

A summing query for my GRV'd items
Another summing query for my Invoiced Items.
And another summing query with a sum in it to join the two, with a
relationship to my stock table to get the descriptions.


Now my sums work out.

P

Hi Guys.
Me again.

I am busy with a stock control database.
I have several tables, the ones that I am having a problem with are as
follows:
tbl_Stock - Stock Descriptions, reorder levels, etc
tbl_Invoicing - When items are booked out of stock
tbl_GrvItems - The specific items that are received
tbl_GrvSuppliers - Lists what supplier, their Inv num, etc.

I Have a query that is supposed to give me the current stock levels
(Here is the SQL)
---------
SELECT tbl_Stock.Description, Sum(tbl_GrvItems.Qty) AS GRV,
Sum(tbl_Invoicing.Qty) AS Inv,
Sum(Nz([tbl_GrvItems].[Qty])-nz([tbl_Invoicing].[Qty])) AS Onhand
FROM tbl_GrvItems RIGHT JOIN (tbl_Invoicing RIGHT JOIN tbl_Stock ON
tbl_Invoicing.StockItem = tbl_Stock.StockID) ON tbl_GrvItems.StockItem =
tbl_Stock.StockID
GROUP BY tbl_Stock.Description;
---------

But now it seems I have a problem when I have Invoiced out say 2 of an
Item, and then later 1 of that item. The query sums get totally wrong the
Inv:Qty does its sums wrong and says 4 of that item have been booked out.

Please Help

I will send any more info you need

Thanks
Peter




--- news://freenews.netfront.net/ - complaints: (e-mail address removed) ---

--- news://freenews.netfront.net/ - complaints: (e-mail address removed) ---