How do I Count number of records based on Criteria in this scenario? (1 Viewer)

ChoiceTom

New member
Local time
Today, 16:02
Joined
Aug 6, 2020
Messages
9
Goal: To have a Button on a form that produces a summary list of Gage Types and how many of each is in the Item table.

The button part is a non-issue, assigning either a macro or query is understood.

Example: Surface Plates 27, Gage Block Sets 9, or even Micrometers 0. That there would be NO records for a given type is meaningful also, thus why I need to involve the GageType table instead of just a count of same types in Item.

Tables:

GageType Table
- ID (auto number), GageType (text)
This table supports a Combo Box for data entry consistency. e.g. "Surface Plate", "Height Gage", etc.

Item Table - ID (auto number), GageType (number), GageID (text), Mfg (text), Comments (text), Link (scanned PDF)
This is the primary table with an item record to which any number of scanned calibration documents will be linked.

GageType in the Item table is setup as a Combo Box on a Form to display the valid list of selections. What is actually stored in the Item table however is the auto-number index from GageType, not the understandable text description.

I want to create a Query that will appear when a button is pushed that will:

List the entries in GageType with a Count next to each showing how many of that GageType exist in the Item Table.

I'm sure this is basic, but I'm having a little trouble with the syntax / method to use, displaying the GageType text field, not the index auto-number ID.

Quick guidance on type of Count or technique to focus on would be appreciated.

Regards,

Tom
 

Minty

AWF VIP
Local time
Today, 21:02
Joined
Jul 26, 2013
Messages
8,020
Go to the query editor add both your item table and GageType tables.

Click on the join between the two tables (if there is one) and right click it to display all records from GageType and only the ones in Item Table that match.

Now select the Gage Type Text GageType and the GageType from the Item table.
Click on the Totals (Epsilon) in the top of the query designer.

In the Grouping option select count under the GageType from the Item table.

Run the query.
 

ChoiceTom

New member
Local time
Today, 16:02
Joined
Aug 6, 2020
Messages
9
:D Well, I'm going to feel silly if it's that simple. I appreciate the assist as I re-familiarize myself with the Access interface. I'll give this a go today, thank you.
 

Minty

AWF VIP
Local time
Today, 21:02
Joined
Jul 26, 2013
Messages
8,020
The SQL would end up being something like

SELECT GageType.GageType, Count(ItemTable.GageType)
FROM GageType LEFT JOIN ItemTable On GageType.ID = ItemTable.GageType
GROUP BY GageType.GageType

Personally I would definitely rename your fields and tables to more meaningful things. Something like

tblGageType - Fields GageTypeID, GageText

tblItems - Fields ItemID, GageTypeID_FK, GageSerialNo , Manufactuer (text), ItemComments text), PdfLink (scanned PDF)

Then your query wouldn't be so ambiguous to read.
You could also add tblManufactures to make that a drop-down and avoid typo's

Have a look at naming conventions - easier to do now than retro-fit it later.
 

arnelgp

error reading drive A:
Local time
, 05:02
Joined
May 7, 2009
Messages
11,503
Select A.[gagetype], B.[gagetype] as Type, Count(A.id) As CountOfGage from [item table] A
Left join [gage table] B on A.[gageType]=B.[ID]
group by a.[gagetype], b.[gagetype];
 

Users who are viewing this thread

Top Bottom