SELECT WHERE compare two fields

[bulb763]

Hi new here, registered as this problem will drive me mad if I dont get help - I'm sure it must be possible...



I've not got much experience with Access or any other sort of SQL.

I have tableA and tableB. tableA contains Col1 and Col2 (Concatenated PK). tableB contains Col1, Col2 and Col3 (PK). tableB Col1 lists distinct values from tableA Col1 - no problems. I want tableB Col2 to list distinct values from tableA Col2 where Col1 in tableA and tableB match. So that is a query with two variables. Please don't be distracted by DISTINCT - this is not the problem!

All the examples on the net I've seen of SELECT WHERE queries show something like WHERE variable = constant, so I don't even know if what I am trying to do is possible, never mind the details of it.

Please Help

[orange]

See if this helps.
http://beginner-sql-tutorial.com/sql-subquery.htm

If not, show some sample data and the query you are using.

[bulb763]

Thank you. Your link is useful - I welcome all that sort of fundamental info. The frustrating thing is that I seem to have constructed my query correctly yet it isn't doing what I expect.

Ok let's hope this works...

Its mostly self-explanatory hopefully. In table2 I want the values in the drop-down box in "PART REVISION" to be dependant on the value that is selected in "PART NUMBER". In order to do this I thought it would just be a matter of making it look at table1 to see which values were valid for PART REVISION with reference to PART NUMBER. Hope this makes sense.

I'm using Access 2003. Thanks

[orange]

There is no query in your example.

Others will tell you Not to use table level lookups.

See this link
http://www.mrexcel.com/forum/showthread.php?t=517308

[bulb763]

There is no query in your example.

There is, see table2 > design view > PART REVISION > Lookup > Row Source

Thanks you for the link - checking it out now.

[bulb763]

Others will tell you Not to use table level lookups.

See this link
http://www.mrexcel.com/forum/showthread.php?t=517308

How do I validate the data that is entered without these lookups / drop down boxes? ok I can do it at the form level, but would it not be better to have some sort of validation in place at the table level so that any front-end need not worry (too much - I suppose there is still a place for validation at the front-end level) about it.

The point was made to me that a back-end could potentially be accessed by any number of front-ends, so would it not be better to have one set of validations at that level, rather than have to create validations for each different front end?