Counting Number of values for a particular field

[snedie]

Have a strange problem. I need to count the number of entries for a particular item located in a table. To keep it simple, we have a database used to process orders for Turkeys, this database has a field for each order called "Long legged Weight". In order to sort the turkeys in the shed, we need to know how many of each weight there are.

So in essence I need a table which looks like the following (Ignore the stops):

Order Number / 10-12 / 11-13 / 12-14 / 13-15 / (etc)
......1 ................1
......2 ..........................1
......3 .....................................1


......4 ..........................1

Total ..................1 ......2 .........1


This is the table I am working from.

[June7]

Need a field in data source that categorizes the weight by range. Either have a table that defines this and join that table to the 'Long Legged Weight' field or calculate the categories with expression in query. How many weight categories? I assume all weight values are integers (no decimal part). You show weight categories overlapping. Do you really want to count turkey in more than one weight group?

Then do a crosstab query.

[rpeare]

You'd have to do a formula in your query (several really) but your weights overlap how do you account for that you could theoretically have a single turkey that weighs 11 pounds and it would fall into two of your columns.

The basic idea is that you'd have something like:

SELECT [Order No], iif([long legged weight] between 10 and 11, 1,0) AS [LLWt_10-11] FROM [Orders 2012]

[snedie]

Gave the cross-tab query a try in multiple iterations whilst I was still at work and couldn't get it to display the information how I wanted. I found the easiest method was to export the data to Microsoft work*, then manually copy paste the [Order No][LL Weight] data into Excel and then run the following IF statement to display a 1 if the LL weight was matched: =if(B2=C1, 1, "") using the LL Weight values as column headers to match against the values in the B column.

Does the trick for what I want. *The reason for exporting to Word first is that when I export directly into an excel file the [Long Legged Weight] data goes from 11-13 to a random (or appears to be random) number such as 18. Exporting to word keep sthis info intact. Any info on this would be appreciated.

Thanks

[rpeare]

There's really no need to do the export step if you do the query I showed.

[snedie]

There's really no need to do the export step if you do the query I showed.

The reason there is overlap on the LL Weights is due to the system we use for selecting birds for each order. We like to give the person sorting the birds a range of weights acceptable to meet the weight asked for by the customer, which in itself is a range (4.5-4.99Kg for example). This does indeed allow one order to fall into two different LL Weight ranges, however the minimum weight it can be is specific to each weight range; As an example a 5.5Kg bird at the selling point would require a 6Kg (13lb) bird minimum before processing. This weight can be variable and we have a margin of 0.49Kg (1lb), so we select a weight range that suits said order. In this example I would select the range 13-15, thus meeting the minimum required weight but also allowing for more options when allocating birds.

When I use the excel sheet I match the phrase "13-15" (these are column headings) against the data in column B (the LL Weight data from the access database). See the following picture:



If a query can perform this task for me that would be fantastic.

[rpeare]

Ok I thought you were actually entering a weight, not a weight range in your long legged weight so June was absolutely right with his suggestion of a crosstab query here it is:

TRANSFORM Count(tblOrders.[Long Legged Weight]) AS [CountOfLong Legged Weight]
SELECT tblOrders.OrderNo
FROM tblOrders
GROUP BY tblOrders.OrderNo
PIVOT tblOrders.[Long Legged Weight];

This assumes your table is called tblOrders and your have a field called [Long Legged Weight] (from the table view screen shot you had)

The summary at the end is possible in a query but you're much better off creating a report based on your crosstab (I'm only suggesting this because you have a very finite list of possible values so there is not a lot of harm in creating a report out of it)

[June7]

A report on the crosstab will be stable only if every weight group is always included in the report dataset. If any group does not show because of lack of data then textbox bound to that fieldname will show #ERROR. Stabilizing report requires a datasource in the query that has every possible weight group. This can be a table of weight groups then join this table to the orders table in the crosstab designer with jointype 'Include all records from tblGroups ...'

[snedie]

