Filling data in to one table from another, using some sort of lookup?

[Cfish3r]

Hi all,

I am going to be terribly rude and join simply to ask a question.
My will probably require a fair bit of Access usage, so hopefully I will quickly get skilled in it and perhaps may be able to help some others in the future.

I have an A level in computing, so I do understand a fair amount of the concepts, but I just need a bit of a pointer as to how to achieve the following.

My Data:

Table: XeroxRequest
- ID (auto-number)
- Usage Function (Text) *This contains only one of three values.
- Serial Number (text)
- Reading (Number) *This is a blank field, and is the field I am trying to fill.
- Reading Date (Date) *This is automatically filled in with the current date when importing data.
- Counter ID (text)
- Contract Number (text)
- Last Valid Reading (Number) *This is for a future validation step that I am not worrying about yet.


Table: OurData
- ID (Auto-number)
- Serial
- Meter (text)
- Reading (number)



Queries

1. First, the data in the OurData table needs to be formatted so that it matches the data in the other table. So far, I have a query that does the following.

Query: OurDataformatted:
- TrimSerial: Trim(Left([Serial],10))
*This is to strip unwanted characters from the serial so that it can match the 'serial' in the XeroxRequest table.

- Meter2match: IIf([Meter]="Colour","ZCOT",IIf([Meter]="MONO","ZBWT",IIf([Meter]="Black & White","ZBWT")))
* This replaces the possible entries in this field, with the relevant text so that it matches the 'usagefunction' in the XeroxRequest table.

- Reading.
*This is left as is.


What I need:

1. For every serial number in the XeroxRequest table, I need to search for an entry in the Query (or table) where the serial numbers match.
2. The UsageFunction also needs to match the 'Meter2match' in the query.

Further Info:
- The XeroxRequest table can contain multiple instances of one serial.
BUT it will only contain one combination of serial and Meter.

For example, it may contain:

--Serial---------useageFunction
- FBT1234567 ---- ZCOT
- FBT1234567 ---- ZBWT
- FBT7418526-----ZBWT
- FBT3698524-----ZCOT


- FBT3698524-----ZBWT


- The OurData table may also do the same. Hence the challenge is to match up the readings using the serial and usage function.















.....
What would be the best way to achieve this?
The person who can help me get this working will receive a drink on me (or something)!


MUCH appreciated,
Cameron Fisher.

[RuralGuy]

If this thread is actually solved, would you care to share your solution with others that read this Forum?

[Cfish3r]

Yes of course!
I must of been thick.

I double checked that all my serial number fields were correctly trimmed.

Then created a query with the following parameters:

[RuralGuy]

Thank you for sharing!