Counting Number of values for a particular field

[rpeare]

Ok, can you provide a sample of your database, it's way easier than going back and forth with this.

Just make a copy of your database, change or remove any sensitive information, put in some junk data for the purposes of testing, then zip it up and upload it to this site.



I thought you were doing data entry through a form when what you're actually doing is using a lookup field on your table (which I stay away from at all costs) and I likely will not be able figure it out without look at your structure first hand.

[snedie]

Ok, can you provide a sample of your database, it's way easier than going back and forth with this.

Just make a copy of your database, change or remove any sensitive information, put in some junk data for the purposes of testing, then zip it up and upload it to this site.

I thought you were doing data entry through a form when what you're actually doing is using a lookup field on your table (which I stay away from at all costs) and I likely will not be able figure it out without look at your structure first hand.

See attached.

[rpeare]

Ok, I have a question, look at order number 1 in your test data.

It has a requested weight of 8.5 - 8.99kg, but a long legged weight of 18 - 21.

If you look on the products table an 8.5 - 8.99 kg bird has to have a long legged weight of 26 - 28.

But 8.5kg converts to about 18.75 pounds and 8.99kg converts to about 19.8 pounds so I'm a little confused as to the relationships you're trying to build.

If the LONG LEG WEIGHTS field of your PRODUCTS table is unrelated to the PRODUCT DESCRIPTION field why are you maintaining it in the same table? It actually looks to me like you could do what you want if you just store the REQUESTED WEIGHT and store the PRODUCT PK (looks like Field1 though that should be an autonumber rather than a plain number so you don't duplicate it) If I'm correct in my guess the LONG LEG WEIGHTS field should be 26-28 for your item numbered 26 in your PRODUCTS table (11-11.49KG) it looks like if you shifted all your long leg weights down five rows you'd actually be on target (or close enough) so that instead of entering a requested weight AND a long leg weight you'd just enter the requested weight and you could figure the long leg weight.

I'm also a little confused in that you're going by .5 KG increments on the weight of the final product which translates to a little over a pound but the range of your long legged weights is a 2 pound spread and I thought you said you basically add half a pound to the requested weight to get the long leg weight.

Is there a direct relationship between the REQUESTED WEIGHT and the LONG LEG WEIGHT, so for instance if someone orders a 4.5 KG bird the long leg weight is ALWAYS going to be 11 - 13 pounds? If that relationship is fixed you can do as I've suggested and only enter ONE weight field.

If there is no relationship between the REQUESTED WEIGHT and LONG LEG WEIGHT, put your long leg weights in a different table with a primary key and store the Primary key field in your ORDERS table NOT the text description. Storing text descriptions on tables you are linking through that text value is a very, very, very bad idea, for instance if someone unintentionally modifies a description on either table it will force a mismatch and you will start getting data that clutters your database but you will not be able to tell what belongs to what because the text value has changed. If you store the PK (autonumber is a good primary key generator) you can hide all instances of that primary key from the unenlightened user so they can't possibly mess up your tables.

[snedie]

Ok, I have a question, look at order number 1 in your test data.  

It has a requested weight of 8.5 - 8.99kg, but a long legged weight of 18 - 21.

If you look on the products table an 8.5 - 8.99 kg bird has to have a long legged weight of 26 - 28.

But 8.5kg converts to about 18.75 pounds and 8.99kg converts to about 19.8 pounds so I'm a little confused as to the relationships you're trying to build.

The data in each column in the Products table bares no direct relation to any other data in another column, I simply placed all the required "descriptions" (prices, weights (lb & Kg), types of bird, etc) so that I could easily perform a lookup on my orders table to speed up data entry. So in a nut shell, the 8.5Kg - 8.49Kg bird weight has no correlation to the 26 - 28 lb weight in the same row.

If the LONG LEG WEIGHTS field of your PRODUCTS table is unrelated to the PRODUCT DESCRIPTION field why are you maintaining it in the same table?  It actually looks to me like you could do what you want if you just store the REQUESTED WEIGHT and store the PRODUCT PK (looks like Field1 though that should be an autonumber rather than a plain number so you don't duplicate it) If I'm correct in my guess the LONG LEG WEIGHTS field should be 26-28 for your item numbered 26 in your PRODUCTS table (11-11.49KG) it looks like if you shifted all your long leg weights down five rows you'd actually be on target (or close enough) so that instead of entering a requested weight AND a long leg weight you'd just enter the requested weight and you could figure the long leg weight.

As above.

I'm also a little confused in that you're going by .5 KG increments on the weight of the final product which translates to a little over a pound but the range of your long legged weights is a 2 pound spread and I thought you said you basically add half a pound to the requested weight to get the long leg weight.

It's to account for the weight of giblets and bone structure of the birds. The larger the bird is the more weight these have, by maintaining a spread of weights which will fall into the requested weight range we are better able to choose birds from what we have available. Which is another reason to have a spread, we cannot guarantee that we are going to have enough birds of a particular weight to fulfill all the orders, so we find the closest match and use that. People never complain when they get a bigger bird for the same price.

Is there a direct relationship between the REQUESTED WEIGHT and the LONG LEG WEIGHT, so for instance if someone orders a 4.5 KG bird the long leg weight is ALWAYS going to be 11 - 13 pounds?  If that relationship is fixed you can do as I've suggested and only enter ONE weight field.

As above, nope.

If there is no relationship between the REQUESTED WEIGHT and LONG LEG WEIGHT, put your long leg weights in a different table with a primary key and store the Primary key field in your ORDERS table NOT the text description.  Storing text descriptions on tables you are linking through that text value is a very, very, very bad idea, for instance if someone unintentionally modifies a description on either table it will force a mismatch and you will start getting data that clutters your database but you will not be able to tell what belongs to what because the text value has changed.  If you store the PK (autonumber is a good primary key generator) you can hide all instances of that primary key from the unenlightened user so they can't possibly mess up your tables.

Don't entirely follow the logic on this one, as you've no doubt guesses I'm pretty new to Access. If you could do a basic mockup of what you mean by having the LL weights in their own table that would be great, especially if it got that query to display the LL Weights as column headings instead of 1, 2, 3...

Quick learner though so don't worry about throwing code and the such my way.

[rpeare]

Ok, don't try to put all that stuff in one table then.

Have one table for the Requested weight (and anything directly related to a requested weight)

Have one table for your long leg weight (and anything directly related to the long leg weight)

Trying to put those in one table is a mistake.

Here's a copy of your database where I've put the LLW labels in their own table, your main orders table looks the same but the functionality is still there.

Then I created 3 queries, one that totals the orders one that shows the detail, then a union query that puts them together into a 'final' format if you want to go that route.

Database for 2012 (1).zip

[snedie]

Ok, don't try to put all that stuff in one table then.

Have one table for the Requested weight (and anything directly related to a requested weight)

Have one table for your long leg weight (and anything directly related to the long leg weight)

Trying to put those in one table is a mistake.

Here's a copy of your database where I've put the LLW labels in their own table, your main orders table looks the same but the functionality is still there.

Then I created 3 queries, one that totals the orders one that shows the detail, then a union query that puts them together into a 'final' format if you want to go that route.

Database for 2012 (1).zip

This is it, exactly what I needed. I really cannot thank you enough for this, it's going to shorten my office hours massively. The union query seems to throw up an error though, "The Office Databse engine does not recognize '[Query1].[10-12]' as a valid field name or expression. Not sure if that's normal or not?

[rpeare]

use this SQL statement for the union query:

SELECT * FROM Qry_OrderDetail UNION ALL SELECT * FROM Qry_OrderSummary

I'm not sure why it's throwing that error, just recreate the query using this SQL statement and save it with whatever query name you want.