how can I use an expression to choose a number value from one of many text values

[tbjmobile@gmail.com]

Hi Everyone, I'm new to Access, trying to convert from Alpha 5 because they only seem interested in large entities now and I am a (very) small business. I notice that syntax is similar but not identical and I've been unable to find an equivalent to what I need to do. I need to choose a numeric value depending on a text field which is a customer code number, there are several codes for one price, several for another.



I used to use;
CASE (CusCode="001".OR.CusCode="002".OR.CusCode="005",20 ,CusCode="003".OR.CusCode="073".OR.CusCode="076",4 0,CusCode="054".OR.CusCode="056".OR.CusCode="080", 75) etc..

CASE in Access doesn't seem appropriate, I've tried multiple IIf functions but I would need about 60 of them and that seems un-necessary. IIf and ORs don't seem to work together for me and I've tried SWITCH but that didn't work.

I am using this to calculate values in a table and eventually it creates a customer account in the shape of a report. The numbers are charges to customers, depending on the code number which is different for 'batches' of differing size houses or apartments.

Thanks for any pointers anyone can give me to get started.
Trevor.

[davegri]

Select Case should work:

Select Case CusCode
    Case "001", "002", "005"
        PriceCode = 20
    Case "003", "073", "076"
        PriceCode = 40
...  and so on
End Select

[Minty]

If you have that many values you should put them in a table with the result you want as a the second field.
Then use a sub query or join to the new table to get the result directly.

This will be easily maintainable and scales for as many values and customers as you need. It will also be much more efficient than a lookup or function.

[Minty]

@Dave - I think this is in a query, and you can't use select case in a query.

[tbjmobile@gmail.com]

Wow, that was quick, thanks Guys. It wasn't a query but a calculated field inside the table.
I'll look at both suggestions, the most flexible seems the separate or joined table but I'll have to look at more courses on YouTube first !
Trevor.

[Minty]

The general recommendation isn't to use calculated fields, as they are very limited in what they can achieve and don't scale well.
If you added the table as suggested, you can simply create a query to produce the values as required.

[tbjmobile@gmail.com]

Thank you, Minty, I'm working on that right now but I only get an hour or so at a time. I'll let you know how I get on.
Trevor.

[tbjmobile@gmail.com]

Hello again, I've made another table with just 4 columns where the three prices for each customer code are stored. I can't get the combo box that drop down to populate the first table with the price field when I click on the relevant customer code, it just posts the customer code field instead. This must be an error I'm staring at but can't see but in the meantime I'm looking at UPDATE in SQL. I can't find any guidance anywhere (including M/S) that covers updating one table with data from another table. This would be ideal for me because the prices (e.g. cost and retail) for one particular customer code will never change because even if a customer is lost, the same code will never be allocated to a new customer so running the same UPDATE will never change the data in the historic records.
If someone could point me to somewhere where this is explained I would be really grateful.
So in English, what I need is;
UPDATE field 6, field 7 and field 9 in table Bookings, to the value stored in table Charges field 6, field 7 and field 9. If this could be invoked from the Bookings table whilst entering a new booking record, all the better.
Many thanks,
Trevor.

[davegri]

https://technet.microsoft.com/en-us/...=sql.105).aspx

You need to use a subquery to update one table from another in SQL:

[Minty]

If you create a query that gathers the data you need, you can then use that as a source for your update.
That's essentially what the Sub query Dave is steering you towards will do, in one query.

[tbjmobile@gmail.com]

Thank you both, I looked at https://technet.microsoft.com/en-us/...=sql.105).aspx Dave, but this is beyond me as a newbie to Access and it seems that SQL-server is needed which I don't have.
Minty, are you saying to create a lookup query and change this to an update query once the data retrieved is verified? I tried this and I can't get it to work. It works with updating within the same table but not when I want to pull data from my tblCharges into my tblBookings. Maybe I'm using the wrong syntax.

[Minty]

Post up a either the SQL or a picture of your query - or both.

[tbjmobile@gmail.com]

Hi again, this is my effort, did also try it in SQL with the same result. I don't understand what validation I am violating! You can't see it on the screenshot but the join IS from tblCharges-cuscode to tblBookings2018-Cuscode (as the foreign key).
Trevor.

[Minty]

Okay - close, but not what I was after. If you click in the query, that what I wanted to see.

As an observation - you shouldn't have a table called Booking2018.
This implies a fresh table each year, that is wrong. One table for ever. Add a suitable field to allow you to filter it, or simply use the ArrivalDate.
As an example of why the current method wouldn't work, what table does arrival on 28/Dec/2017 and left on 3/Jan/2018 go into?

[Minty]

In fact - I can now see what you are trying to achieve - don't try and store the total in the bookings table - just add it up from the charges when you need it.
A general rule of thumb don't store calculated values if you have the data available to calculate them.

There are many good reasons for this - suppose you had seen an error on a room charge and amended it.
You would now have to re-store the saved total value, and make sure that this happened in every possible place a change to data could occur.
Your calculations on the other hand, would automatically be correct to the new value.