!!! Checking some assumptions
From your database schema, it seems that:
- There could be multiple
Sales
records for a given BatchNumber/BarNo
(for instance, I can imagine that multiple customers may have bought subsections of the same bar).
- There could be multiple
Reservation
records for a given BatchNumber/BarNo
(for instance, multiple sections of the same bar could be 'reserved')
To check if you do indeed have multiple records in those tables, try something like:
SELECT CountOfDuplicates
FROM (SELECT COUNT(*) AS CountOfDuplicates
FROM Sales
GROUP BY BatchNumber & "," & BarNo)
WHERE CountOfDuplicates > 1
If the query returns some records, then there are duplicates and it's probably why your query is returning incorrect values.
Starting from scratch
Now, the trick to your make your query work is to really think about what is the main data you want to show, and start from that:
- You basically want a list of all bars in the stock.
Some of these bars may have been sold, or they may be reserved, but if they are not, you should show the Quantity available in Stock. Your current query would never show you that.
- For each bar in stock, you want to list the quantity sold and the quantity reserved, and combined them to find out the quantity remaining available.
So it's clear, your central data is the list of bars in stock.
Rather than try to pull everything into a single large query straight away, it's best to create simple queries for each of those goals and make sure we get the proper data in each case.
Just the Bars
From what you explain, each individual bar is recorded in the BarStock
table.
As I said in my comment, from what I understand, all bars that are delivered have a single record in the BarStock
table, without duplicates. So your main list against which your inventory should be measured is the BarStock
table:
SELECT BatchNumber,
BarNo,
OrgLength
FROM BarStock
Just the Sales
Again, this should be pretty straightforward: we just need to find out how much total length was sold for each BatchNumber/BarNo
pair:
SELECT BatchNumber,
BarNo,
Sum(QtySold) AS SumAvQtySold
FROM Sales
GROUP BY BatchNumber, BarNo
Just the Reservations
Same as for Sales:
SELECT BatchNumber,
BarNo,
SUM(QtyReserved) AS Reserved
FROM Reservation
GROUP BY BatchNumber, BarNo
Original Stock against Sales
Now, we should be able to combine the first 2 queries into one. I'm not trying to optimise, just to make the data work together:
SELECT BarStock.BatchNumber,
BarStock.BarNo,
BarStock.OrgLength,
S.SumAvQtySold,
(BarStock.OrgLength - Nz(S.SumAvQtySold)) AS OnStock
FROM BarStock
LEFT JOIN (SELECT BatchNumber,
BarNo,
Sum(QtySold) AS SumAvQtySold
FROM Sales
GROUP BY BatchNumber, BarNo) AS S
ON (BarStock.BatchNumber = S.BatchNumber) AND (BarStock.BarNo = S.BarNo)
We do a LEFT JOIN
because there might be bars in stock that have not yet been sold.
If we did an INNER JOIN
, we wold have missed these in the final report, leading us to believe that these bars were never there in the first place.
All together
We can now wrap the whole query in another LEFT JOIN
against the reserved bars to get our final result:
SELECT BS.BatchNumber,
BS.BarNo,
BS.OrgLength,
BS.SumAvQtySold,
BS.OnStock,
R.Reserved,
(OnStock - Nz(Reserved)) AS Available
FROM (SELECT BarStock.BatchNumber,
BarStock.BarNo,
BarStock.OrgLength,
S.SumAvQtySold,
(BarStock.OrgLength - Nz(S.SumAvQtySold)) AS OnStock
FROM BarStock
LEFT JOIN (SELECT BatchNumber,
BarNo,
SUM(QtySold) AS SumAvQtySold
FROM Sales
GROUP BY BatchNumber,
BarNo) AS S
ON (BarStock.BatchNumber = S.BatchNumber) AND (BarStock.BarNo = S.BarNo)) AS BS
LEFT JOIN (SELECT BatchNumber,
BarNo,
SUM(QtyReserved) AS Reserved
FROM Reservation
GROUP BY BatchNumber,
BarNo) AS R
ON (BS.BatchNumber = R.BatchNumber) AND (BS.BarNo = R.BarNo)
Note the use of Nz()
for items that are on the right side of the join: if there is no Sales
or Reservation
data for a given BatchNumber/BarNo
pair, the values for SumAvQtySold
and Reserved
will be Null
and will render OnStock
and Available
null as well, regardless of the actual quantity in stock, which would not be the result we expect.
Using the Query designer in Access, you would have had to create the 3 queries separately and then combine them.
Note though that the Query Designed isn't very good at dealing with multiple LEFT
and RIGHT
joins, so I don't think you could have written the whole thing in one go.
Some comments
I believe you should read the information that @Remou gave you in his comments.
To me, there are some unfortunate design choices for this database: getting basic stock data should be as easy as s simple SUM()
on the column that hold inventory records.
Usually, a simple way to track inventory is to keep track of each stock transaction:
- Incoming stock records have a + Quantity
- Outgoing stock records have a - Quantity
- The record should also keep track of the part/item/bar reference (or ID), the date and time of the transaction, and -if you want to manage multiple warehouses- which warehouse ID is involved.
So if you need to know the complete stock at hand for all items, all you need to do is something like:
SELECT BarID,
Sum(Quantity)
FROM StockTransaction
GROUP BY BarID
In your case, while BatchNumber/BarNo
is your natural key, keeping them in a separate Bar
table would have some advantages:
- You can use
Bar.ID
to get back the Bar.BatchNumber
and Bar.BarNo
anywhere you need it.
- You can use
BarID
as a foreign key in your BarStock
, Sales
and Reservation
tables. It makes joins easier without having to mess with the complexities of compound keys.
There are things that Access allows that are not really good practice, such as spaces in table names and fields, which end up making things less readable (at least because you need to keep them between []
), less consistent with VBA variable names that represent these fields, and incompatible with other database that don't accept anything other than alphanumerical characters for table and field names (should you wish to up-size later or interface your database with other apps).
Also, help yourself by sticking to a single naming convention, and keep it consistent:
- Do not mix upper and lower case inconsistently: either use CamelCase, or lower case or UPPER case for everything, but always keep to that rule.
- Name tables in the singular -or the plural-, but stay consistent. I prefer to use the singular, like table
Part
instead of Parts
, but it's just a convention (that has its own reasons).
- Spell correctly: it's
Received
not Recieved
. That mistake alone may cost you when debugging why some query or VBA code doesn't work, just because someone made a typo.
- Each table should/must have an
ID
column. Usually, this will be an auto-increment that guarantees uniqueness of each record in the table. If you keep that convention, then foreign keys become easy to guess and to read and you never have to worry about some business requirement changing the fact that you could suddenly find yourself with 2 identical BatchNumbers
, for some reason you can't fathom right now.
There are lots of debates about database design, but there are certain 'rules' that everyone agrees with, so my recommendation should be to strive for:
- Simplicity: make sure that each table records one kind of data, and does not contain redundant data from other tables (normalisation).
- Consistency: naming conventions are important. Whatever you choose, stick to it throughout your project.
- Clarity: make sure that you-in-3-years and other people can easily read the table names and fields and understand what they are without having to read a 300 page specification. It's not always possible to be that clear, but it's something to strive for.
Best Answer
I know this is an old thread, but just in case someone is still looking for an answer, this worked for me. I converted the Projects Web Database.
The process is as follows:
Create a blank "client" accdb database.
Import all web tables, client forms, client reports from the template web database into the blank client accdb database.
Close the client database and open the template web database.
(Now, you must individually create a client object from each web object. This can be a bit tedious.)
Highlight the web object (a web form, for example) that you want to create a corresponding client object. Click the File Tab, then Save & Publish, then Save Object As, and finally click the Save As Client Object button. Give the Client Object a different name than the web object (add Client to the end of the name, for example).
You will need to repeat these steps for each web query, web form, web report, and web macro in the template web database.
Once every web object has a corresponding client object in the template web database, open up the "client" database and import all client objects that you just created. Rename the objects to their original names (remove the word "client" if you took my suggestion above).
Now you should have a working template database that you can change to you liking.