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

[tbjmobile@gmail.com]

Thanks Minty, but I'm not trying to store a total.
The tblCharges actually contains three columns of numbers, one called FrstWkDelia which is the one in the example. This number is the charge in euros that our cleaner charges us and the other two columns in tblCharges are what we charge to the customer etc.. As we complete the entry in the tblBookings2018 the key field is the customer code (Cuscode) which identifies the particular property to us and the database. When we get further along the row, there are the three columns for the charges information that are different for each batch of properties (codes). That is to say that we have a few properties that cost 20 to clean and we charge 35 for, a few that cost 40 and we charge 55 for, and so on. So in MS Access, in the tblCharges there will be about 40 rows of data which will relate to a cuscode and provide the particular number (of euros) for that property.
What I'm trying to do is to place those values into the tblBookings2018 from the looked up numbers in the tblCharges without going backwards and forwards to cross reference the value from the cuscode and then place it in the tblBookings2018 so that later on, we can bill the client and pay the cleaner.
I appreciate that this makes all the sense in the world to me, but I can only hope that it does to you. As I said earlier on in the thread, I used to use Alpha 5 and in that I used a CASE statement, saying if the case was that the cuscode = "002" or the cuscode = "045" or the cuscode = "075" the charge was 55 and so on to cover all the codes. In that era, I did this as a calculation on each field entered.
Regards,
Trevor.

[tbjmobile@gmail.com]

Only just seen your comment about the Bookings2018. I agree but I do think I could change that to just bookings and create a filter later on, at the moment we are still using Alpha 5 and I only have 7 entries in the bookings table until I prove it's all working properly. An arrival in December 2017 that was still around in January 2018 would have been carried over to the 2018 table when we copied over 2017 filtered records to the new 2018 table.
If I click the query, I ultimately see the Microsoft warning about violating the validation rules.

[Minty]

Okay - Can you post up a stripped down version of your database, with some sample data.
Although I think I know what you are trying to achieve it will be much easier to see in real data.
It may help if you Zip the file and maybe post up an excel example of your desired output.

I still think that ultimately you can simply query this rather than trying to store it into the booking table, but perhaps if rates change then you need it stored at the time, although there are other ways around even this scenario. (You include a valid from date field in your lookup table and then can historically get the right value - but lets keep it simple to start with... )

[tbjmobile@gmail.com]

Clients.zip
Hi Minty and thanks for your patience. Here is what I think you're asking for. I know that there are things missing and half done, I'm just obsessed with the current problem.
Trevor.

[Minty]

Just so I'm I think I'm on the right track here paste this into a new query and see if it does what you want.

UPDATE tblCharges INNER JOIN tblBookings2018 ON tblCharges.Cuscode = tblBookings2018.Cuscode SET tblBookings2018.ServDelia = [tblCharges].[ServDelia];

I will say right now that at first glance your table structure for your bookings and charges is not correct. You need to have a good read up about normalisation. http://rogersaccessblog.blogspot.co....on-part-i.html

You should never have fields that are storing information for a single possible outcome. e.g. if you have or more importantly could have more than one cleaner, then you shouldn't be storing which cleaner it is in a specific field called ConchitasRate.
I would say you need a tblPropertyCharges, and record the ChargeType, EmployeeID, WorkDate, PropertyID.
You can lookup what each Employee charges for each type of work over time in a your new EmpCharges table that stores the EmpID, WorkType, CostPrice, SalePrice, ValidFromDate.
All this would relate back to an Employees Table

Do you see how this extrapolates out? The ideas above are by no means complete or even well thought out, but might give you and idea of how to break down you data into the correct tables required.

Get a paper and pen out, and plan.
Remember two basic things a)that if you can't do it on paper - you can't do it in Access. b) Access is NOT a spreadsheet. It's way different but very powerful, IF your data is stored correctly in the first place.

[tbjmobile@gmail.com]

Hi Minty,
Yes thank you, that works. I haven't analysed it yet to see how it differs from the sql that I tried but I will do. Also I can play with it to populate the other two fields. I appreciate your criticism about my approach, I know i'll never make a programmer, and I have done some paper and pencil work. I shall do so again, once I've cracked this problem and I did intend to re-organise the table.
As a humorous anecdote, Delia has her own company and we sub-contract her, and her Spanish cleaners, we've been working together for more than 25 years so I clearly didn't envisage any changes in the future !
Anyway, thank you again.
Trevor.

[Minty]

Hence DeliaRate Makes sense!

Please don't take my ramblings as anything other than trying to help you avoid design problems now, that will "bite you on the arse!" later.

They really aren't meant as criticism, if you read through other peoples problem questions (particularly new users), they are almost always related to a poor choice of design, which is now making what should be a simple task very awkward, or impossible.
They then spend a month of Sunday's kludging some workaround that won't scale if something else changes, when if they had got the basics right would have all been completely unnecessary! /minor rant over/

[tbjmobile@gmail.com]

I completely understand and agree, I'm known in the family for making simple things difficult.
Cheers.

[ssanfu]

I agree with the points Minty pointed out.

It looks like you are "Committing spreadsheet". Typically, Spreadsheets are short and wide; Access tables are tall and narrow.
I think "tblBooking2018" could be split into at least 2 tables.... maybe 3.


