Crosstab query help - can't seem to do this with the Wizard

[Dave]

I have a table called IOList with the following fields:

Address Text
ObjectNum Number
Description Text
IsOnAddress Yes/No
IsOffAddress Yes/No
IsMonAddress Yes/No

Here are 4 sample rows:

I:001/00 1 FAN 1 Yes No No
I:001/01 1 FAN 1 No Yes No
I:001/02 1 FAN 1 No No Yes
I:001/03 2 FAN 2 Yes No No

I want a crosstab query to produce the following fields for each row:

ObjectNum
Description
On Address
Off Address
Mon Address

For example:

1 FAN 1 I:001/00 I:001/01 I:001/02
2 FAN 2 I:001/03

 

[Tom Ellison]

Dear Dave:

Here's a way you could look at this that I think would solve your
problem.

Your 3 columns (IsOnAddress, IsOffAddress, and IsMonAddress) seem to
be mutually exclusive. Is it the case that one and only one of them
is Yes in every row? If so, then you have seemingly taken one atom of
data and put it into 3 columns. To illustrate, consider this
transformation of your sample data:

I:001/00 1 FAN 1 IsOn
I:001/01 1 FAN 1 IsOff
I:001/02 1 FAN 1 IsMon
I:001/03 2 FAN 2 IsOn

While it would be best to change this table, that may create more work
for you than you'd like right now. So, here's a way to transform
this:

SELECT ObjectNum, Description, "IsOn" AS Address
FROM IOList
WHERE IsOnAddress = Yes AND IsOffAddress = No AND IsMonAddress = No
UNION ALL
SELECT ObjectNum, Description, "IsOff" AS Address
FROM IOList
WHERE IsOnAddress = No AND IsOffAddress = Yes AND IsMonAddress = No
UNION ALL
SELECT ObjectNum, Description, "IsMon" AS Address
FROM IOList
WHERE IsOnAddress = No AND IsOffAddress = No AND IsMonAddress = Yes

Save this query and perform the crosstab on that. By the way, I
didn't know whether your Yes/No values are boolean or are text. You
may need to adjust the above query for what they really are. If they
are text put double quotes around them. If boolean, 0 and -1 for Jet,
0 and 1 for MSDE.

Any rows in the data where there is not exactly one of the three
option which is Yes would be excluded. Would you have any such rows?
We could create a value for that and put it in the crosstab too, as an
additional column.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Dave]

Thanks for the detailed response. Just to clarify, yes the IsOnAddress,
IsOffAddress and IsMonAddress are Yes/No fields and are mutually exclusive
(only zero or one of the three flags will be set). If no flags are set then
I want the row to be excluded from the query results.

When you say it may be better to change the table what would you suggest?
Combine the 3 flags into one field?

I'm not sure you understand my requirements exactly. Each row has an
Address text field. The 3 flags indicate what type of address that Address
field represents: an On address, an Off address, or a Monitor address.

I think the basic problem with the crosstab is the fact that I want 3 (?)
column headings and a normal crosstab only allows 1?

I'm trying to do this with subqueries and LEFT OUTER JOINS and it almost
works but it is excluding rows that do not have the IsOnAddress flag set.
Access is changing my LEFT OUTER JOINs to LEFT JOIN when I save the query.
Are they the same thing?

 

[Tom Ellison]

Dear Dave:

See my comments in-line below.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Thanks for the detailed response. Just to clarify, yes the IsOnAddress,
IsOffAddress and IsMonAddress are Yes/No fields and are mutually exclusive
(only zero or one of the three flags will be set). If no flags are set then
I want the row to be excluded from the query results.

What I suggested will do exactly that. It would also exclude any row
with more than on "Yes" value.

When you say it may be better to change the table what would you suggest?
Combine the 3 flags into one field?

Yes. The table would look just like what the UNION ALL query does.
Please try running this and you'll see. A combo box would work here
instead of 3 columns of check boxes. You would pick any one of 3
values (or more if it could ever expand) which excludes the other 2.
It would store this in the table which creates the crosstab column
headings.

I'm not sure you understand my requirements exactly. Each row has an
Address text field. The 3 flags indicate what type of address that Address
field represents: an On address, an Off address, or a Monitor address.

I really believe I got that.

I think the basic problem with the crosstab is the fact that I want 3 (?)
column headings and a normal crosstab only allows 1?

A "normal crosstab" allows a variable number of columns, depending on
the number of values in the one column on which it transforms. What
I'm doing is to create this column with the 3 values, so you'll get 3
columns in the crosstab.

I'm trying to do this with subqueries and LEFT OUTER JOINS and it almost
works but it is excluding rows that do not have the IsOnAddress flag set.
Access is changing my LEFT OUTER JOINs to LEFT JOIN when I save the query.
Are they the same thing?

Yes, these are the same thing. I don't know what it is you're doing
with the LEFT OUTER JOIN approach. You don't show much about that
here. But what I suggested should be exactly what you need. Why not
give it a try?

 

[Dave]

Sorry but I am not understanding this at all.

What I don't understand about the query you suggest is the "IsOn" AS Address, etc, parts. All that does is put a column with text values "IsOn", etc in the query results. I need the text string from the Address field.

Again, here is my original table:

Addr Obj Desc IsOn IsOff IsMon
I:001/00 1 FAN 1 Yes No No
I:001/01 1 FAN 1 No Yes No
I:001/02 1 FAN 1 No No Yes
I:001/03 2 FAN 2 No Yes No

Here is what I want from the query:

Obj Desc OnAddr OffAddr MonAddr
1 FAN 1 I:001/0 I:001/01 I:001/02
2 FAN 2 NULL I:001/03 NULL

I want the data from the table's Addr column to be placed in the OnAddr, OffAddr, or MonAddr columns in the output depending on the IsOn, IsOff, or IsMon flags in the original table.

 

[Tom Ellison]

Dear Dave:

Reworking what I suggested before:

SELECT Addr, ObjectNum, Description, "IsOn" AS AddressType
FROM IOList
WHERE IsOnAddress = Yes AND IsOffAddress = No AND IsMonAddress = No
UNION ALL
SELECT Addr, ObjectNum, Description, "IsOff" AS AddressType
FROM IOList
WHERE IsOnAddress = No AND IsOffAddress = Yes AND IsMonAddress = No
UNION ALL
SELECT Addr, ObjectNum, Description, "IsMon" AS AddressType
FROM IOList
WHERE IsOnAddress = No AND IsOffAddress = No AND IsMonAddress = Yes

I've added Addr to what is being shown. In the crosstab, you may be
able to use the FIRST() aggregate function if there is only one row of
On, Off, or Mon for it. Transform on the AddressType column.

If you're still having trouble, you may send me a fragment of your
database with this table and I'll try it first hand. It should be
zipped and must be less than 1 megabyte in size when zipped. I'll
respond in the newsgroup with what I find.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts