Multiple queries in one report?

[LPL]

I have a database with address information for 1500+
entries. We have 200+ divisions. Each "company" entry
fits into a division. Some fit into several divisions;
there is a field called division, one called div2, one
called div3. I would like to sort this database by
division for a report. Some company names will appear
multiple times based on whether there is an entry in the
div2 or div3 fields. How do I do this?

 

[Gerald Stanley]

The first point to note that your database is not
normalised, hence the difficulty in producing your query.
The divisions associated with each 'company' should be held
in a separate table e.g
tblCompanyDivision
companyId = primaryKey of data in Company table
divisionId = primaryKey of data in Division table

The query would then be
SELECT CD.divisionId, C.companyId, etc
FROM tblCompany AS C INNER JOIN tblCompanyDivision AS CD ON
C.companyId = CD.companyId
ORDER BY CD.divisionId

If you cannot change the table structure, then a UNION
query may solve your problem
e.g.
SELECT division, etc
FROM tblCompany
UNION
SELECT div2, etc
FROM tblCompany
WHERE div2 IS NOT NULL
UNION
SELECT div3, etc
FROM tblCompany
WHERE div3 IS NOT NULL
ORDER BY division

Hope This Helps
Gerald Stanley MCSD