Normalization woes - how do I reproduce a query?

[Frank]

I'm a newbie to databases. After reading Allen Browne's site I realized my
experience with Excel was not serving me well with MS Access (2000), so I
set out to normalize my db. Prior to doing this I was able to easily
create a query in Access and then paste the SQL into Dreamweaver. The
resulting recordset worked fine with my web app.

I can now appreciate the advantages of normalization but cannot reproduce
the same recordset for my web app.

Please have a look at the below link, it's a screenshot of the
relationships before normalization along with the recordset it generated in
Access. At the very bottom is a screenshot of the relationships after
normalization.

http://shycatwebs.com/PUB/BeforeAfter.gif

Here's the SQL....
SELECT Lodging.globalLoc, Lodging.regional, Lodging.location,
Lodging.LodgingName, Lodging.photo, Lodging.LodgingDescription,
IconAssignments.Icon1, IconAssignments.Code1, IconAssignments.Icon2,
IconAssignments.Code2, IconAssignments.Icon3, IconAssignments.Code3,
IconAssignments.Icon4, IconAssignments.Code4
FROM Lodging INNER JOIN IconAssignments ON Lodging.LodgingID =
IconAssignments.LodgingID;

The recordset is a listing of all 91 lodgings from the Lodging table and
the associated records from the IconAssignments table. As you can see,
some lodgings have no Icon Assignments while others can have up to 4.

After normalizing, all my attempts at creating queries failed. The best
resulted in Lodging records being duplicated for each assigned policy icon,
and records that had no assigned icons didn't appear at all. For example,
the above link shows the 1st record (Algonquin) has no assigned icons,
while the 4th record (Phantom Ranch) has two. What I got were 2 Phantom
Ranch records with each showing just 1 of the assigned icons (instead of 1
record with 2 icons), and no record for Algonquin. I could get lodging
records with no policy icons to appear if I entered a blank record in the
PolicyIcons table, and then in the IconAssignments table created a dummy
record that matched the LodgingID with the blank PolicyIconID. But I'm
still left with the duplicate record problem, and using dummy records in
this manner doesn't seem like the right thing to do just because I'm unable
to produce a working query.

What is the SQL needed to get the same recordset I had before normalization
but using the normalized db?

Reproducing the recordset appeals best to me because it means no rework in
existing Dreamweaver pages. However, I suspect another query would be more
appropriate if I didn't have code in existing web pages to consider. Would
you agree? And if so, how would such a query handle the (dupe & dummy)
problems I've encountered?

Regards,
Frank

 

[Duane Hookom]

Have you tried creating a crosstab?

BTW: I would use a standard select query with code in the web page to manage
the display.

 

[Frank]

Have you tried creating a crosstab?

Thanks for the reply.

Yes, I tried using the crosstab query wizard. I thought that would be
exactly what I needed, but it wasn't as straight forward as I hoped.
Perhaps I'm using it wrong. Here's the steps I've taken....

The first dialog said that to include fields from more than one table I had
to create another query first. So I closed the crosstab wizard and created
another query containing the 6 fields from the Lodging table, and the 2
fields from the PolicyIcons table (IconFor and Code). I named it
Prerequisite Query. Here's the SQL...

SELECT Lodging.globalLoc, Lodging.regional, Lodging.location,
Lodging.LodgingName, Lodging.photo, Lodging.LodgingDescription,
PolicyIcons.IconFor, PolicyIcons.Code
FROM PolicyIcons INNER JOIN (Lodging INNER JOIN IconAssignments ON
Lodging.LodgingID = IconAssignments.LodgingID) ON PolicyIcons.PolicyIconID
= IconAssignments.PolicyIconID;

This query had a duplicate lodging record for each icon assigned to it (eg:
2 records with 1 icon each). As well, it didn't include all the records
from the Lodging table, it was only pulling up Lodging records that had a
match in the PolicyAssignments table. That's when I added dummy records to
PolicyAssignments table for lodgings that had no icons.

