PDA

View Full Version : Design, Relationships and Queries



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.

babsc01
09-10-2004, 07:37 AM
Hi...I've fixed a couple things that may help. First, check out the new query called MAIN. I simply changed a couple of the relationships from ONE-ONE to ONE-MANY. Then, just changed the CAPTIONS (found under PROPERTIES). Finally, I created a pivot-table FORM which should hopefully give you the summary you were looking for.

Daniel Klann
09-10-2004, 08:45 AM
Hi Shades,

I'd also suggest dumping the idea of prefixing your table names with "tbl". Unfortunately this seems to be something that is recommended by many books and/or Access courses but I believe it should be discouraged. Look at any professionally designed database and you'll never see that convention used - it makes SQL statements very unpleasant to read.

Regards,
Daniel

shades
09-10-2004, 10:55 AM
Hi Shades,

I'd also suggest dumping the idea of prefixing your table names with "tbl". Unfortunately this seems to be something that is recommended by many books and/or Access courses but I believe it should be discouraged. Look at any professionally designed database and you'll never see that convention used - it makes SQL statements very unpleasant to read.

Regards,
DanielThanks, Daniel. This gets to be confusing. While I work for a Fortune 100 company as an analyst, I never see or interact with any "professional databases". And most of the IT people that maintain and use these (Oracle, etc.) express almost a distain for anyone using "Access". Of course, we don't have any choice but use Access. Therefore, my learning has been from a couple of books ("Database Design for Mere Mortals" and "MS Access 2003 Inside and Out"), asking a couple of Access developers/users when I first started, and much trial and error. All of the advice I had read, heard, seen, etc. was to distinguish tables (tbl), query (qry), forms (frm), and macros (mcr) using these abbreviations at the front of the name.

Not having dealt with SQL (other than to look a few times at my queries in Access), what is it that makes it harder to read?

What convention do you recommend? All caps for TABLES? What about queries, forms, and macros?

babsc01
09-10-2004, 11:06 AM
Shades/Daniel...although I do agree with keeping the names of your tables/queries simple as possible, there are times when you DO need the ability to tell a query from a table, and so forth. For instance, the design wizards in Access sometimes let you select a BOTH option for using queries and tables to design something new. However, Access provides them in a list together. If you have tables and queries identically named (a big NO-NO in my opinion), you have to tell them apart somehow.

That's when prefixing with "qry" or "tbl" or "frm" comes in handy.

shades
09-10-2004, 11:11 AM
Hi...I've fixed a couple things that may help. First, check out the new query called MAIN. I simply changed a couple of the relationships from ONE-ONE to ONE-MANY. Then, just changed the CAPTIONS (found under PROPERTIES). Finally, I created a pivot-table FORM which should hopefully give you the summary you were looking for.Thanks for looking at this.

On this attachment I have included the tblFINAL to show what I want to end up with. To get there using my way of thinking, I used the intermediate table to draw them together, then append that data to tblFINAL. Obviously tblINTERMEDIATE will be deleted each time after the data has been appended to tblFINAL. (Since this was done, I also put all queries into one macro to finish the work in one step.)

Then I will use tblFINAL for all my further queries (i.e., by media type, by qtr, by year, summaries for each city, each company - see the last query as one example in this sample). Thus, I will have many such queries to feed forms for online search or reports for printed reports.

BTW, noone else will have access to this part of the data base. If anyone does online search, I will have a switchboard set up for them to use.

Now, I will see whether and how the changed relationships help/change what I have done.

shades
09-10-2004, 01:25 PM
babsc01, I have changed the relationships, although it doesn't affect the results (which in this case is good!). I suspect that later on, such details will prove even more beneficial.

Thanks again.