Query to Develope Price Book

[Mark L]

I am using Access2007 to construct a price book for my business. Vegetable Seed



I have a Table (Table1: Vegetable Seed) which includes the "Cost" of different "Varities" (810 different varieties) of vegetable seed. The variety (ex. Bronco, Cougar, etc.) is assigned to a "Vegetable Group", example Beans, Cucumbers, etc). Each Variety is also assigned to a "Pricing Group" (there are 48 of these) example Beans1 or Beans2 or Squash1. So for example: Bronco(the variety) is assigned to the Beans1 (pricing group) and is in the Category Beans.

I have a second table (Table2: Pricing Groups) with "Pricing Groups" for example Bean1, Beans2, Squash1. Each pricing group has 5 columns that define different quanities for seed which is priced differently based on volume purchased. example: 1lb, 5lb, 10lb, 50lb, 100lb. (more volume you buy the cheaper the price). I am trying to caluclate pricing for each variety under a volumn column. I need to calculate a price for all varieties that fall within a specific "Pricing Group" but will call under a given pricing column.

I have a Query which pulls these table together using "Pricing Group" as the common.

I need to calculate a price (example: Price1:[Cost]/0.90 or Price2:[Cost]/.85, etc) for each volume purchased column but specific for each unique variety (Bronco vs Cougar). The profit caputured by the sale of Bronco within a volume column may be more or less than the sale of Cougar within a volumne column.

While I can use a calculated field in the query to get a price for the entire column; I do not know how to segment the calculation for each variety; I only can make a common calculation for the column.

I need to be able to price each vareity under a column.

Any help?

The report would look like this:
1lb 5lb 10lb 50lb 100lb
Bronco Price Price Price Price Price
Cougar Price Price Price Price Price

the pricing under Bronco would be unique to that vareity while the pricing for Cougar would be unique to it under the volume columns

[jo15765]

When you are setting up the query, import all of the tables that have potential data in them that you are needing to use, and then just set up the query like this:

in the design view of the query, click the Sigma button to add the word "Total." In the field "Total" add the word "Sum" and run the query and it will total all of the data that has been input into the fields you are wanting to sum.