finding the before sequence number

[Gilbert]

Hi I have a question not sure if possible. I need to find the before sequence number based on a sequence number provided. I have a table A. Which has a 3 digit tick number and I've table B which Also has a 3 digit tick number. What I need is to look in table A with Ticket number of 103 and look into table B an Id that last tick associated with 103 was 100 based off table B

For example


Table A


machine Tick.
M1. 104
M2. 108
M3. 105

table B
machine. tick
m6. 167
m1. 100
m2. 106
m3. 104

output
machine. Tick 1. Tick2
M1. 100. 104
M2. 106. 108
M3. 104. 105


thanks for your assistance

[June7]

You reference 103 in your narrative but there is no 103 in the sample data. Did you mean 104?

Machine ID is not unique in Table B?

Maybe:

SELECT machine, Tick, DMax("Tick", "TableB", "machine='" & [machine] & "' AND Tick<" & [Tick]) As TickB FROM TableA;