Backwards database...

[C. Homey]

I am helping some colleagues with an Access 2000 database and they
have set their database up backwards - in other words, instead of
setting up the fields so that you have a single field called "hotel",
they actually created a field that is each hotel (Four Seasons /
Hilton / Marriott). They only have a small number of records in their
database, but they have maxed out the fields (255)fields. Oy. So
their problem is apparent: when they create a query, they have to
deal with all 255 fields. So I have been asked if you can get a query
to report only the fields with data in them.

I am not a programmer so I don't know how to write code that might
suppress unused fields or pull only fields with data in them.

So, here's the question: is there a way to query all the fields, yet
only see the fields that contain data? If this is unclear, I am
adding a chart that might help below:

Database looks something like this:

Last Name / First Name / Address / Marriott / Sheraton / Hilton / Four
Seasons
Smith Tom 123 St. x
Jones John 4546 St. x
Williams Bill 3535 St. x

Can Query create a table that looks like this:

Last Name / First Name / Address / Sheraton / Hilton
Smith Tom 123 St. x
Jones John 4546 St. x
Williams Bill 3535 St. x

Any assistance will be hughly appreciated!!!!

 

[Lauri]

I'm wondering if using a crosstab query for the hotel information might get
you started on the track you're looking for.

-Lauri

 

[Duane Hookom]

I wouldn't put any more effort into this application until it is normalized.
There are methods that combine union and crosstab queries to get close to
what you want but that would be like putting a bandaid on a compound
fracture. There are some crosstab report samples at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4

 

[John Vinson]

I am helping some colleagues with an Access 2000 database and they
have set their database up backwards - in other words, instead of
setting up the fields so that you have a single field called "hotel",
they actually created a field that is each hotel (Four Seasons /
Hilton / Marriott). They only have a small number of records in their
database, but they have maxed out the fields (255)fields. Oy. So
their problem is apparent: when they create a query, they have to
deal with all 255 fields. So I have been asked if you can get a query
to report only the fields with data in them.

I am not a programmer so I don't know how to write code that might
suppress unused fields or pull only fields with data in them.

So, here's the question: is there a way to query all the fields, yet
only see the fields that contain data? If this is unclear, I am
adding a chart that might help below:

OY. What a nightmare!

I would STRONGLY - violently even - urge scrapping this hideous
spreadsheet in favor of a "tall-thin" normalized table. You can do it
in three or four "Normalizing Union" queries.

First, create a People table with fields PersonID (autonumber),
LastName, FirstName, and Address (and any other non-hotel fields). Run
an Append query from those fields to fill it.

Second, manually create a 250 or so record table of Hotels, with
fields HotelID and HotelName. Put a unique Index on HotelName.

Third, create a "HotelLink" table with two Long Integer fields -
PersonID and HotelID.

Finally, create a series of UNION queries like:

SELECT People.PersonID, Hotels.HotelID
FROM (People INNER JOIN bigtable
ON bigtable.FirstName = People.FirstName
AND bigtable.LastName = People.LastName)
INNER JOIN Hotels ON Hotels.HotelName = "Marriott"
WHERE [Marriott] = "x" ' or True, if the hotel field is yes/no
UNION ALL
SELECT People.PersonID, Hotels.HotelID
FROM (People INNER JOIN bigtable
ON bigtable.FirstName = People.FirstName
AND bigtable.LastName = People.LastName)
INNER JOIN Hotels ON Hotels.HotelName = "Hilton"
WHERE [Hilton] = "x"
UNION ALL
SELECT People.PersonID, Hotels.HotelID
FROM (People INNER JOIN bigtable
ON bigtable.FirstName = People.FirstName
AND bigtable.LastName = People.LastName)
INNER JOIN Hotels ON Hotels.HotelName = "Sheraton"
WHERE [Sheraton] = "x"
UNION ALL
SELECT People.PersonID, Hotels.HotelID
FROM (People INNER JOIN bigtable
ON bigtable.FirstName = People.FirstName
AND bigtable.LastName = People.LastName)
INNER JOIN Hotels ON Hotels.HotelName = "Motel 6"
WHERE [Motel 6] = "x"

