Need help on development (1 Viewer)

[Maarten]

Hi all,

I saw this brilliant description quite nicely fitting my level of professionalism wrt Access and I quote >> "I am a newbie—self taught years ago, then forgot everything and am now returning to fix some things up." ... sort of...

I'm self taught in business modelling, SQL, vba etc. I get things going with the help of Google almighty and all you guys who actually know what they're doing. So...

I'm building this Access database to keep track of my wine collection and some related stuff. Currently I'm building a form which allows me to fill in and store my tasting notes. I have added this button which after clicking it obviously spits out a text file, containing HTML code, which I can copy into my Wordpress website. Now, part of this tasting note are points a wine can get. This score related to the wine's price gives a somewhat price/quality indication by the way of a score in stars. Scores range from 0 - 5 stars building up by the half, so 0 | 0,5 | 1,0 | 1,5 etc. 5 is the highest.

I can't find a clever way to calculate the number of stars a wine has earned to put in the HTML code. :banghead: I was hoping somebody here would be so kind as to share their thoughts on how to do this. I have the price and the total score available. Here's the table for finding the right number of stars, btw. points are the lower limit, e.g. a wine of €3,75 with a score of 73 pts. earns 3 stars. A wine of €11,99 with 83 pts. gets 3,5 stars:

>>See the file attached<<

I hope I've been clear in explaining what it is I'm looking for and I've given any necessary info. If not, my apologies and please let me know what to add. I'm running Acces 2010 64-bit.

 

[MarkK]

I would probably convert your price/score/star table into two tables in a database. One table will be the price thresholds, since prices are subject to change over time. The second table will be something like . . .

tScoreStar
ScoreStarID
PriceThresholdID
Score
Stars

This will require two lookups, one to find the threshold ID given the price of the current wine, and then one to find the stars for the given score and threshold.
The first table would be . . .

tPriceThreshold
PriceThresholdID
Price

Normally I would not store a single variable value like price as the only data point in a table, but in this case the threshold functions like a discrete object, and simplifies the process of updating your thresholds as a result of inflation. You can update the threshold prices annually, for instance, by the annual inflation rate with a single query.
To find the stars in tScoreStar, filter a recordset for the correct thresholdID and for scores <= your test score and sort it by score DESC, and read the stars from the first record.

 

[Maarten]

Wow! Well that should keep me busy for some time! It sounds doable, but I'm not sure whether I fully understand what you're saying just yet, but learning on the job I'm sure to let you know my progress (or questions...)

Thank you lagbolt for sharing your ideas with me. It sure is starting to get to me why I couldn't come up with a working solution. I do feel I'm finally heading in the right direction...

Appreciate it!

 

[Maarten]

Hello again, I tried my best, can't get it to work. I'm just too much of a beginner I'm afraid. Would you please be so kind as to help me get my desired result? Thanks very much! What do I have...
I made two tables:

I put in the high and low value, cause I couldn't figure out how to query (not VBA) the right ID out of it, based on the price with only 1 price in the table

Table PriceThreshold
PriceThresholdID
ThresholdValueLow
ThresholdValueHigh

Table StarScore
ScoreStarID
PriceThresholdID
Score
Stars

I wrote a query to get the PriceID "QRY GetPriceThresholdID". Price is in table Wine:

SELECT a.Id, b.PriceThresholdID, b.ThresholdValueLow, a.Price, b.ThresholdValueHigh
FROM PriceThreshold AS b, Wine AS a
WHERE (((a.Price)<=b.ThresholdValueHigh And (a.Price)>b.ThresholdValueLow)) Or (a.Price)>b.ThresholdValueHigh And b.ThresholdValueLow=50;

Now I'd like to get the stars from the table, but I can't get it to work.
Last try was to make a button and activate some VBA... Here's the code:

Private Sub AddSterren_Click()
Dim PriceID, Score, WijnID

WijnID = Forms!Proefnotitie!NaamWijn 'Gets the wine ID from the table

