R
Rosemary
Hello,
I am designing a new, fairly simple, MS Access database for a practice group
of 10 users. We have Windows XP and MS Office 2003 in a network environment.
Next year, we are upgrading to Vista.
I am creating the DB based on an Excel spreadsheet that has become
cumbersome to revise, and would be more efficient in Access.
I am stumped on one question on how to make the report in Access match the
format of the one now in Excel.
The question has to do with one-to-many relationships.
The database is about Companies. Each company will have records
illustrating its retirement plans. Some companies have one, some more than
one. The Excel spreadsheet has 12 columns, which break down information
about the Company's retirement plans.
The one-to-many relationships are not the same for each company. For
example, for one company, there may be 3 retirement plans, and only one
enrollment date for all 3 plans. Another company may have 3 retirement
plans, but 1 with one enrollment date, and the other 2 with a different
enrollment date, etc.
I can easily create the tables with this criteria - just input the
enrollment date for each plan in its individual record.
The problem comes in the format of the report. The client does not want the
same enrollment date to appear more than once for a company. For example
(difficult to illustrate formatting here, but I will try) :
COMPANY RETIREMENT PLAN ENROLLMENT DATE
Company A Ret Plan A |
Ret Plan B | January 1st (date
same for all 3)
Ret Plan C |
Company B Ret Plan A January 1st
Ret Plan B December 31st
Company C Ret Plan A |
Ret Plan B | January 1st (same for
first 2)
Ret Plan C | December 31st (same
for second 2)
Ret Plan D |
We accomplish this format in Excel by merging and splitting cells. Can I
get this same format for my report in Access?
Please let me know if further information is needed for this question.
Many thanks,
I am designing a new, fairly simple, MS Access database for a practice group
of 10 users. We have Windows XP and MS Office 2003 in a network environment.
Next year, we are upgrading to Vista.
I am creating the DB based on an Excel spreadsheet that has become
cumbersome to revise, and would be more efficient in Access.
I am stumped on one question on how to make the report in Access match the
format of the one now in Excel.
The question has to do with one-to-many relationships.
The database is about Companies. Each company will have records
illustrating its retirement plans. Some companies have one, some more than
one. The Excel spreadsheet has 12 columns, which break down information
about the Company's retirement plans.
The one-to-many relationships are not the same for each company. For
example, for one company, there may be 3 retirement plans, and only one
enrollment date for all 3 plans. Another company may have 3 retirement
plans, but 1 with one enrollment date, and the other 2 with a different
enrollment date, etc.
I can easily create the tables with this criteria - just input the
enrollment date for each plan in its individual record.
The problem comes in the format of the report. The client does not want the
same enrollment date to appear more than once for a company. For example
(difficult to illustrate formatting here, but I will try) :
COMPANY RETIREMENT PLAN ENROLLMENT DATE
Company A Ret Plan A |
Ret Plan B | January 1st (date
same for all 3)
Ret Plan C |
Company B Ret Plan A January 1st
Ret Plan B December 31st
Company C Ret Plan A |
Ret Plan B | January 1st (same for
first 2)
Ret Plan C | December 31st (same
for second 2)
Ret Plan D |
We accomplish this format in Excel by merging and splitting cells. Can I
get this same format for my report in Access?
Please let me know if further information is needed for this question.
Many thanks,