Table design for applying Yearly Pricing static 1

[nastar1]

I have a table called tblCLIN with

CLINID autonumber as my Primary Key
Number text
FY05 number (is a dollar amount)
FY06 number "
FY07 number "
FY08 number "

Sample table result:

Number FY05 FY06 FY07 FY08
1011 $4.01 $4.33 $4.39 $4.46
1012 $1.09 $1.19 $1.29 $1.39
1019 $5.43 $5.55 $5.70 $5.85
1140 $3.11 $3.18 $3.29 $3.35

And another table with Year in it with the format of 2005, 2006, etc.

I need to be able to show on a form and report, the related FYxx dollar amount for each number for the appropriate Year.

For example if the Number on a form or report is 1012 and the Year is 2006, i'd like the amount $1.19 to display.

 

[GingerR]

How about Normalizing your table structure first? It should be instead:

ClinID
FiscalYear
Amount

1011 2005 $4.01
1011 2006 $4.33
1011 2007 $4.39
1012 2005 $1.09

etc.

Then you will be able to write queries easily for the rest of the db's life.


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[nastar1]

Thanks GingerR,

That should work nicely. I was having a brain lock and needed a push.

 

[nastar1]

After looking more closely, I see a potential problem with your table suggestion.

You forgot the Number field. It is actually a text field 4 characters long. Each year I get a handful of new 'Numbers' that each have a new cost figure associated.

Fields in my table using your suggestion are:

CLINID the autonumber field
Number
Year
Rate

I would end up with many 'Number' entries, one for each year that the rate changes.

Doesnt seem that I would have a normalized table in this case. Do I need to have a separate table for the Number?

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nastar1]

Studying up on the relational database hasnt helped. Each year I have new pricing for an item (CLIN). I must keep the old pricing in the database by the year it is related to.

My tables are:

tblWA
WAID Long Integer 4
Title Text 255
Year Text 4
CLIN Long Integer 4

tblCLIN
CLINID Long Integer 4
CLIN Text 4
Descrip Text 255

tblCLINRATE
RATEID Long Integer 4
Year Text 4
Rate Currency 8
CLINID Long Integer

I can't seem to get a query that will show all the Titles and the related CLIN's with the appropriate Rate where the Year in the RATEID table matches the Year in the WAID table.

I feel I've taken a wrong fork in the road somewhere in my table design, but can't find my way back.

My table relationships are one to many tblCLIN.CLINID to tblCLINRATE.CLINID and
one to many tblCLIN.CLINID to tblWA.WAID

 

[GingerR]

Hi. Sample data would help us. Is there more than one year's worth of data in the Rate table?


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[nastar1]

Sample data is below.

CLINID CLIN Descrip
1 5010 Management articles
2 6107 Data processes
3 6119 Outbound taskers

RATEID Year Rate CLINID
1 2005 $71.05 1
2 2006 $71.90 1
3 2007 $74.88 1
4 2008 $75.76 1
5 2005 $44.15 2
6 2006 $46.65 2
7 2007 $47.00 2
8 2005 $51.09 3
9 2006 $53.44 3
10 2007 $55.14 3

WAID Title Year CLIN
1 Test1 2005 5010
2 Processes 2007 5010
3 Testing 2005 6107
4 Management 2006 6107
5 Supervision 2007 6119

Resulting query should have all five of the WAID records with Rates of $71.05, $74.88, $44.15, $46.65 and $55.14 respectively. The applicable rates based upon the CLIN number and Year.

 

[GingerR]

Hi--With your tables now normalized, it's pretty straight-forward. I made the names of your tables: tblClin, tblRates and tblWA.

SELECT tblWA.*, tblRates.Rate
FROM (tblWA INNER JOIN tblClin ON tblWA.Clin = tblClin.Clin) INNER JOIN tblRates ON (tblWA.Year = tblRates.Year) AND (tblClin.ClinID = tblRates.ClinID);


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[nastar1]

Ginger, we are getting very close with the JOINs.

I am only getting 4 of the 28 total records from the tblWA table. In my query I need to pull all of the tblWA records and only the tblCLIN records that match the CLIN field in both tables. As well I need only the Rates included from the tblRates where the Year and CLIN fields match with the tblCLIN table.

I'm thinking I need a LEFT OUTER Join, but am struggling with the syntax if I still need an INNER JOIN between the tblRates and tblCLIN tables.

I need all 28 WA records with the appropriate rates based upon Year and CLIN.

Thanks

 

[nastar1]

GingerR,

Disregard. Your INNER JOIN works fine.

I do not understand why, but with my 28 'test' records, the Query was only pulling 4 of the 28 records.

After I decided to delete all the test records and I imported 167 actual records, the query worked perfectly.

There must have been some incompatibility between columns in the tables involved that were cleared up with the actual records.

Thanks for the big help with the INNER JOIN solution.

Regards