Query Possibly Using Count Function

[zooropa66]

Hi Everyone,

I have 2 tables:

tbl_lot

lot_id (PK) lot process_step_id
1 lot-a-01 1
2 lot-a-02 2
3 lot-b-01 1
4 lot-b-02 1

tbl_process_step

process_step_id (PK) process_step
1 step1
2 step2

I'd like a Combo to show lotb-01 and lotb-02 because the first 5 characters "lot-b" for these lots is at the same process step.

The combo would not show lota-01 and lot-a2 because the first 5 characters of those lots are not at the same process step (must have at least 2 lots with first 5 characters at same process step)

I'm sure it involves using the Count function in an Expression but after 2 hours i'm not making much progress.

Any help would be greatly appreciated,

Thanks

 

[Galaxiom]

That is very clumsy.

I have not looked closely but it is probably sensible to separate the letter and number to different field. One thing absolutely for certain, you should not be storing the string "lot" in every record.

BTW Count is unlikely to be involved at all.