Duplicates in Report/Query Due to frm and sbfrm

[wetsnow13]

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

[rpeare]

Let me see if I understand correctly.

You have a 'master' table (tbl_Proj) that stores the basic project information
For each project you may have one or more cities involved, these are stored in tbl_Proj_City
For each project city, you may have one or more notes involved, these are stored in tbl_Notes.

If this is correct, you would need to have the PK of tbl_Proj_City in your tbl_Notes table so that a note is directly tied to the city the note is relevant to. You do not need to carry the PK from the tbl_Proj table, you can get that by linking your tables.

In terms of your overall question, you are mixing between forms and reports, but really what it comes down to is that if you are doing a form you will need a main form, a subform and a sub-subform for data entry (if you're using a bound form)

In terms of a report you can group/sort the data any way you want and display only the items you want (i.e. you don't duplicate the project number for each project note for instance)

[Dal Jeanis]

One possible (simple) solution would be to add a yes/no flag on tbl_Proj_City that says whether this city is the reporting city.

Initially, set all of the flags to no. Then run an update query to set the lowest alpha city for each project to yes. (You could run this periodically to pick up any glitches where no reporting city had been set for a project, or you coudl run it immediately before the relevant query.

After that, you can just add "WHERE tbl_Proj_City.pcReportingCity = True" to the query to eliminate the dups.

The alternative is to build the subquery that picks out the tbl_Proj_City record with the lowest alpha city for each project, and then build that into each of your other reports where you need the dedup to occur. The first is easier and simpler, but the second is more "doctrinally sound", since the first method would be storing transient data that could be calculated from the remainder of the data on the tables.

[wetsnow13]

rpeare,

that is close.

I have a 'master' table (tbl_Proj) stores project number, billing number etc.

Each project may be in one or more cities. The cities are stored in tbl_Proj_City and are selected from a set tbl_City via combobox in a form.
tbl_Proj_City has Proj_City_ID (PK), Proj_ID (FK to tbl_Proj) and City_ID (FK to tbl_City)

Each project has many notes with the date of the note stored in tbl_Notes - these notes are project specific, not city specific.
tbl_Notes has an ID, a Note memo, a note date and Proj_ID (FK to tbl_Proj)

I would like a query that returns standard project data from tbl_Proj, the project city(ies), and the notes and their dates. As it is returned now I return multiple identicle lines except the city is changed. i.e. I get each note returned twice if there are two cities.

For filing purposes the first alphabetical city is used. I do not know how to select only that city. I think I could use that for the query to generate the report and have the other cities in a seperate query used for a sub-report. (i assume the QBE statement would just be NOT "whatever i need to get the first apha/number city")

Currently I generate the project specific report of all the notes using a form with combobox and "run report" button that June7? posted from datapigtechnologies website. I think this could get super messy if I need to run multiple queries.

[rpeare]

if you're printing this information on a report I'd use reports/subreports and not try to do it all in one query. IN other words your main report would be based on tbl_Proj, and you'd have two subreports one for the data in tbl_Proj_City and one subreport for the data in tbl_Notes.

The first city showing in your subreport base don tbl_Proj_City would be your filing city.

If you only want to show ONE city on your report, that being the first alpha city you'd have to create an aggregate query linking your tbl_Proj_City to your table tbl_Cities on the City_ID. Then add the city name and project_ID, click the sigma button (looks like a capital E on your ribbon bar), then choose MIN in the TOTALS line of the query on the city name. You should end up with a query that shows all project_ID's and the first alphabetical city for that project. Then you can link this back to your 'main' query. (you should also be able to do the same thing linking the project city table directly into your 'main' table depending I'm just offering this so you can see how to build the query to show the first alpha project city)

[wetsnow13]

rpeare,

I will give that a go.

Dal Jeanis and rpeare - thank you both very much.

[Dal Jeanis]

rpeare - there ya go making the "doctrinally sound" way look easy.