Pull letters from notes field if empty

[Elmobram22]

Hi,

I have a query that holds data based on a field. If the field [Device In] is "TimeStation-1" in TblTime for example it holds "AV" in the field [House]. Trouble is some fields are blank and when this is so I want it to pull the last two letters from the [Notes] field. I have attached the database. The query is [QryDeductionsandSleep Ins]. Let me know if this makes no sense.

Cheers,

Paul

 

[namliam]

Are you looking for something like
Nz([device in],Right([notes],2))

Going to skip your column names and choices of columns

 

[plog]

Going to skip your column names and choices of columns

Not me. You've got a lot of issues with the structure of your tables. Overall, the biggest issue you have is storing a ton of redundant data. Once you have a date/time, you don't need fields to store pieces of information you can calculate from that field.

tblEmployee:

[Employee] is redundant data. You already have the first name and the last name, no reason to store the whole thing as well.


tblTime:

No reason to store Employee ID and Employee Name in this table. You only should store the ID field.

You're [In] & [Out] fields eliminate the need for about 7 of the other fields (Day In, Date In, Time In, Day Out, Date Out, Time Out, Hours). All of those fields shouldn't be in the table, if you need them, which you probably don't--you can calculate them in a query. You don't store redundant data.

[Total Pay] also is redundant. It's a calculated value and you don't store calculated values....you calculate them.

Lastly, for this issue you are having (Last 2 Characters of Notes), you need to store that data in its own field. Each piece of discrete non-redundant data needs to go into its own field. With that you just have to look at the field the data is in and then work with it, instead of trying to extract out pieces of data from other fields.

I'd really focus on your table issues before adding more functionality to this thing.

 

[namliam]

Not me.

Me = lazy

 

[Elmobram22]

Hi,

I see what you're saying on all points and I would love to stream line it all to be fair but I'm pulling data from a csv file from a third party and they only allow a file of that type. A lot of the data is redundant but once I have completed a month the data will be cleared so stream lining isn't too much of an issue. It's solely to create timesheets. The staff at the house I'm suggesting this for will be using an online interface, again not my design so I am limited to what it allows. I have tried to compensate for this and make it straight forward and quick to use for my purposes. And thanks for the quick reply. Worked like a charm.

Cheers,

Paul