Define range and fill with summing values of other fields (1 Viewer)

[Maarten]

I'm a hobbyist with only basic understanding of SQL and Ms Access, so please bear with me while I try to explain my question. I'm trying to do something that obviously is way out of my league... I did a lot of searching and trying, but I guess I just don't really know what I'm looking for or understand what I'm supposed to do. Thanks for your help!

I'm trying to define a query that will ultimately show me the number of bottles of wine in a pricerange. So the end result should look something like:

PriceRange NumberOfOccurrences
>100------12
0-10-------50
10-25----149
25-50------29
50-100-----17

I have a table [Wijn] (Wine) with the prices in it in a field called [Marktwaarde] (market value). So the Pricerange should be divided into the segments above. I used a Count to construct the overview above using this Query:

SELECT TablePriceRange.PriceRange, Count(*) AS NumberOfOccurrences
FROM (SELECT IIf(Marktwaarde>=0 And Marktwaarde<10,"0-10",IIf(Marktwaarde>=10 And Marktwaarde<25,"10-25",IIf(Marktwaarde>=25 And Marktwaarde<50,"25-50",IIf(Marktwaarde>=50 And Marktwaarde<100,"50-100",">100")))) AS PriceRange FROM Wijn)  AS TablePriceRange
GROUP BY TablePriceRange.PriceRange;

(See Count_rslt.png)

Problem is, it counts the number of times a PriceRange occurs (i.e. a wine has one PriceRange), but what I would like it to do is sum the amount of bottles in stock in the overview.
The number of bottles present is located in the same table [Wijn] in three separate fields (depending on where they are stored): [WK], [WR] and [WB]. I just can't seem to be able to define an overview (table) with the ranges and the bottles in that range. I also tried DSUM without really understanding it's possibilities, but the way I used it, it will only give me the sum of a particular range:

SELECT DSum("[wb]+[wk]+[wr]","Wijn","[Marktwaarde]<10") AS Expr1
FROM Wijn
WHERE (((Wijn.Marktwaarde)<10));

(see dsum_rslt.png)

Can somebody please relieve me from my ignorance? Thanks!

 

[Galaxiom]

I have a table [Wijn] (Wine) with the prices in it in a field called [Marktwaarde] (market value). So the Pricerange should be divided into the segments above.

The query could be simplified using the Access Switch() function.

The number of bottles present is located in the same table [Wijn] in three separate fields (depending on where they are stored): [WK], [WR] and [WB].

In a normalized table structure there should be one field for quantity and one of location with a separate record for each WineID and LocationID. In full normalization the stock quantity would not be stored at all but calculated from the stock movement records.

I just can't seem to be able to define an overview (table) with the ranges and the bottles in that range.

There should never be an overview table because it can be calculated from the basic stock records.

 

[Maarten]

Thanks for your reply Galaxiom. So where does that leave me?
I appreciate your point, but I'm not quite sure how this helps me further, since the table is what it is...? I think changing it, would not make things easier for me, I've been using it for a while you see. Maybe if I could accept this error?
Or are you saying that what I would like is not possible?

Btw an overview table is only what I used to express what I was trying to do. I would like to have this insight. The table was only the result of the query (hope this clarifies).

 

[andydlindsay]

It sounds like what you need is something quick and dirty (aka not normalized). Why don't you create a price range table as you have defined them and reference that table with a new foreign key field in Wijn (your base table) called PriceRangeID?

Each record in Wijn will then have a Marktwaarde (market price) and a PriceRangeID. For instance, a bottle has a price of 29.99 and a PriceRangeID of 3 (25-50).

You can then get a sum of all bottles by price range by storage location with a query. If you would like a total of all bottles by price range regardless of storage location, just add another field to the query that sums the values of WK, WR, and WB.

Remember to make your join include all records from your price range table and not just records that match. That way you will still see the price range even if you don't have any bottles in stock.

Good luck!

 

[Uncle Gizmo]

It sounds like what you need is something quick and dirty (aka not normalized).

I meant what's the picture on your flag and the gif?