Hi,
I have tbl_Proj, tbl_Notes, and tbl_Proj_City. They are all joined by the tbl_Proj's ID.
I have a set list in tbl_City - tbl_Proj_City is 3 columns an ID for itself, tbl_City ID and tbl_Proj ID
In instances where a project covers more than one city, when I generate a report (or query) for that project I get each notes that number of times. If a project is in 3 cities I get each note 3 times.
I have a project entry form with a combobox subform for the city selection.
The report is from a qry, by Proj_ID, I need to show the cities - the "key" city is the first alphabetically (also lowest via autonumber in City_ID).
I would like to add that I do not know SQL. I have created this database using access commands, lots of help from this website and the QBE (big ups to Mr. Zloof)
I am thinking that I may be able to query the project ID for the Cities and somehow select the first alphabetically or the lowest in ID and store that somewhere? and then run a query that uses that and the notes to generate the report and then have a sub report for the other cities? I may be totally out to lunch here.
Thank you for your assistance