Need help with query please

sumdumgai

Registered User.
Local time
Today, 18:16
Joined
Jul 19, 2007
Messages
453
Need help designing query for following problem. Three tables: T1, T2 and T3. T1 has field F1. T2 has fields F1, F2 and F3. T3 has fields F1 and F4. F1 would be a part number that exists in all three tables. F2 is a color. F3 is the id of the group to which F1 belongs. F4 is the number of units of the part sold. I need a query that will produce the following table, giving me the total number of units sold for every group by color (under F5), and the total units sold by group (under F6).

Example: (please excuse periods which I needed to align)
T1........T2....T2....T2.........T3.....T3
F1........F1.....F2....F3.........F1.....F4.............F5.......F6
1..........1.......R......A...........1.......4..............10.....25
2..........2.......R......A...........2.......6..............10......25
3..........3.......Y......A...........3.......7..............15......25
4..........4.......Y......A...........4.......8..............15......25
5..........5.......R......B...........5.......2.............2........ 2
For part number ‘1’, the total number of units sold is ‘4’. Part ‘1’ belongs to group ‘A’ and has color 'R'. The total number of units sold for group ‘A’, color 'R' is 10. I want the query to produce 10 in new field F5 for parts '1' and '2', 15 in field F5 for parts '3' and '4', and 25 in field F7 for parts '1', '2', '3', and '4', etc.

It's complicated but I did my best to describe the problem. Thanks.
 
Forgot to mention that T1 is short list of parts of interest. T2 is a master list of all parts and has tens of thousands of records. T3 has thousands of records; i.e., all parts that have sold.
 
You will need two queries one to group your F2 and F3 and total the f4 from T3 the other to group your F3 and total the f4 from T3.

I have attached a sample database wit the two queries
 

Attachments

Here's a little twist to my problem. And thanks again for your help so far. Let's say that for a given set of parts, instead of subtotalling by color, I need to subtotal the units sold by grouping the parts according to cost (a new field - call it F7 in T2). For example, the number of units sold of parts whose cost are between $10 and $20 should be subtotalled. Or, more complex, the number of units of parts sold whose costs are within $3 of each other should be subtotalled. Any help would be appreciated.
 
Here is an updated version qry_group_cost_total will give you the total units sold between the minimum and maximum values entered in the prompts.

Or, more complex, the number of units of parts sold whose costs are within $3 of each other should be subtotalled. Any help would be appreciated
.

This is a broad statement could you please be more specific?
 

Attachments

Sure, and thanks again. Let's say, in my example above where F4 is units sold for each part, that parts '1' has a cost of $1.50, '2' costs $10.00, '3' costs $2.25, '4' costs $12.00, and '5' costs $2.75. The number of units of each part, by cost grouping, would be:
Part '1' = (4 + 7 + 2) or 13
Part '2' = (6 + 8) or 14
Part '3' = (4 + 7 + 2) or 13
Part '4' = (6 + 8) or 14
Part '5' = (4 + 7 + 2) or 13
That's because the cost of parts '1', '3' and '5' are within $3.00 of each other, and parts '2' and '4' are also within $3.00 of each other.
 
Last edited:
Still looking for help with this. Would it help if the costs were sorted? Thanks.
 
It took me a while to work out what you want.

  • You have these tables structured as above with an extra field which I will call F8 to store the cost of the part.
  • You have a query or table that displays the number of parts sold during a certain period as shown in T4 above how T4 is populated is immaterial at this stage.
  • For each part number sold you all want to include the other part numbers that were sold that have a sale price within $ 3 of the part sold. Say the cost of the part sold was $10 then you would include parts with a sale price between $7 and $13.
  • Now using that logic in the above point I finally worked out how you arrived at your reequirement.
In the attached database I have
  • added a field called F7 in T2 as in your earlier post.
  • created a query qry_p1_cost_range. This query will list all the parts that are within the cost of P1
  • created a query qry_total_p1_total_units, this will give you the total units for all parts sold that are within the cost of P1, including P1
For all other parts you will have to create queries. This will a lot of queries depending on the number of parts. How many parts do you have?
Edit 08/09
parts_003 is the latest version as I added a query qry_cost_part_1 and updated design to qry_p1_cost_range
 

Attachments

Last edited:
There will be over 100,000 parts so creating a lot of queries is something I'd rather not do. Also, the master parts list will change month to month. I've attached a condensed database (Parts DB.zip) that shows what I'm looking for. Three tables: Parts Master, Units Sold and Parts Local.
1) Parts Master has Part Num, Group (the number of the Group to which Part Num belongs, Cost of Part Num, and Number in Group (which gives the number of Part Num's in this Group).
2) Units Sold has Part Num and Units Sold (which is the number of units sold for this part).
3) Parts Local lists the parts that are of interest to this warehouse. It starts with Part Num. The following fields in this table were imported from Excel to show desired results, but these fields need to be created with a query. Group and Cost woudl come from the Parts Master table. Units Sold would come from the Units Sold table. Group Units needs a query that computes the Units Sold for the Group to which this Part Num belongs (e.g. for Group 9801, this field contains 30,139 for every Part Num in Group 9801).

Now the tricky part. The next field, 'Units Sold by Cost Within Grouping', was imported from Excel but it needs to be generated with a query. It shows the Units Sold for this Part Num's Group but only for Part Num's in this Group whose Cost is within $2.00 (+/- $1.00) of the Cost of all other Part Num's in this Group. The next field, 'Manually Set by Group and Cost Within $2', was manually set by me and it illustrates what I'm talking about. If you filter, say, on '1' in this field, you'll see the Part Num's for Group '6373' whose Cost's are within $2.00 of each other. Field 'Units Sold by Cost Within Grouping' shows '50' for this set of Part Num's.

All rather complicated and I'm not sure it can be done. Maybe VBA code is needed. But I appreciate your help and understand if your time on this is limited.
 

Attachments

Users who are viewing this thread

Back
Top Bottom