T
Tim Rogers
I'll try to make this as brief as humanly possible. I'm an access
newbie, so I apologize if my terms are not correct. I am at the point
in my access learning curve where I understand how I can create a form
and 'bind' it to a table or a query. I also understand that you can
use a subform, usually with a datasheet view, to display one-to-many
relationships on a form.
What I am trying to understand now is how to create a screen that can
somehow display multiple rows from a table, but not in a datasheet
view. Essentially what I have is a table that when issued a specific
query will return multiple rows by design. I need to be able to
display a particular column from each of the rows in that result set
in different text box controls. It might help if I give you a brief
description of my tables.
Table: Accounts (this table already exists - so I can't modify it)
Field: Account - Text - PK
Table: Categories (new table)
Field: Id - AutoNumber - PK
Field: Description - Text
Table: Ratings (new table)
Field: Id - AutoNumber - PK
Field: Account - Text - FK to Account field in Accounts table - combo
box lookup
Field: Category - Number - FK to Id field in Categories table - combo
box lookup
Field: Quarter - Date
Field: Rating - Number
This allows us to maintain ratings for various categories for each
account on a quarter-by-quarter basis.
So, what I want to create is a form that is displayed for each account
and has a row for each category name followed by four text box
controls. The text boxes from left to right will house Quarter 1 -
Quarter 4 (of the calendar year) data. So, (as an example) for
category "Is Customer Happy?" you would see the account's ratings for
Q1 2004, Q2 2003, Q3 2003, and Q4 20003 as of today (1/7/2004). This
is a 'rolling' set of ratings from the last 3 quarters and the current
quarter.
So, the form would display the date similar to the following:
2004 2003 2003 2003
Is Customer Happy? Q1: 2 Q2: 4 Q2: 3 Q4: 4
Customer Calls Frequently? Q1: 1 Q2: 3 Q3: 2 Q4: 5
....
I know I can figure out how to create a query that will return me the
four rows that I need for each category per account. I just don't
know what would be the best approach for loading the data onto the
form the way that I want. I should mention that number and names of
the categories are fixed. So, I will probably just hard code labels
on the form.
If what I am wanting to do is horribly complicated, another approach I
thought about taking was to change the Ratings table to have Q1, Q2,
Q3, and Q4 ratings all on one row. Then there would only be one row
for each category for each account. I could simply do a query to pull
back all rows that matched the account I'm currently dealing with. I
could put this in a datasheet view, and I believe I'd be done. The
problem with this is that it would destroy historical data. This may
not be acceptable.
I know there are several ways to approach this problem, I'm just
looking for some direction. Thanks if you had the patience to read
through all of this!
BTW, I'm using Access97
Thanks,
Tim Rogers
newbie, so I apologize if my terms are not correct. I am at the point
in my access learning curve where I understand how I can create a form
and 'bind' it to a table or a query. I also understand that you can
use a subform, usually with a datasheet view, to display one-to-many
relationships on a form.
What I am trying to understand now is how to create a screen that can
somehow display multiple rows from a table, but not in a datasheet
view. Essentially what I have is a table that when issued a specific
query will return multiple rows by design. I need to be able to
display a particular column from each of the rows in that result set
in different text box controls. It might help if I give you a brief
description of my tables.
Table: Accounts (this table already exists - so I can't modify it)
Field: Account - Text - PK
Table: Categories (new table)
Field: Id - AutoNumber - PK
Field: Description - Text
Table: Ratings (new table)
Field: Id - AutoNumber - PK
Field: Account - Text - FK to Account field in Accounts table - combo
box lookup
Field: Category - Number - FK to Id field in Categories table - combo
box lookup
Field: Quarter - Date
Field: Rating - Number
This allows us to maintain ratings for various categories for each
account on a quarter-by-quarter basis.
So, what I want to create is a form that is displayed for each account
and has a row for each category name followed by four text box
controls. The text boxes from left to right will house Quarter 1 -
Quarter 4 (of the calendar year) data. So, (as an example) for
category "Is Customer Happy?" you would see the account's ratings for
Q1 2004, Q2 2003, Q3 2003, and Q4 20003 as of today (1/7/2004). This
is a 'rolling' set of ratings from the last 3 quarters and the current
quarter.
So, the form would display the date similar to the following:
2004 2003 2003 2003
Is Customer Happy? Q1: 2 Q2: 4 Q2: 3 Q4: 4
Customer Calls Frequently? Q1: 1 Q2: 3 Q3: 2 Q4: 5
....
I know I can figure out how to create a query that will return me the
four rows that I need for each category per account. I just don't
know what would be the best approach for loading the data onto the
form the way that I want. I should mention that number and names of
the categories are fixed. So, I will probably just hard code labels
on the form.
If what I am wanting to do is horribly complicated, another approach I
thought about taking was to change the Ratings table to have Q1, Q2,
Q3, and Q4 ratings all on one row. Then there would only be one row
for each category for each account. I could simply do a query to pull
back all rows that matched the account I'm currently dealing with. I
could put this in a datasheet view, and I believe I'd be done. The
problem with this is that it would destroy historical data. This may
not be acceptable.
I know there are several ways to approach this problem, I'm just
looking for some direction. Thanks if you had the patience to read
through all of this!
BTW, I'm using Access97
Thanks,
Tim Rogers