Grouping - How to combine two fields... 1

[keiem]

I have a reported based on a query derived from two tables. I would like to group my results by a list of "district names" which are common to every record. My problem is that some records belong to two districts, recorded as [DISTRICTNAME] and [DISTRICTNAME2] in the underlying tables. The same district names are common to both fields. As such, I would like the records that are in two districts to appear twice in the grouped report, once under each of the appropriate district name headings.

Could anyone tell me an easy way to achieve this? Thanks!

 

[dhookom]

Your table is not normalized. You could use a UNION query to normalize.

SELECT DistrictName as District, OtherFldA, OtherFldB
FROM tblYourTable
UNION ALL
SELECT DistrictName2 , OtherFldA, OtherFldB
FROM tblYourTable
WHERE DistrictName2 is not Null;

Use this query as the basis for your report.

Duane
MS Access MVP

 

[keiem]

Thanks that is exactly what I needed!! Works great.

I have a couple questions, though. Once I have added other tables and fields to the SQL statement, what is it that associates the [districtname] and [districtname2] fields together as the [district] field. Is it the order they appear in the SELECT statement? If listed another field first in the second SELECT statement, would it be the one associated with the "districtname as district"?

Also, might you have suggestions on how data such as this could be normalized at the front end? I've always found it easier to just create a second field for the second attribute.

 

[dhookom]

You must have the same number of fields and they must be listed in the same order.

Duane
MS Access MVP