The dummies gave me all the records in the lodging table. Now that all the
data was there I had only the dupes to contend with. At this point I
opened the crosstab wizard again and selected this Prerequisite Query from
the query list.

On the next crosstab dialog I presumed this would be a Detail Query since
the Summary options didn't seem to be what I was after. I had nothing to
calculate.

The next dialog asked "which fields' values do you want as row headings".
Although I could only select 3 of the 6 fields in the lodging table, I did
so, and clicked next.

The next dialog asked "which fields' values do you want as column
headings". At this point I started to lose it because I could only select
1 of the 2. I was expecting to input my own field names (Icon1, Code1, and
so on). Nevertheless I selected IconFor and clicked next.

The next dialog asked "what number do you want calculated for each column
and row intersection". So I selected Code from the 3 fields remaining in
the list, and First under the functions list. I also unchecked the box
that says "Yes, include row sums".

In addition to the 3 lodging field column headings, the results had 14
other column headings. One named <> (with no data), and 13 named after
records found in the IconFor field of the PolicyIcons table. As well,
there were only 65 records, not the 91 I expected.

Here's the SQL for the crosstab query.....

TRANSFORM First([Prerequisite Query].Code) AS FirstOfCode
SELECT [Prerequisite Query].globalLoc, [Prerequisite Query].regional,
[Prerequisite Query].location
FROM [Prerequisite Query]
GROUP BY [Prerequisite Query].globalLoc, [Prerequisite Query].regional,
[Prerequisite Query].location
PIVOT [Prerequisite Query].IconFor;

I completely lost it at this point. Not only were the results not what I
wanted, but the SQL seems to now reference another query within Access.
Would this fly with Dreamweaver? In other words, would this query work in
the web page as long as the Prerequisite Query already exists in Access?
Sorry if this is a stupid question, but I haven't used a query to create
another query before and don't know if DW or the server will complain or
simply pass the Prerequisite Query name to Access and let Access complete
the processing.

BTW: I would use a standard select query with code in the web page to manage
the display.

I'm trying to keep recordset code in web pages as simple as I can, but find
it increasingly difficult with the normalized db. I do use DW to pass a
lookup parameter from one page to the next. For example, to limit results
to only lodgings in the USA. So far I haven't had a problem with that,
it's just that I can't reproduce the recordset it expects now that the db
is normalized. Perhaps your answer to the above question will help give me
a better understanding.

Regards,
Frank

 

[Duane Hookom]

