Finding values in a table

[Mike20255]

I have a table of classes with different rates (%'s) so first column shows codes (DG, FG, RG) 2nd column is C-rate (.05%,.4%,1.2%) then D-Rate (.04%,.5%,0.9%)

I have another table that has a bunch of items with a code beside them. I want a query to look at what code is beside the item and have it multiply a value against the C-rate based on that code.

Table 1
Dog  DG  $500
Cat  FG   $325

Table 2 (rates)
Code C-Rate D-Rate E-Rate
DG   .05%   .4%   1.2%
RG    .6%   1.3%   2.4%
FG   .04%   .5%   .9%

Query would show:

                      NEW COLUMNS             SUM
Dog DG   $500       $.25   $2   $6             8.25
Cat  FG   $325      $.13   $1.63   $2.93     4.69

How can I do this?

Thanks,
Mike

 

[rainman89]

use a dlookup function then multiply them together

 

[Mike20255]

I can't figure out how to uses this function I am using Access 97 can you give me an example based on the data above.

Thanks!!

 

[rainman89]

are you doing this on a form or in a query?

 

[Mike20255]

in a query

 

[Mike20255]

I guess it should be something like this but I still can't get it to work:

Dlookup "[Class]","Table1" or should this be table two I don't understand how you connect the two columns so it refers to the proper column in the other table?

 

[rainman89]

if you are doing this in a query, why not include both tables in it and just make another column called

sum: [c-rate] * [amount]

?

 

[Mike20255]

The C-rate is dependent on teh class so if the Cat is the FG class how would I put the associated c-rate in another column beside that...i don't know how to do that either thats why I was using d-lookup?

 

[rainman89]

=dlookup("c-rate", "table2", "Class = 'me.class'")
is how you would get the rate based on the class you are looking for. you would have to change the me.class to whatever it is you are basing the class on

 

[Mike20255]

Okay its these relationships that I dont' understand, I see what your saying once you create a relationship to the codes between the two tables. But how do you know when to use what type of relationship and what does "Enforce Referential Integrity" mean?

Thanks!

 

[Mike20255]

Why wouldn't the query right align this: City Wide $: Format([Assessment]*[City Wide],"Currency")

If it is formatted as Currency should it not be aligned to the right?

Thanks again

 

[Mike20255]

Does anyone know why it doesn't align to the right when using the format function?

 

[boblarson]

Why wouldn't the query right align this: City Wide $: Format([Assessment]*[City Wide],"Currency")

If it is formatted as Currency should it not be aligned to the right?

Thanks again

Can only think of one possible reason myself - get rid of the $ in the name of the field. You shouldn't use special characters that way but that is a reserved character for a string as well, so maybe it is interfering with your format.

 

[Mike20255]

I removed the $ character still doesn't format to the right, I am using Access 97 Is it possible using the Format function for currency in a query just doesn't have that feature? Or is there anything else you can thing of?

Thanks!

 

[boblarson]

It works for me, but I don't have A97, so I don't know - it may have to do with that.

 

[Simon_MT]

I maybe missing something but there doesn't seem any reason why there are two tables at all. DG and and FG are in both tables. I would be tempted unless there is variability affecting Table 1 to combine the two tables.

If you to keep the current structure you can do an Innner Join between the Table1 and Table 2. I'm using [Value] for the amount in Table1, then you simply:

C_Amount: [Value] * [C-Rate]
D_Amount: [Value] * [D-Rate]
E_Amount: [Value] * [E_Rate]

Total Amount: Value + C_Amount + D_Amount + E_Amount

or

Total Amount: Value + ([Value] * ([C-Rate] + [D-Rate] + [E-Rate]))

or

Total Amount: Value * (1 + ([C-Rate] + [D-Rate] + [E-Rate]))

Simon

 

[Mike20255]

If I use this:
City Wide: Format([Assessment]*[City WideR],"Currency") &
Cons Area: Format([Assessment]*[Cons AreaR],"Currency")

and try and sum those two columns by using this:
Expr1: [City Wide]+[Cons Area]

It says enter perimiter value for City Wide and Cons Area. I just say okay okay and don't enter values it will put the two numbers together as a string

If I remove formatting from the City Wide and Cons Area columns it adds but still gives me the enter perimiter value message??

What am I doing wrong? I am utilizing the totals function but even when I remove it, the same problems occur. If I try and use the Sum([City Wide]+[Cons Area]) it says I can't?

 

[boblarson]

By chance is either one's number data type in the table set to Integer or Long Integer instead of Single, Double, or Decimal?

 

[Mike20255]

By chance is either one's number data type in the table set to Integer or Long Integer instead of Single, Double, or Decimal?

Both are set to Double, and formatted as currency and percentage.

 

[Mike20255]

Samples of problems I am having:

City Wide: Format([Assessment]*[City WideR],"Currency")
- GROUP BY
Cons Area: Format([Assessment]*[Cons AreaR],"Currency")
- GROUP BY

Expr1: Format([city wide]+[cons area],"Currency")
- Expression

works without messages but puts both city wide and cons area together in a string

If I change Expr1 to "GROUP BY" then I get the enter perimiter values message

If I change cons area to not have a format so:
City Wide: Format([Assessment]*[City WideR],"Currency")
- GROUP BY
Cons Area: [Assessment]*[Cons AreaR]
- GROUP BY
Expr1: Format([city wide]+[cons area],"Currency")
- Expression

This works like it should only problem being the Cons Area isn't formatted otherwise it returns the desired result

Any ideas why this would be?