lookup

T

tom

Hi.....
I have question about lookup in period, so pls help with many many tks.

I have one table call "Contract" inside have "Month" etc .......
now contract renew in every year, so rate is difference,
e.g. fm may2006 to Apr2007 = 1 and fm May2007 upto now is 2

So, how can I run report and it will refer to the difference period of rate ?

Tks U so much for yr help !

Tom
 
T

Tom Wickerath

Hi Tom,

I'm not sure what you mean by "...lookup in period..." and by "...refer to
the difference period of rate". Can you clarify?

Perhaps you are asking how to keep the initial rate at 1 in the report, and
rate for the next period at a new higher value? If this is your question,
take a look at the sample Northwind database. In Access 2003 and prior, click
on Tools | Relationships, to display the relationships window. Notice how two
tables have fields named "UnitPrice": Products and Order Details. The reason
for this is that the [Products].[UnitPrice] reflects the current price, while
the [Order Details].[UnitPrice] reflects the price at the time the sale was
made.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
T

tom

Tom Wickerath said:
Hi Tom,

I'm not sure what you mean by "...lookup in period..." and by "...refer to
the difference period of rate". Can you clarify?

Perhaps you are asking how to keep the initial rate at 1 in the report, and
rate for the next period at a new higher value? If this is your question,
take a look at the sample Northwind database. In Access 2003 and prior, click
on Tools | Relationships, to display the relationships window. Notice how two
tables have fields named "UnitPrice": Products and Order Details. The reason
for this is that the [Products].[UnitPrice] reflects the current price, while
the [Order Details].[UnitPrice] reflects the price at the time the sale was
made.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

tom said:
Hi.....
I have question about lookup in period, so pls help with many many tks.

I have one table call "Contract" inside have "Month" etc .......
now contract renew in every year, so rate is difference,
e.g. fm may2006 to Apr2007 = 1 and fm May2007 upto now is 2

So, how can I run report and it will refer to the difference period of rate ?

Tks U so much for yr help !

Tom
 
T

tom

tom said:
Tom Wickerath said:
Hi Tom,

I'm not sure what you mean by "...lookup in period..." and by "...refer to
the difference period of rate". Can you clarify?

Perhaps you are asking how to keep the initial rate at 1 in the report, and
rate for the next period at a new higher value? If this is your question,
take a look at the sample Northwind database. In Access 2003 and prior, click
on Tools | Relationships, to display the relationships window. Notice how two
tables have fields named "UnitPrice": Products and Order Details. The reason
for this is that the [Products].[UnitPrice] reflects the current price, while
the [Order Details].[UnitPrice] reflects the price at the time the sale was
made.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

tom said:
Hi.....
I have question about lookup in period, so pls help with many many tks.

I have one table call "Contract" inside have "Month" etc .......
now contract renew in every year, so rate is difference,
e.g. fm may2006 to Apr2007 = 1 and fm May2007 upto now is 2

So, how can I run report and it will refer to the difference period of rate ?

Tks U so much for yr help !

Tom
 
T

tom

Dear Tom Wickerath,

Frist of all, Tks U very much for yr information, but "Northwind" is not my
question.

Actually, I want the flwg "Vol Table - Month" refer to the "Rate Table" with
dtls as below:

Vol Table
Month Vol.
2006Jan 10
2006Feb 15
2006Mar 10
2006Apr 15
2007Jan 10
2007Nov 15

Rate Table
Period Rate
2006Jan ~ Mar 100
2006Apr upto now 110

Result vol. x rate
2006Jan 10 x 100
2006Feb 15 x 100
2006Mar 10 x 100
2006Apr 15 x 110
2007Jan 10 x 110
2007Nov 15 x 110

Above is possible ?

Tks Again

Rgds
Tom


tom said:
Tom Wickerath said:
Hi Tom,

I'm not sure what you mean by "...lookup in period..." and by "...refer to
the difference period of rate". Can you clarify?

Perhaps you are asking how to keep the initial rate at 1 in the report, and
rate for the next period at a new higher value? If this is your question,
take a look at the sample Northwind database. In Access 2003 and prior, click
on Tools | Relationships, to display the relationships window. Notice how two
tables have fields named "UnitPrice": Products and Order Details. The reason
for this is that the [Products].[UnitPrice] reflects the current price, while
the [Order Details].[UnitPrice] reflects the price at the time the sale was
made.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

tom said:
Hi.....
I have question about lookup in period, so pls help with many many tks.

I have one table call "Contract" inside have "Month" etc .......
now contract renew in every year, so rate is difference,
e.g. fm may2006 to Apr2007 = 1 and fm May2007 upto now is 2

So, how can I run report and it will refer to the difference period of rate ?

Tks U so much for yr help !

Tom
 
T

Tom Wickerath

Hi Tom,
Above is possible ?

Sure. You need to define a relationship between the Vol Table and the Rate
Table. This will require the addition of a third field to the Vol Table,
which serves as a foreign key, so that you can define a one-to-many (1:M)
relationship between the Rate table (one side) and the Vol Table (many side).
Just to clarify, in your Vol Table, you currently have two fields, correct?
Because, if you have a field for each period indicated, ie. 2006Jan, 2006Feb,
2006Mar, etc., then you have an incorrect multi-valued field design that
needs to be normalized. If all of this is sounding a bit foreign to you, I
suggest heading to this link to read up more on database design and
normalization:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

The first two papers by Michael Hernandez are excellent primers on this most
important subject.

Also, now would be a good time to adopt naming standards. For example, the
names you have indicated appear to include a space in the names of the tables

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

I also see some reserved words as field names (month and rate). You should
avoid using any reserved words for things that you assign a name to in Access:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

In general, it is best to calculate the results on the fly, as needed,
instead of attempting to store them in a table.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
T

Tom Wickerath

PS. I'd like to encourage you to avoid using shorthand abbreviations, such
as the two occurances in this sentence:

"Actually, I want the flwg "Vol Table - Month" refer to the "Rate Table" with
dtls as below:"

Now, I can guess that you mean "following" for "flwg", and "details" for
"dtls", but then again, I might guess wrong. And, anyone else, especially
from another country, who may be reading this thread and might want to
volunteer a reply, may be totally lost with the use of such abbreviations.

...but "Northwind" is not my question.

But, it actually demonstrates the answer to your question. Substitute Rate
with UnitPrice, and Vol Table with Order Details, and you'll see that you
come very close to what you are asking. Reports in Northwind can include a
calculated Total that includes the quantity sold (vol), and unitprice (rate)
at time of sale.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
 
T

tom

Dear Tom Wickerath,

First of all, Sorry for my poor english and the shorthand, because I'am
using these everyday in my work, I forgot here is not (HongKong) he he .....
Anyway, Thanks You very much. It's really good help for me in here.

Thanks & Regard
Tom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top