how would I modify total

[bmoyer123]

I have two tables. One called Inventory and the other called InventoryTotal.
Inventory list each individual unit in stock, InventoryTotal lists total number of units. How would I have database adjust total number whenever unit is shipped? Units are shipped using a form.

[HansV]

Can't you use a Totals query to list the totals instead of storing them in a separate table? A query will always return up-to-date results whichever way the Inventory table is edited.

[bmoyer123]

I was able to get the desired results by using a totals query (count), but I need to exclude some data found in the column (BAGID). This column has text data A0, B0, C0...etc. It also includes QC1, QC2, QC3...etc and QV1, QV2, QV3...etc. I need to exclude all QCx and QVx samples and track these in a separate column. I was able to exlude the data by using criteria <>"QC1" And <>"QV1", but would need to list each type of sample individually QC1, QC2 etc which I can't seem to do without listing each sample individually. Also, whe I add another BAGID column to count only QC and QV samples, I get no results. I tried calling each column something different but still cant get it to work. Any ideas. Tried to send you a sample database, but don't know how to attach it. your website keeps closing on me each time I try.

[HansV]

You cannot attach .mdb or .accdb files directly, you have to zip the database and attach the zip file to a reply.

[bmoyer123]

I've attached an abbreviated database. tblBOHAll has all data
queBOH has the query to count excluding QC1 and QV1. queQC_BOH had query for only QC and QV data. This was the only way I could get the data needed, but when I thry to combine them in another query I get no results.

[HansV]

You don't need to use anything in the Criteria line. You can use the following definitions in a single query. Set the Total line for these to Expression:

NonQCQV: Abs(Sum([BagID] Not Like "QC*" And [BagID] Not Like "QV*"))

and

QCQV: Abs(Sum([BagID] Like "QC*" Or [BagID] Like "QV*"))

See the attached version.

Database1x.zip

[bmoyer123]

THANK YOU!

[bmoyer123]

Ok, I needed to expand the issue a bit more it seems. I needed to add storage location to the table and query. Problem is now I get two entries. Is it possible to have a column for NOTQCQV items and one for QCQV items instead of two rows for each?

[HansV]

In your new query, you group by StorageUnit, SectionID and RackID in addition to COLL#. So each unique combination of these will get its own row.

Can you explain what result you would want? Perhaps a drawing, or a mockup in Excel?

[bmoyer123]

I would like the following columns:
COLL# NonQCQV QCQV Location:(Storage Unit + Section + Rack) QCLocation:(Storage Unit + Section + Rack)

I previously combined these values using: Location: Trim([StorageUnitID] & ", " & [Section ID] & " " & [Rack#]).

[HansV]

Thanks. We do need separate queries for this, and combine them in a 'final' query.
I created 4 queries:

1) queAllColl: returns unique Coll#, just in case one of the Coll# is not represented in the QC/QV query or in the non-QC/QV query.
2) queQCQV: returns count and location for BagIDs beginning with QC or QV.
3) queNonQCQV: returns count and location for BagIDs not beginning with QC or QV.
4) queCombined: combines the above queries, with left joins from queAllColl to queQCQV and queNonQCQV.

Database11.zip

[bmoyer123]

Thank you!