Michael:
A crosstab query is the way to do this as SD suggests, but assuming that for
each person/event there is only one row in the table you don't need a
computed column as you can arbitrarily use an aggregation operator to return
the value of the Response column in each row, e.g. the MAX operator, as the
maximum value of a set of only one value is the value itself of course, so,
if we assume for this example that the table is called Attendances, a
cross-tab query would be like this:
TRANSFORM MAX(Response)
SELECT [Unique ID], FirstName, Surname, Notes
FROM Attendances
GROUP BY [Unique ID], FirstName, Surname, Notes
PIVOT Attendances.Event;
If there are events for which there has been no response by one or more
individuals or indeed by anyone, but you still wish to include them in the
result table then add an IN clause and use the NZ function to return a value
such as 'no response'. So, if in addition to the zoo and museum trips you
have a science park:
TRANSFORM NZ(MAX(Response),"No response")
SELECT [Unique ID], FirstName, Surname, Notes
FROM Attendances
GROUP BY [Unique ID], FirstName, Surname, Notes
PIVOT Attendances.Event
IN ("Museum Trip", "Zoo Trip", "Science Park Trip");
If you omit the IN clause but keep the NZ function then you will get a
column for each event where at least one person has responded, but no columns
for any events where there have been no responses at all.
Important points to note:
1. The values for the row headings, [Unique ID], FirstName, Surname, Notes
must be absolutely consistent across all four columns or you'll get more than
one row per individual.
2. The values for the events must be absolutely consistent throughout the
Event column or you'll get more than one column per event in the result table.
3. If you use an IN clause the values in its value list must exactly match
those in the Event column.
These points arise of course because the table is not normalized. If it
were decomposed into correctly normalized related tables then consistency
would be ensured. Three tables would be required, e.g. People, Events to
model the main entity types and another Attendances to model the many-to-many
relationship between them, with two columns referencing the primary keys of
Events and a Response column. You might find the following of interest. It
is the labels and queries from a demo Access file I produced showing how to
decompose 'flat file' data, in this case imported from Excel:
The following set of queries and explanatory notes are taken from a demo
file I produced some time ago to show how imported data can be decomposed
into normalized tables. The demo uses a simple international contacts lists
so there are countries, regions and cities involved in the addresses. Also
each contact can have more than one employer:
The query below inserts rows into the Countries table with unique values of
the Country column imported from Excel into the table MasterTable. This is
necessary before rows can be inserted into the Regions table as that table
references the Countries table in a many-to-one relationship.
INSERT INTO Countries (Country)
SELECT DISTINCT Country
FROM MasterTable;
Having inserted rows into the Countries table rows can now be inserted into
the Regions table with the query below. This joins the MasterTable to the
newly filled Countries table on the Country columns and inserts unique values
from the Region column of the MasterTable and the CountryID column of the
Countries table into the Regions table.
INSERT INTO Regions (Region, CountryID)
SELECT DISTINCT Region, CountryID
FROM MasterTable INNER JOIN Countries
ON MasterTable.Country=Countries.Country;
Having inserted rows into the Regions table rows can now be inserted into
the Cities table with the query below. This joins the MasterTable to the
newly filled Regions table on the Region columns. The Countries table is
joined to the MasterTable on the Country columns and to the Regions table on
the CountryID columns, thus taking account of any regions of the same name in
different countries. The query inserts unique values from the City column of
the MasterTable and the RegionID column of the Regions table into the Cities
table.
INSERT INTO Cities (City, RegionID)
SELECT DISTINCT MasterTable.City, Regions.RegionID
FROM Countries INNER JOIN (MasterTable INNER JOIN Regions
ON MasterTable.Region=Regions.Region)
ON (MasterTable.Country=Countries.Country)
AND (Countries.CountryID=Regions.CountryID);
The previous queries inserted rows into the Countries, Regions and Cities
tables. Following the insertion of data into the last of these, Cities, it is
now possible to insert rows into the Contacts table as this only needs to
reference the Cities table, the relevant Region and Country being referenced
via the relationships between these three tables. The query below does this
by joining the MasterTable to both the Cities table, on the City columns, and
to the Regions table, on the Region columns. The Cities table is also joined
to Regions on RegionID and the Countries table is joined to the MasterTable
on Country and the Regions table on Country ID. This is to take account of
the possibility of two cities having the same name, but being in different
regions, which themselves could theoretically have the same name but be in
different countries, so that the correct CityID value is inserted into
Contacts.
For simplicity it is assumed that contacts at the same address have unique
names. This might not always be the case, particularly with commercial
premises (the developer of this demo once worked with two Maggie Taylors in
the same building!). In such cases, however, there is likely to be some
distinguishing value such as Job Title or Department which could be used.
INSERT INTO Contacts ( FirstName, LastName, Address, CityID )
SELECT DISTINCT MasterTable.FirstName, MasterTable.LastName,
MasterTable.Address, Cities.CityID
FROM Countries INNER JOIN ((MasterTable INNER JOIN Cities ON
MasterTable.City = Cities.City)
INNER JOIN Regions ON (Regions.RegionID = Cities.RegionID)
AND (MasterTable.Region = Regions.Region))
ON (Countries.CountryID = Regions.CountryID)
AND (Countries.Country = MasterTable.Country);
The query below inserts rows into the Employers table with unique values of
the Employer column imported from Excel into the table MasterTable. This is
necessary before rows can be inserted into the ContactEmployers table as that
table references the Employers table.
INSERT INTO Employers (Employer)
SELECT DISTINCT Employer
FROM MasterTable;
Having inserted rows into the Contacts and Employers table it is now
possible to insert rows into the ContactEmployers table which models the
many-to-many relationship between Contacts and Employers. The query below
does this by joining the MasterTable to Contacts on the Address, LastName and
Firstname columns and to the Employers table on the Employer columns. The
ContactID values from Contacts and EmployerID values from Employers are
inserted into the two columns of ContactEmployers.
INSERT INTO ContactEmployers (ContactID, EmployerID)
SELECT Contacts.ContactID, Employers.EmployerID
FROM (Contacts INNER JOIN MasterTable
ON (Contacts.Address=MasterTable.Address)
AND (Contacts.LastName=MasterTable.LastName)
AND (Contacts.FirstName=MasterTable.FirstName))
INNER JOIN Employers ON MasterTable.Employer=Employers.Employer;
The above does assume that the imported data is completely consistent, which
might not be the case. If you'd like a copy of the demo itself, which is
made up of an Excel file from which the data is imported and the Access file
which decomposes it using the above queries mail me at:
kenwsheridan<at>yahoo<dot>co<dot>uk
Ken Sheridan
Stafford, England
