Design Help Needed

[Marella]

My mind has gone blank, so I am hoping all of you can help me out. I really
feel like I should know this, but I am for some reason stumped.

I have two tables. The first one has the following fields:
Hospital, Type, Resources, and Phone.
The second table has the following fields:
City, Rank1, Rank2, Rank3...Rank50.

Populating the rank fields is the Hospital, and the rank is dependent on the
distance from each city. So, the user goes to the form, chooses the city from
the drop down and then gets the 50 hospitals in order of distance.

My problem is that I want the Type, Resources, and Phone next to the Ranking
on the form. What do I do?

Thanks!

 

[John W. Vinson]

My mind has gone blank, so I am hoping all of you can help me out. I really
feel like I should know this, but I am for some reason stumped.

I have two tables. The first one has the following fields:
Hospital, Type, Resources, and Phone.

What's its Primary Key? There are an awful lot of hospitals named "St.
Luke's"... Might you consider adding a HospitalID and perhaps some other
identifying information, e.g. city and state?

The second table has the following fields:
City, Rank1, Rank2, Rank3...Rank50.

And this table IS SIMPLY WRONG. "Fields are expensive, records are cheap". A
much better design would be City, Rank (a number 1 to 50), and HospitalID (or
Hospital).

Populating the rank fields is the Hospital, and the rank is dependent on the
distance from each city. So, the user goes to the form, chooses the city from
the drop down and then gets the 50 hospitals in order of distance.

My problem is that I want the Type, Resources, and Phone next to the Ranking
on the form. What do I do?

With the normalized design, a piece of cake - just join the two tables by
Hospital (or HospitalID).

With your overgrown spreadsheet... a nightmare; a query with the hospitals
table joined to your table FIFTY TIMES, almost surely giving you the Query Too
Complex error.

John W. Vinson [MVP]

 

[Jamie Collins]

There are an awful lot of hospitals named "St.
Luke's"... Might you consider adding a HospitalID and perhaps some other
identifying information, e.g. city and state?

Perhaps someone has already thought to tackle this problem centrally
<g>? Try a google search for "American Hospital Association" and
"national provider identifier number" or simply hospital+identifier
for other regions.

And this table IS SIMPLY WRONG. "Fields are expensive, records are cheap". A
much better design would be City, Rank (a number 1 to 50), and HospitalID (or
Hospital).

The OP should incorporate such a sequence number into the PK or other
UNIQUE constraint to ensure it uniquely identifies rank number for
each city, plus a validation rule to ensure it does not exceed 50.

your overgrown spreadsheet... a nightmare; a query with the hospitals
table joined to your table FIFTY TIMES, almost surely giving you the Query Too
Complex error.

FWIW (perhaps not much) the OP could do something like this without
the query becoming "too complex":

SELECT City, 'Rank1' AS rank_name, Rank1 AS rank_value
FROM Ranks
UNION ALL
SELECT City, 'Rank2', Rank2
FROM Ranks
UNION ALL
SELECT City, 'Rank3', Rank3
FROM Ranks
UNION ALL
....
UNION ALL
SELECT City, 'Rank50', Rank50
FROM CityHospitalsRankings;

Jamie.

--

 

[Marella]

Thank you so much, that makes sense! I knew I was totally blanking and doing
it incorrectly!! Thank you!

 

[Marella]

I wish they would have done it centrally. It took me quite some time using
Google Geocoder for all 865 cities in MN to do distance!

Many thanks for the SQL to assist me!! Much apprecitated.

 

[Jamie Collins]

Many thanks for the SQL to assist me!! Much apprecitated.

I trust you do this only as a temporary fix (query not updateable,
performance not good, schema maintenance issues, etc). Take JohnV's
advice and fix the table as soon as you can

Jamie.

--

 

[John W. Vinson]

FWIW (perhaps not much) the OP could do something like this without
the query becoming "too complex":

SELECT City, 'Rank1' AS rank_name, Rank1 AS rank_value
FROM Ranks
UNION ALL
SELECT City, 'Rank2', Rank2
FROM Ranks

Thanks Jamie... you're right, a normalizing union query is certainly the
better of the bad options for this.

John W. Vinson [MVP]