Referring to multiple tables in a query

[Tony]

Hi All,

I need to relate rate tables to work records where the rate tables are
different for each client each year. I want to store the appropriate table
name in the work record and have a query that can look at the record and use
the appropriate rate table based on the table name stored in the work
record. For example, the work records would look like this:

workRecord1 2 hours rateTable1
workRecord2 3 hours rateTable1
workRecord3 4 hours rateTable2
etc...

The rate tables would look like this:

rateTable1
ID1 $50
etc...


rateTable2
ID1 $100
etc...

Then the query would return something like this:

workRecord1 2 hours $50 $100
workRecord1 3 hours $50 $150
workRecord1 4 hours $100 $400
etc...

Any ideas?

Thanks & Ciao,

Tony

 

[Les]

Hi Tony,
Think I emailed you earlier regarding this. My
suggestion (and I hope I'm not being too simplistic), is
to have 1 table for rates. Client/year being the key.
Instead of storing table name in work record, store
client/year. This way, you can join work record table to
rate table by client/year field(s).
If I'm way off base, could you possibly list fields in
both tables, so I could better understand?

 

[John Vinson]

I need to relate rate tables to work records where the rate tables are
different for each client each year. I want to store the appropriate table
name in the work record and have a query that can look at the record and use
the appropriate rate table based on the table name stored in the work
record.

I agree with Les. Storing data in tablenames is simply BAD DESIGN, and
will get you into no end of trouble.

Use *one* table for rates; its primary key could consist of two
fields, the ClientID and the year.

 

[Tony]

Hey Les,

I looked at doing it this way based on your earlier suggestion and think it
may work, but decided not to go that route because I would need ~ 150
columns, and that seems excessive. Also, I would need to import values for
the rate table from Excel and have never done so with (in my opinion) the
axis switched from normal.

Any thoughts on having this many columns? I guess I'm looking at keeping a
separate rate table for each iteration of rates because that's the way we do
things now.

Again, thanks for the suggestions.

Tony

 

[Tony]

Hi, John.

Thanks for your response. I'll pose the same question to you that I did to
Les: should I be concerned about having a table with 150+ columns? There
are quite a variety of rates, each of which also has an overtime and
doubletime component.

Either way, I think I'm getting my ship pointed in the correct direction due
to the feedback you and Les have provided.

Thanks & Ciao,

Tony

 

[Les]

Tony,
I am curious as to why you would have more columns and
need to import from Excel for my suggestion. What
information is in the excel spreadsheet? What data would
be housed in the 150 columns?

I am envisioning creating your tables something like:

Rate Table
1)client id
2)year
3)ratevalue1
4)ratevalue2
etc.

Work rec table
1) client id
2) year
3) hours worked

I get the impression your tables would be like:

Ratetable1
1) client id
2) ratevalue1
3) ratevalue2
etc.

Ratetable2
1) client id
2) ratevalue1
3) ratevalue2
etc.

Work rec table
1) client id
2) hours worked
3) tablename

 

[Tony]

Les,

Perhaps I misunderstood your suggestion. I need that many columns because there are that many rates that might applied. So, I thought you meant that the rates table should look like this:

CLIENT YEAR RATE1 RATE2 RATE3 RATE4...
Client1 2004 10 20 30 40
Client2 2004 15 25 35 45
Client1 2005 20 30 40 50
Client2 2005 25 35 45 55

In the above example, I would have RATE1 - RATE150+, hence the need for that many columns. As far as importing, Excel is the *authoritative* residence of the rates.

Did I miss something in translation?

 

[Les]

Tony,
That is what I suggested for your rates table. How do
you have your rate tables set up now? Or how were you
going to set them up? >-----Original Message-----

Les,

Perhaps I misunderstood your suggestion. I need that

many columns because there are that many rates that might
applied. So, I thought you meant that the rates table
should look like this:

CLIENT YEAR RATE1 RATE2 RATE3 RATE4...
Client1 2004 10 20 30 40
Client2 2004 15 25 35 45
Client1 2005 20 30 40 50
Client2 2005 25 35 45 55

In the above example, I would have RATE1 - RATE150+,

hence the need for that many columns. As far as
importing, Excel is the *authoritative* residence of the
rates.

Did I miss something in translation?



"Les" <[email protected]> wrote in

message news:[email protected]...

 

[John Vinson]

Hi, John.

Thanks for your response. I'll pose the same question to you that I did to
Les: should I be concerned about having a table with 150+ columns? There
are quite a variety of rates, each of which also has an overtime and
doubletime component.

You need one table with FIVE columns, not 150:

ClientID
Year
BaseRate
OvertimeRate
DoubletimeRate

or perhaps you will only need four:

ClientID
Year
RateType (e.g. Base, Overtime, Doubletime, Holiday, ...)
Rate

"Fields are expensive, records are cheap". If a Rate is a valid
Entity, which I think it is, you should have one table for all your
rates.

 

[Tony]

Les,

I don't have anything set up as of yet, and it appears that I didn't
misunderstand. John's posts seem to indicate that I don't need so many
columns, but it would appear to me that I do, since there are multiple
rates.

I'm going to give this a go and see what happens...

Again, thanks for the assistance.

Tony

 

[Tony]

Hi, John.

If I'm reading your posts and those of Les correctly, it looks like you're
both pointing me in the same direction. However, when I look at what you've
suggested, I don't see the ability to store multiple rates, only one rate
with its overtime and doubletime variations. From what you posted, it looks
like I could accommodate one rate (base, overtime, and doubletime) per
client. What I need is several rates (one for each type of work) with their
corresponding overtime and doubletime variations.

Does that make sense or I am still looking at this incorrectly?