DoCmd.OpenQuery "QRY GetPriceThresholdID" 'Run Query
PriceID = DLookup("PriceThresholdID", "QRY GetPriceThresholdID", "[Id]=" & WijnID) 'Uses WineID to get the price from the table
DoCmd.Close acQuery, "QRY GetPriceThresholdID" 'Close Query

Score = Forms!Proefnotitie!ScorePnt

End Sub

So now I have the score and I have the PriceID (in VBA). I tried DFirst, but couldn't get it to work. Couldn't get Dlookup to work either and I had a go at the recordset as proposed by lagbolt, but I lack the knowledge and Googling for some time didn't make it that much clearer I'm afraid.
So please any help on how to get the stars out?

Finally, I don't really know how to automatically trigger an event to calculate the stars and put it in the Stars Textbox in the form. It needs to be activated the moment another Textbox called ScorePnt calculated the score (needed to find the stars).
Can I for instance put the Event Procedure in the 'On Change' property of the ScorePnt Textbox and have the VBA put the value in the Stars Textbox? I've already tried the 'On Change', 'Before Update', 'After Update' and 'On Exit, 'but after calculating the score, nothing happens...
Thanks for any thoughts given and time spent on helping me out!

 

[jdraw]

Maarten,

A good reference for functions, syntax and examples is techonthenet

Here's the link for DLookup
http://www.techonthenet.com/access/functions/domain/dlookup.php

Also you should be aware that Access/vba requires you to explicitly Dim variables,
eg Dim MyInt as Integer

If you don't do this, the variable will default to Variant type.

If you do Dim A, B, C as Integer,

A and B will be variants, C will be Integer.

 

[MarkK]

Maarten, if you post your database I'll take a look.

 

[Maarten]

@jdraw, thank you for the link and the info on variables. I think I'll need to be a bit more specific. A good lesson.

@lagbolt, I appreciate it! It is too big to upload (30Mb) (I already inputted some data inc. pictures for test purposes). I can't post links, but maybe this works, if you don't mind editting brackets out of the link - I uploaded it to:
www(.)stemvers.nl/download/Wijn.accdb
The db is in Dutch, so if I need to explain anything, please let me know.

 

[MarkK]

See if this makes sense...

...This shows a few things you can do, from writing a query, using it in a function, and creating a form to find stars.
The key is to join the tables, and then sort them descending for price and score where price and score are <= the price and score you are testing for, and the first record is the correct one.

And I just realized an error I made. In the query I have only done < (less than). Those comparisons should be changed to <= (less than or equal to).

hth

 

[Maarten]

Wow, just like that huh? Thanks a million lagbolt. I only had a brief look, but this should help me out I'm sure. Will look into it deeper when I have some time the coming week. It does go a little above my head, so if I have any questions Im sure to report back here. Appreciate the help big time! Thanks.

 

[MarkK]

But the concept is simple, right? Like if I have thresholds . . .

0
5
10
15

. . . and I need to determine where some other number, say 12, falls in respect to those, then I filter for thresholds less than (or equal to) 12 . . .

0
5
10

. . . sort descending . . .

10
5
0

. . . and pick the first one.

In your case we apply this logic twice, once for the price and once for the score. The rest is implementation.

Cheers,

 

[Maarten]

Oh yes, absolutely true. The concept is beautiful. Perfectly demonstrates my lack of experience. In particular I was referring to the sorting mechanism in the query. Couldn't have done that myself. I do understand it when reading it. Sorry for not being too clear straight away. I'll keep you posted on my progress!

 

[Maarten]

YES! I got it working! I used the On Current Form Event to trigger the VBA code in which I call the function you wrote for me. So when a new record is loaded, it will calculate the number of stars for each record again.
Thanks very much lagbolt. No way I could have done it without your help!
Btw, how incredibly neat the way you use the Crosstab Query to reconstruct my table. I'm impressed. Will read some more on how to do this.

@jdraw Thanks again for your remark on variable declaration, especially the last bit. That saved me searching for a solution big time, cause I got a Type mismatch, where I assumed I had declared it to be an integer, but you showed me it actually was a viariant!

 

[MarkK]

Right on Maarten. Thanks for posting back with your success!