join 2 tables, but filter records

[Hutchy]

I want a query that gives me something like this from 2 different tables:

table1 - AA, AC, DE
table2 - AA01, AA02, AA03, AC01, DE01, DE02

query -
column 1 - column 2
AA - AA01
------ AA02
------ AA03
AC - AC01
DE - DE01
------ DE02

Is that possible?

 

[vbaInet]

Create a SELECT query that joins both tables via the field you mentioned, go to SQL view and change the join to look like this:

FROM table1 INNER JOIN table2 ON table2.Field LIKE table1.Field & "*"

 

[Hutchy]

Create a SELECT query that joins both tables via the field you mentioned, go to SQL view and change the join to look like this:

FROM table1 INNER JOIN table2 ON table2.Field LIKE table1.Field & "*"

Great!!!
Thanks!

One more thing - how do I get the records in table 1 to not repeat for each record in table 2? If it's possible.

 

[vbaInet]

You mean like you have it in your first post? It's much easier to do it in a report where you set the Hide Duplicates property of the textbox to Yes.

In a query, you'll need to get the Min() value of Column2 per Column1, e.g.:

AA - AA[COLOR=Blue]01[/COLOR]
AC - AC[COLOR=Blue]01[/COLOR]
DE - DE[COLOR=Blue]01[/COLOR]

... those are the min values of column2 per column1. Then use an IIF() statement to say:

New_Column1: IIf(Is Null [COLOR=Blue]MinOfColumn2[/COLOR], Null, Column1)