Problem with multiple instances

[spiral123]

I have a table that has a unit number and plate number field and a province field

Unit Plate Prov
1234 123AB456 Alberta
2345 145BC456 British Columbia

sometimes a unit will have two plates

Unit Plate Prov
1234 123AB456 Alberta
1234 125BC457 British Columbia
2345 145BC456 British Columbia

I also have a table with unit information

Unit Make Model Year
1234 Ford F150 2001

I need to join these tables so i only have one instance of each unit, and i need to do it through a query so no tables are altered
Im thinking maybe like so
Unit Make Model Year Plate Prov
1234 Ford F150 2001 123AB456/125BC457 AB/BC
any suggestions on how to accomplish this?

 

[dhookom]

There is a generic concatenate function with sample usage at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]