Hi Tom,
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
__________________________________________