Ok I thought you were actually entering a weight, not a weight range in your long legged weight so June was absolutely right with his suggestion of a crosstab query here it is:

TRANSFORM Count(tblOrders.[Long Legged Weight]) AS [CountOfLong Legged Weight]
SELECT tblOrders.OrderNo
FROM tblOrders
GROUP BY tblOrders.OrderNo
PIVOT tblOrders.[Long Legged Weight];

Thanks for the code, little tweaking of the table names got it to work. One slight problem is that the query is displaying the primary key cell data as the column names (1, 2, 3 etc), which although can translated to the corresponding LL Weight value (1 is 9-11 for example) it is a pain and not exactly human readable at glance, not to mention impractical.

Any way I can change this? Just for clarification some more pictures:

How the Query currently looks:


The products table:

[rpeare]

So you want the PRODUCT DESCRIPTION to be your column header in your crosstab? Because the query I gave you uses the label (10-12 for instance) as the column header in the crosstab query.

[snedie]

So you want the PRODUCT DESCRIPTION to be your column header in your crosstab? Because the query I gave you uses the label (10-12 for instance) as the column header in the crosstab query.

Yes. For it to be of any use to staff members it will need to have 9-11, 10-12, 11-13 etc as the column headings, rather than 1, 2, 3, 4 etc.

[rpeare]

Ok I'm getting lost in what I perceive as contradictory posts so let's reset a little bit.

The value for your field [Long Legged Weight] what does that correspond to?

The primary key (PK) of your PRODUCTS table?

The actual desired weight of the bird?

If it refers to the PRODUCT PK you can link your ORDERS table to your PRODUCTS table and use the PRODUCT DESCRIPTION as the COLUMN HEADER for your crosstab.

[snedie]

The Long Legged Weight specifies the weight a bird should weight prior to any processing. This is derived from a simple conversion of the requested weight a customer asks for in Kg into lb, then using our scale chart converted into a "Long Legged" weight.

The primary key of the Products table is just a number to be used simply as a UI, it has no relevant data in it.

The crosstab query is exactly what I needed and is functioning just fine, it's just the column headings aren't human readable without considerable effort.

[rpeare]

Alright, so... when you do your data entry you put in the field [REQUESTED WEIGHT] which I assume corresponds to the field [LONG LEG WEIGHT] in your PRODUCTS table? Then you are running some process in the background, or doing it via data entry where the [LONG LEGGED WEIGHT] in your ORDERS table is being stored as an actual numerical value.

If so your COLUMN HEADER field of your crosstab can be the REQUESTED WEIGHT field value.

Though if I'm right and you're storing the TEXT VALUE (i.e. '10 - 12' in the REQUESTED WEIGHT field you should actually be storing the PRODUCT ID in that field. It will make your queries etc a lot easier to handle particularly if your headers change... ever.

[snedie]

Alright, so... when you do your data entry you put in the field [REQUESTED WEIGHT] which I assume corresponds to the field [LONG LEG WEIGHT] in your PRODUCTS table? Then you are running some process in the background, or doing it via data entry where the [LONG LEGGED WEIGHT] in your ORDERS table is being stored as an actual numerical value.

If so your COLUMN HEADER field of your crosstab can be the REQUESTED WEIGHT field value.

Though if I'm right and you're storing the TEXT VALUE (i.e. '10 - 12' in the REQUESTED WEIGHT field you should actually be storing the PRODUCT ID in that field. It will make your queries etc a lot easier to handle particularly if your headers change... ever.

In the Orders table the field [Long Legged Weight] uses a lookup to find the values in the field [Long Leg Weights] in the table Products. This gives me a simple drop down box to select the LL weights when entering orders to save on time. I'm unsure as to what method is used to store that data in the orders table.

The [Requested Weight] field is the weight a customer requests in Kg, again this field performs a lookup to the field [Product Description] in the products table to provide me with a drop down box for selecting the weights. See below (note I use a form to create orders not directly in the table):