I would add PolicyIconID field to the SELECT fields of [Prerequisite Query].
Then create a query like:
====[qselPrereqWithSeq]====
SELECT [Prerequisite Query].globalLoc, [Prerequisite Query].Regional,
[Prerequisite Query].Location, [Prerequisite Query].LodgingName,
[Prerequisite Query].Photo, [Prerequisite Query].LodgingDescription,
[Prerequisite Query].PolicyIconID, [Prerequisite Query].IconFor,
[Prerequisite Query].Code,
DCount("*","PolicyIcons","PolicyIconID =" & [PolicyIconID] & " AND IconFor
<=""" & [IconFor] & """") AS Seq
FROM [Prerequisite Query];
=========================

Create a table [tblIconFields] with a single text field [IconField] and add
two records with values:
Code
Icon

Then finally combine all this in a crosstab query:
TRANSFORM First(IIf([IconField]="Code",

,[IconFor])) AS Expr2
SELECT qselPrereqWithSeq.globalLoc, qselPrereqWithSeq.Regional,
qselPrereqWithSeq.Location, qselPrereqWithSeq.LodgingName,
qselPrereqWithSeq.Photo, qselPrereqWithSeq.LodgingDescription,
qselPrereqWithSeq.PolicyIconID
FROM tblIconFields, qselPrereqWithSeq
GROUP BY qselPrereqWithSeq.globalLoc, qselPrereqWithSeq.Regional,
qselPrereqWithSeq.Location, qselPrereqWithSeq.LodgingName,
qselPrereqWithSeq.Photo, qselPrereqWithSeq.LodgingDescription,
qselPrereqWithSeq.PolicyIconID
PIVOT [IconField] & [Seq] In
("Icon1","Code1","Icon2","Code2","Icon3","Code3","Icon4","Code4");



--
Duane Hookom
Microsoft Access MVP


[QUOTE="Frank"]
[QUOTE]
Have you tried creating a crosstab?[/QUOTE]

Thanks for the reply.

Yes, I tried using the crosstab query wizard.  I thought that would be
exactly what I needed, but it wasn't as straight forward as I hoped.
Perhaps I'm using it wrong.  Here's the steps I've taken....

The first dialog said that to include fields from more than one table I had
to create another query first.  So I closed the crosstab wizard and created
another query containing the 6 fields from the Lodging table, and the 2
fields from the PolicyIcons table (IconFor and Code).  I named it
Prerequisite Query.  Here's the SQL...

SELECT Lodging.globalLoc, Lodging.regional, Lodging.location,
Lodging.LodgingName, Lodging.photo, Lodging.LodgingDescription,
PolicyIcons.IconFor, PolicyIcons.Code
FROM PolicyIcons INNER JOIN (Lodging INNER JOIN IconAssignments ON
Lodging.LodgingID = IconAssignments.LodgingID) ON PolicyIcons.PolicyIconID
= IconAssignments.PolicyIconID;

This query had a duplicate lodging record for each icon assigned to it (eg:
2 records with 1 icon each).  As well, it didn't include all the records
from the Lodging table, it was only pulling up Lodging records that had a
match in the PolicyAssignments table.  That's when I added dummy records to
PolicyAssignments table for lodgings that had no icons.

The dummies gave me all the records in the lodging table.  Now that all the
data was there I had only the dupes to contend with.  At this point I
opened the crosstab wizard again and selected this Prerequisite Query from
the query list.

On the next crosstab dialog I presumed this would be a Detail Query since
the Summary options didn't seem to be what I was after. I had nothing to
calculate.

The next dialog asked "which fields' values do you want as row headings".
Although I could only select 3 of the 6 fields in the lodging table, I did
so, and clicked next.

The next dialog asked "which fields' values do you want as column
headings".  At this point I started to lose it because I could only select
1 of the 2.  I was expecting to input my own field names (Icon1, Code1, and
so on).  Nevertheless I selected IconFor and clicked next.

The next dialog asked "what number do you want calculated for each column
and row intersection".  So I selected Code from the 3 fields remaining in
the list, and First under the functions list.  I also unchecked the box
that says "Yes, include row sums".

In addition to the 3 lodging field column headings, the results had 14
other column headings.  One named <> (with no data), and 13 named after
records found in the IconFor field of the PolicyIcons table.  As well,
there were only 65 records, not the 91 I expected.

Here's the SQL for the crosstab query.....

TRANSFORM First([Prerequisite Query].Code) AS FirstOfCode
SELECT [Prerequisite Query].globalLoc, [Prerequisite Query].regional,
[Prerequisite Query].location
FROM [Prerequisite Query]
GROUP BY [Prerequisite Query].globalLoc, [Prerequisite Query].regional,
[Prerequisite Query].location
PIVOT [Prerequisite Query].IconFor;

I completely lost it at this point.  Not only were the results not what I
wanted, but the SQL seems to now reference another query within Access.
Would this fly with Dreamweaver?  In other words, would this query work in
the web page as long as the Prerequisite Query already exists in Access?
Sorry if this is a stupid question, but I haven't used a query to create
another query before and don't know if DW or the server will complain or
simply pass the Prerequisite Query name to Access and let Access complete
the processing.
[QUOTE]
BTW: I would use a standard select query with code in the web page to manage
the display.[/QUOTE]

I'm trying to keep recordset code in web pages as simple as I can, but find
it increasingly difficult with the normalized db.  I do use DW to pass a
lookup parameter from one page to the next.  For example, to limit results
to only lodgings in the USA.  So far I haven't had a problem with that,
it's just that I can't reproduce the recordset it expects now that the db
is normalized.  Perhaps your answer to the above question will help give me
a better understanding.

Regards,
Frank
[/QUOTE]

 

[Frank]

Thank you so much for taking the time to do this. I had no idea it would
become so complex.

I followed the four steps you outlined below, unfortunately the results
didn't pan out. You can view a screenshot at
http://shycatwebs.com/PUB/Crosstab.gif

The dupe records still exist (see the highlighted Phantom Ranch records).
And of the 8 Icon/Code columns only the first two are populated with data.
Except for the field names and the number of columns, this data is
virtually identical to the Prerequisite Query.

BTW I see what you mean by using a standard select query with code in the
web page. I didn't know Dreamweaver displayed Access Queries as "Views" in
its recordset dialog. It's much simpler to select the View than to
copy/paste its SQL. Thanks for the heads up.
It also makes editing recordsets in DW

Regards,
Frank

 

[Duane Hookom]

I am so proud that you named a crosstab query after me ;-)
If you still need help, come back with your SQL views of your queries.

