Grouping number ranges

[Ruth]

I have a database of products that we sell in a shop. Each product has a
unique four digit code known as a PLU. The PLU is assigned according to what
sort of product it is. For example Poultry Feed is assigned a code between
5000 and 5079, Game Feed between 5080 and 5099 and so on.

What I want to do is pull a report off to use as a price list to hand out to
all our salesmen. So, for example all 5000 codes are for Merchant Feed - so
I want to pull one price list off for this category. However within the
category I would like to have a list of products listed under their group
heading if possible e.g.

PRICE LIST FOR MERCHANT FEED

POULTRY FEED
5000 Layers Pellets £5.00
5001 Layers Meal £5.50
etc.

GAME FEED
5080 Pheasant Grower £7.00
5081 Pheasant Starter £7.50
etc.

I've worked out how to group by the first 1 or 2 digits in the PLU field,
but because my groups don't necessarily change every 100 or every 50 I cannot
see how to group by my own custom number grouping.

Has anyone any ideas?

 

[Dale Fye]

Ruth,

Create a new table (tbl_PLU_Groupings) with fields
Group Category Group_Name Group_Start Group_End
Merchant Feed Poultry Feed 5000 5079
Merchant Feed Game Feed 5080 5099
....

Now you can create a query that looks something like:

SELECT Group_Category, Group_Name, Product_ID, Product_Price
FROM tbl_Products P, tbl_PLU_Groups G
WHERE P.Product_ID >= G.Group_Start
AND P.Product_ID <= G.Group_End
ORDER BY Group_Category, Group_Name, Product_ID

HTH
Dale

 

[Ruth]

Right, I'm a bit confused.

I have created my table "Grouping Codes".
My field names within this table are "Group Name" e.g. Merchanted Products,
"Group Category" e.g. Poultry Feed, "PLU Start" and "PLU End".

I want to extract data from this table and combine it with data from a query
called "Calculated Fields Query", the fields I require from this query are
"Description 1", "Pack Size", "PLU Number", "Sell Ex VAT" and "VAT Rate".

I didn't really understand the code you put in your reply so I went into New
Query in Design View, selected the "Calculated Fields Query" and the
"Grouping Codes Table", and put all the fields in from "Grouping Codes" table
and the fields as listed above from "Calculated Fields Query" and I'm now
stuck. If anyone can help me I'd be very grateful...........