shades
09-09-2004, 11:56 AM
Howdy. I have been working sporadically with MS Access (2002) for about three years. I have set up a database that does what I need. However, it is rather inefficient (I have about 10 tables of intermediate steps that don't need to be there), and it takes many steps to get the end result. I have to write out the steps so I can remember (I use the database only about 4 days out of the quarter). I managed to set this up original by trial and error (much error!). But the longer I go, the more the bad design is showing.
Design
Anyway, I am in the process of redoing it from scratch. The attachment shows some dummy data. In this sample there are three tables:
1. tblCITIES: (I work with more than 100 in the real one.) The sample has three cities.
2. tblCOMPETITORS: (I work with about 300 in the real db.) The sample db has five companies, each of which may have components: i.e. CompA will have A1, A2, A3, A4 as sub-companies, but for reporting purposes they are all considered CompA. "Merger" designates CompA, CompB, etc.
3. tblIMPORT: There are seven different Media types (MediaAAAA, MediaBBBB, MediaCCCC, MediaDDDD, MediaEEEE, MediaFFFF, MediaGGGG). This is the main table. I import data from different sources, each has a dollar amount associated with each Media type. Each sources would have: City, Competitor, Year, Qtr, MediaType, and Spend.
Do I need to include a separate table listing the Media types? Do I need tables for Year and Quarter?
Relationships
Are these relationship sufficient? Do I need something for Year and Quarter?
Queries
I have queries that provide a summary of data for each Media type by city and merger. In the future development, there will be additional quarters, and additional years, so it would be by Year and by Qtr.
However, I would like to have a summary query that would provide the data in this form:
City...Merger...Year...Qtr...MediaAAAA...MediaBBBB...MediaCCCC...MediaDDDD. ..MediaEEEE...MediaFFFF...MediaGGGG
However, I have not been able to get the query built properly.
Any ideas on how to do this? Should I set up a table, and populate the table based on each of the individual Media type queries?
Any help would be appreciated.
Design
Anyway, I am in the process of redoing it from scratch. The attachment shows some dummy data. In this sample there are three tables:
1. tblCITIES: (I work with more than 100 in the real one.) The sample has three cities.
2. tblCOMPETITORS: (I work with about 300 in the real db.) The sample db has five companies, each of which may have components: i.e. CompA will have A1, A2, A3, A4 as sub-companies, but for reporting purposes they are all considered CompA. "Merger" designates CompA, CompB, etc.
3. tblIMPORT: There are seven different Media types (MediaAAAA, MediaBBBB, MediaCCCC, MediaDDDD, MediaEEEE, MediaFFFF, MediaGGGG). This is the main table. I import data from different sources, each has a dollar amount associated with each Media type. Each sources would have: City, Competitor, Year, Qtr, MediaType, and Spend.
Do I need to include a separate table listing the Media types? Do I need tables for Year and Quarter?
Relationships
Are these relationship sufficient? Do I need something for Year and Quarter?
Queries
I have queries that provide a summary of data for each Media type by city and merger. In the future development, there will be additional quarters, and additional years, so it would be by Year and by Qtr.
However, I would like to have a summary query that would provide the data in this form:
City...Merger...Year...Qtr...MediaAAAA...MediaBBBB...MediaCCCC...MediaDDDD. ..MediaEEEE...MediaFFFF...MediaGGGG
However, I have not been able to get the query built properly.
Any ideas on how to do this? Should I set up a table, and populate the table based on each of the individual Media type queries?
Any help would be appreciated.