I created my data using the orders and other tables from Northwind and
aliased columns to match your object names.

--
Duane Hookom
Microsoft Access MVP

Thank you so much for taking the time to do this. I had no idea it would
become so complex.

I followed the four steps you outlined below, unfortunately the results
didn't pan out. You can view a screenshot at
http://shycatwebs.com/PUB/Crosstab.gif

The dupe records still exist (see the highlighted Phantom Ranch records).
And of the 8 Icon/Code columns only the first two are populated with data.
Except for the field names and the number of columns, this data is
virtually identical to the Prerequisite Query.

BTW I see what you mean by using a standard select query with code in the
web page. I didn't know Dreamweaver displayed Access Queries as "Views" in
its recordset dialog. It's much simpler to select the View than to
copy/paste its SQL. Thanks for the heads up.
It also makes editing recordsets in DW

Regards,
Frank

I would add PolicyIconID field to the SELECT fields of [Prerequisite Query].
Then create a query like:
====[qselPrereqWithSeq]====
SELECT [Prerequisite Query].globalLoc, [Prerequisite Query].Regional,
[Prerequisite Query].Location, [Prerequisite Query].LodgingName,
[Prerequisite Query].Photo, [Prerequisite Query].LodgingDescription,
[Prerequisite Query].PolicyIconID, [Prerequisite Query].IconFor,
[Prerequisite Query].Code,
DCount("*","PolicyIcons","PolicyIconID =" & [PolicyIconID] & " AND IconFor
<=""" & [IconFor] & """") AS Seq
FROM [Prerequisite Query];
=========================

Create a table [tblIconFields] with a single text field [IconField] and add
two records with values:
Code
Icon

Then finally combine all this in a crosstab query:
TRANSFORM First(IIf([IconField]="Code",

,[IconFor])) AS Expr2
SELECT qselPrereqWithSeq.globalLoc, qselPrereqWithSeq.Regional,
qselPrereqWithSeq.Location, qselPrereqWithSeq.LodgingName,
qselPrereqWithSeq.Photo, qselPrereqWithSeq.LodgingDescription,
qselPrereqWithSeq.PolicyIconID
FROM tblIconFields, qselPrereqWithSeq
GROUP BY qselPrereqWithSeq.globalLoc, qselPrereqWithSeq.Regional,
qselPrereqWithSeq.Location, qselPrereqWithSeq.LodgingName,
qselPrereqWithSeq.Photo, qselPrereqWithSeq.LodgingDescription,
qselPrereqWithSeq.PolicyIconID
PIVOT [IconField] & [Seq] In
("Icon1","Code1","Icon2","Code2","Icon3","Code3","Icon4","Code4");[/QUOTE]
[/QUOTE]