Here is some reading for you:

"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.


AutoNumber
----------------
Purpose Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key. (An autonumber should not have real world meaning and should not be displayed on a form)


Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers
(shouldn't use a text field for the PF field)

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Naming Conventions
https://access-programmers.co.uk/for...d.php?t=225837

One source about how to name things
http://access.mvps.org/access/general/gen0012.htm

What not to use in names
http://allenbrowne.com/AppIssueBadWord.html


Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names. (example: "C/O" is a bad field name. "CO" is better)
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.

[tbjmobile@gmail.com]

Hello Steve,
Thank you too, for taking the time to put together the advice and reading list, I will certainly read all of those articles. I have read loads of tutorials but they never seem to be an all encompassing account of many of the subjects covered. One example is that didn't find anywhere if using the auto number ID fields in two tables can be an ID to ID link or whether the linking field in one of the tables has to be other than the primary key. I expect that this will be covered in the Microsoft Access Tables: Primary Key Tips and Techniques.
So thanks again I will look at all the points, I have plenty of time because we are still using the old database Alpha 5
Regards,
Trevor.

[ssanfu]

One example is that didn't find anywhere if using the auto number ID fields in two tables can be an ID to ID link or whether the linking field in one of the tables has to be other than the primary key. I expect that this will be covered in the Microsoft Access Tables: Primary Key Tips and Techniques.

If you link two tables on the PK fields of each table, that is a one-to-one relationship. This is a very rare occurrence.
You want to have The PK field (autonumber) in one table linked to a field in another table (number - long). This is a one-to-many relationship (the most common).

I always use an autonumber type field as the PK field in my tables (autonumber type is a long integer type fields). The foreign key field (in the many table) must be a Long integer. Neither the PK nor the FK field should be displayed on a form.

I also use a suffix on the PK/FK field names to help identify what the fields are. If you use CustomerID for the PK field on one table and CustomerID as the FK field in another field, when creating queries or looking at a form in design view, it is hard to tell which field you are looking at.
If you use the suffix, CustomerID_PK and CustomerID_FK, it is real obvious which is which.
If you look at a table in design view, it is easy to tell which fields are the FK fields.
Using CustomerID as the field name in the many table, how do you tell if you would enter a customer ID or if it is a FK field?




When you have time , you should read these links:

Normalization Terms and Concepts
http://www.utteraccess.com/wiki/Norm...s_and_Concepts

Normalization
http://www.utteraccess.com/wiki/Normalization


What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization?
http://rogersaccessblog.blogspot.com...on-part-i.html
There are 5 parts....


The Normal Forms
=========================
The Normal Forms: Introduction
http://rogersaccessblog.blogspot.com...roduction.html
There are 5 parts......


If you can still see, try
Entity-Relationship Diagramming
==============================
Entity-Relationship Diagramming: Part I
http://rogersaccessblog.blogspot.com...ng-part-i.html
There are 4 parts......

[tbjmobile@gmail.com]

Thanks again Steve,
In fact I had read that this very afternoon on Roger Carlson's blog so I now understand 1 to 1, 1 to M and M to M via 2 x 1 to M.
I will certainly read all of the links provided. I still have questions that I haven't yet found the answers to, I expect these answers will be in one of these documents but I'll come back to the forum if not.
Cheers,
Trevor.

[tbjmobile@gmail.com]

Hi again,
Minty and ssanfu (Steve) very kindly gave me some pointers and reading matter as a newbie to Access. I have read all of the documents recommended and a lot more, so I'm moving forward a little and trying to normalise my main table - tblBookings.

I have some ideas but would like someone to say ye or ney to them so far please. My table contains all of the fields and wrongly, calculations at the moment and my thoughts are to firstly separate out fields to 2 or 3 tables. The main one would be tblBookings and only contain the information given by the property owner and directly associated data. CusCode and Arrive[Date] would become the primary key as it will always be unique (one property will only ever have one arrival on that date, or I sincerely hope so). Then Depart[Date], Property, PartyName, Pax (number of people in party) Extras ("leave outside lights on") and LimpCode. The last one does change from booking to booking depending on what the holidaymaker requests of the owner. It is a number and the number represents the level of cleaning and linen changes.

Next I thought the fixed numbers could remain in table tblCharges as now. These are costs of the sub-contracted cleaning service, again depending on the LimpCode.

Next the fields that require calculations and are used to a) create lists to organise cleaning b) later, pay the cleaners and c) charge the customer (owner of the property) on the account which will probably only be a report o send by email..

I understand that the calculated items can be done on the forms and reports later on down the line. I'm not sure how I would do that yet because some seem quite complicated. At the moment though I just would like to see if I'm heading in the correct direction.

A few points that might otherwise raise questions - ChOver is derived as a "Yes" if there is an arrival at the same property (Cuscode) on the same day as a departure there.
Nights, ChgWks, DaysOver and WksServ are used to work out how many weekly cleans there will be and the charges associated because the first (arrival) clean is more extensive and therefore more expensive that any cleans/linen change, afterwards.

Should I leave all of the calculated fields for the forms and reports?

Thanks for any help.
Trevor.

[tbjmobile@gmail.com]

This is the zip file that I forgot to attach !
Trevor.