I'd limit this to say 20 or 25 hotels, or you'll run the risk of
getting a "Query Too Complex" error - try pushing it to see.

Then save this query, and base an Append query on it to populate your
HotelLink table. Do the same with the next 20 hotels until you're
done.

Now you'll have the exact same data in a properly normalized table,
and can create a Crosstab based on the three tables to generate your
desired result.

 

[Jeff Boyce]

I'll second/third the notions that this will expand to consume the rest of
your life if you don't normalize the data first! Or, as an alternative,
could you stuff the data into Excel and do what you need without using
Access?

Just a thought...

Jeff Boyce
<Access MVP>

 

[C. Homey]

On 12 Mar 2004 15:20:04 -0800, (e-mail address removed) (C. Homey) wrote:

John - that you SOOOO much for your help with this! It's going to be
a tedious process but I believe your query solution is going to work.
You haven't written a book of problems and solutions by any chance,
have you? I'd buy it in a heartbeat!

Thanks again!!!

OY. What a nightmare!

I would STRONGLY - violently even - urge scrapping this hideous
spreadsheet in favor of a "tall-thin" normalized table. You can do it
in three or four "Normalizing Union" queries.

First, create a People table with fields PersonID (autonumber),
LastName, FirstName, and Address (and any other non-hotel fields). Run
an Append query from those fields to fill it.

Second, manually create a 250 or so record table of Hotels, with
fields HotelID and HotelName. Put a unique Index on HotelName.

Third, create a "HotelLink" table with two Long Integer fields -
PersonID and HotelID.

Finally, create a series of UNION queries like:

SELECT People.PersonID, Hotels.HotelID
FROM (People INNER JOIN bigtable
ON bigtable.FirstName = People.FirstName
AND bigtable.LastName = People.LastName)
INNER JOIN Hotels ON Hotels.HotelName = "Marriott"
WHERE [Marriott] = "x" ' or True, if the hotel field is yes/no
UNION ALL
SELECT People.PersonID, Hotels.HotelID
FROM (People INNER JOIN bigtable
ON bigtable.FirstName = People.FirstName
AND bigtable.LastName = People.LastName)
INNER JOIN Hotels ON Hotels.HotelName = "Hilton"
WHERE [Hilton] = "x"
UNION ALL
SELECT People.PersonID, Hotels.HotelID
FROM (People INNER JOIN bigtable
ON bigtable.FirstName = People.FirstName
AND bigtable.LastName = People.LastName)
INNER JOIN Hotels ON Hotels.HotelName = "Sheraton"
WHERE [Sheraton] = "x"
UNION ALL
SELECT People.PersonID, Hotels.HotelID
FROM (People INNER JOIN bigtable
ON bigtable.FirstName = People.FirstName
AND bigtable.LastName = People.LastName)
INNER JOIN Hotels ON Hotels.HotelName = "Motel 6"
WHERE [Motel 6] = "x"

I'd limit this to say 20 or 25 hotels, or you'll run the risk of
getting a "Query Too Complex" error - try pushing it to see.

Then save this query, and base an Append query on it to populate your
HotelLink table. Do the same with the next 20 hotels until you're
done.

Now you'll have the exact same data in a properly normalized table,
and can create a Crosstab based on the three tables to generate your
desired result.

 

[John Vinson]

John - that you SOOOO much for your help with this! It's going to be
a tedious process but I believe your query solution is going to work.
You haven't written a book of problems and solutions by any chance,
have you? I'd buy it in a heartbeat!

<blush> Thanks! Sorry, but no I haven't - some of my colleagues have
done such good jobs with their books that there's no way I could
compete.