Tony

 

[John Vinson]

Hi, John.

If I'm reading your posts and those of Les correctly, it looks like you're
both pointing me in the same direction. However, when I look at what you've
suggested, I don't see the ability to store multiple rates, only one rate
with its overtime and doubletime variations. From what you posted, it looks
like I could accommodate one rate (base, overtime, and doubletime) per
client. What I need is several rates (one for each type of work) with their
corresponding overtime and doubletime variations.

Does that make sense or I am still looking at this incorrectly?

You are still looking at this incorrectly.

Consider the following ClientRates table records:

ClientID 123
Year 2001
Rate $10.50
Overtime $15.75
Double $21

ClientID 123
Year 2002
Rate $11.00
Overtime $16.50
Double $22

ClientID 123
Year 2003
Rate $12.00
Overtime $18.00
Double $23.00

ClientID 123
Year 2004
Rate $12.00
Overtime $18.00
Double $24.00

Four years. Four values for each rate.

If you normalize further and use the Ratetype field as suggested,
you'ld have 12 records instead of 4 but the same principle would
apply; you'ld simply use a query which selects the appropriate rate
for this client, this year, and this ratetype. You may also need a
Worktype field (which you haven't mentioned previously), but that's
just a fourth element to the multifield primary key; you're allowed up
to ten so that's no problem.

 

[Les]

Hey Tony,
I think your last post to John just listed the the
key. John's layout will work, if you add type of work
right after year. Then, you only have about 6 columns,
but any number of rows.
Hope this helps.

Good luck.

 

[Tony]

John,

Thanks again for the reply. I guess the part I'm missing is how to handle
the various rates for each client/year, and perhaps I haven't been clear.
Your example is very close to what I'm looking for but it appears (to me
anyway) that one piece is missing: multiple rates (above & beyond overtime
and doubletime) for each client. For example, we may provide the client
with 3 services: A, B, & C.

Service A has a rate of Rate1
Service B has a rate of Rate2
Service C has a rate of Rate3

This is what I think things should look like:

ClientID 123
Year 2001
Rate1 $10.00
OTRate1 $15.00
DTRate1 $20.00

ClientID 123
Year 2001
Rate2 $20.00
OTRate2 $30.00
DTRate2 $40.00

ClientID 123
Year 2001
Rate3 $30.00
OTRate3 $45.00
DTRate3 $60.00

Etc...

If I'm reading your example correctly, I have 3 rates/client/year - base,
overtime, and doubletime - based on the only one service provided to the
client. However, we provide many services so it turns out to be 3
rates/service/client/year.

Make sense & I am still missing something? It seems like things would work
if I used lot's of columns, but your quote about columns being expensive and
records cheap makes sense.

Going home for the evening, but will pick up the thread on Monday. Again,
thanks a million for the assistance.

Tony

 

[Tony]

Hi Les,

Looks like I'm getting closer and I'll have to read that more closely. But,
time to go home & drink some beer.

Cheers & thanks for the help. If you have any more suggestions, I'll be
back here on Monday.

Tony

 

[Albert D. Kallal]

Gee, I even hate to jump in this thread when you got John V helping you!

As always with normalizing, you remove the repeating data fields.

However, my comments are mixed in below:

So:

Service A has a rate of Rate1
Service B has a rate of Rate2
Service C has a rate of Rate3

This is what I think things should look like:

ClientID 123
Year 2001 RateType Rate1
Rate $10.00
OTRate $15.00
DTRate $20.00

ClientID 123
Year 2001 RateType Rate2
Rate $20.00
OTRate $30.00
DTRate $40.00

ClientID 123
Year 2001 RateType Rate3
Rate $30.00
OTRate $45.00
DTRate $60.00

Etc...

Of course, this means you need a nice table with the fields of:

RateId RateType Rate OTRate DTRate Year
1 Rate1 $10 $15 $20 2001
2 Rate2 $20 $30 $40 2001
etc

And, of course you now can add a nice "description" to the above rate table
if you want.

Further, if you DO NOT need to modify the rates, then you can dump the need
to "repeat" or copy the rate data (OT, DT, year) from the above new rate
table. There for, the above examples become

ClientID 123 RateId 1

ClientID 123 RateId 2

ClientID 123

RateId 3

Anytime you need the additional fields of

Rate $30.00
OTRate $45.00
DTRate $60.00

You can grab those extra fields via a relational join. IF YOU NEED TO modify
the rates AFTER selecting a rate plan, then it only takes 3 lines of code to
copy those actual rates from the rate table into the OT,DT, and Year field.
As mentioned, you ONLY need to "copy" this data IF you need to be able to
"modify" those rates to different values from the rate table. So, the
question of adding the DT,OT fields to the above is decided on the issue of
the values needing to be modified after being selected. (but, as you can
see, just storing the id is all you need!).

 

[Tony]

Les, John, & Albert,

Took a while for me to understand, but I think I got it now. I was fixated
on limiting the number of records but thought about what I was storing and
the reality of how long it would take to approach any maximum limits in
Access. Duh...

Thanks a million for the help; I can move forward with the project and I've
learned a few things in the process.

Thanks & Ciao,

Tony

 

[John Vinson]

Took a while for me to understand, but I think I got it now. I was fixated
on limiting the number of records but thought about what I was storing and
the reality of how long it would take to approach any maximum limits in
Access. Duh...

<g> I was coming to that realization and was about to point out that 2
GByte can hold a LOT of records...

Glad you got it straightened out!

 

[Tony]

John, again, a million thanks. I was getting frustrated to the point where
I was dreaming about it. I've worked on this today and so far, so good.

Thanks,

Tony