dB design

g28dman

Registered User.
Local time
Today, 07:05
Joined
Nov 4, 2005
Messages
84
3 years ago I started on a access project. Throughout that time my dB has grown from 3 tables for one job to over 30 for multiple, because I saw the "power" of how I could track anything I wanted and built it to work for my business.

I can honestly say that I think it's normilized to the best of my ability, and not entering data twice when it can be done once.

However one thing I just dont understand - I have alot of queries. It just seems like I have to many, everytime I want to create a new report I build a query for it.

Is this wrong design? I have seen that you can have multiple criteria on queries. Can I use the the same queries multiple times?
 
What I will typically do, is to build a base query off of almost all of my other queries are built. Also, I will build a generic report with a generic query and when I use code to open the report I use the WHERE clause argument of the DoCmd.OpenReport code to limit the data.

This method is not the best if you are not on a LAN, or using SQL Server as a backend, but it does work well in the more normal situations.
 
Yes, you can use the same query for as many reports as you want. :)
 
queries tend to proleferate a bit i think - sometimes you get so many, you lose track of what youve done, with slight variations of similar queries, and end up duplicating some of them - i dont think the queries add too much in terms of size to the dbs, so its not too much of an issue.

i prefer to use stored queries for combo boxes, to reuse them, and this adds to the number of queries - but its probably more efficient than having the sql repeated in each combo box, and maybe easier to maintain as well.
 
Thanks, for the responses. I have some reports and queries where I use combobox and options to show/hide data that I want...The funny thing is when I first started that same report involved 20 reports and 20 queries. Using that technique I cleaned up quite abit.

It just seems like I am using queries alot, and yes I probably have duplicated many of them. I am getting lost in this thing, and find working on it a stress reliever until I hit a problem - then it becomes a duel between mind over matter between ms access and me.
 
Several options to help manage your objects:

a. Name them so that a sort will put related objects together when you sort them
b. Use object property descriptions to do the same as above except with the desription column.
c. Hide objects you are not working with
d. Use object groups
 

Users who are viewing this thread

Back
Top Bottom