how many queries is too many queries?

[SirStevie3]

So my primary table consists of 6,670 records. i also have 7 other tables of a much smaller caliber.

I have 18 Forms that utilize my tables and queries to make certain tasks a tad more convenient.

I have 10 reports (so far) with more on the "to do" list.

I have about 84 different queries i use for various functions including updating records, creating records, and analyzing records.


Is there a point where having too many queries becomes a problem? or can i just keep going and creating more and more queries?

this is my first database...

 

[jdraw]

It is difficult to say that X is too many of anything without knowing more about your database and business. What is the subject of the database? Have you normalized your tables? Perhaps you could post a copy of your tables and relationships to help readers understand what you have.
Sometimes you can use a form to get parameters, and adjust code behind the scenes to create/adjust a skeleton query.
Here's a link to some related info (several useful tutorials at this site)
http://www.fontstuff.com/access/acctut17.htm

 

[plog]

Is there a point where having too many queries becomes a problem?

I think this is one of those questions that since you are asking it, it means you are already having difficulty with it. So let's jump to your next question.

Generally, action queries (INSERT, UPDATE, MAKE TABLE, DELETE) are a symptom of poor structure. They have their uses, but my experience is that they are used as band-aids for people who can't grasp normalization. How many action queries do you have and what are their purposes? I feel these can be eliminated.

As for your SELECT queries I'm certain you can consolidate them. For example, let's say you have 12 Sales Total Queries, one for each month. That's unnecessary. Instead you build one query, make the month it is for a field and then either put [Insert Month] in the criteria section under that month field in the query and it will display a prompt for you to enter the month you want. That kills 11 queries right there.

Look for similar queries like that. Maybe instead of months its Departments, or some other field used as criteria. Any query with the same fields (or a subset of fields) as another query but with different criteria can be consolidated.

 

[SirStevie3]

well... i can't upload the database because it's dealing with alot of sensitive data - i'm going to have to ask before i can upload it. I understand this is really the only way for you guys to know what im dealing with here...

I think this is one of those questions that since you are asking it, it means you are already having difficulty with it. So let's jump to your next question.

i do not have any problems at the moment, just concerned with how quickly my query list is growing. Also i know for a fact that things could be done more efficiently. i've been guessing and checking the whole way though it.

Have you normalized your tables?

what does normalizing tables mean?

I'll see what i can do about uploading the whole thing.

 

[plog]

Database Normalization (http://en.wikipedia.org/wiki/Database_normalization) is properly structuring your database, its not a button or option in Access you just click and it does it for you. Its the process of deciding what tables you need, where each field of data goes and how your tables relate to each other.

If you have relationships set up in your database you can do a screen print of that and post it--that will let us know the structure of your tables and how they relate without disclosing the data they hold.

 

[SirStevie3]

here's a pic of the relationships... its probably very unorthodox at best...

thanks so much for your guys' help and advice and patience so far! it is very much appreciated!! I'm here to listen to the experts and learn!

 

[jdraw]

You would probably benefit greatly from working through this tutorial.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[plog]

Just looking at that I don't see anything too horrible. Data would be more revealing, but I understand. Here's a few notes:

~Generally tables shouldn't have just one field of real data.

~Requesters should have an autonumber ID field and that shoud be used to link to Request Details, not the Last_Name field.

What are your action queries (UPDATE, DELETE, etc.) doing and why?

 

[SirStevie3]

You would probably benefit greatly from working through this tutorial.
http://www.rogersaccesslibrary.com/T...lationship.zip

thanks for this i'm looking at it right now. will read through the entire thing.

~Generally tables shouldn't have just one field of real data.

The only reason i have those tables with one field of data is because i didnt know how else to create a dropdown list when i first started designing this DB. i figured out how to like a combo box to a table, so i figured i would just create tables for things i would need in combo boxes.

~Requesters should have an autonumber ID field and that shoud be used to link to Request Details, not the Last_Name field.

not that i disagree with you, i just like to understand the logic behind statements like this - when first learning about PK's the way i understood it was that it didnt have to be an auto number, it just had to be a unique field of data that wont be repeated at all. would you mind enlightening me on the logic of why i would need an autonumber ID field for Requesters?

What are your action queries (UPDATE, DELETE, etc.) doing and why?

well i dont think they are actually true action queries - they dont do anything when run, they just present me with the data i need to see and i do the updating manually. so i guess they all qualify as either SELECT or UNION queries?

i tend to design forms with combo boxes that will determine the criteria of a given query, then a command button that runs the query. once the query is open i'll do whatever editing updating i need to do and close it.

 

[plog]

You're right, primary keys do not have to be autonumbers. Primary keys must be unique and immutable. Last names are neither unique (Smith, Jones, etc.) nor immutable (i.e. neither Muhammad Ali or Hillary Clinton were born with the last names they have now.). So, for that table since you don't have a good natural primary key (Social Security Number, employeeID), you should use an autonumber which ensures both.

Your answer to action queries makes it a little better, but how come the system doesn't know to automatically make the changes you manually make? For example, if Field A is less than 100 then field B needs to be "Tuesday", that is technically a calculated value and shouldn't be stored and instead generated in a query. I'm wondering if some of your changes aren't in fact calculations you can automate.

 

[SirStevie3]

So, for that table since you don't have a good natural primary key (Social Security Number, employeeID), you should use an autonumber which ensures both.

ahh i see what you're saying. in my case however, i may have to respectfully decline that suggestion. and i think when (if) im allowed to post my db file you'll see why.

there are only 10 or less people in the "requesters" table, and that will likely not change much. also, i'm the only one using this database, no one else has access to it at all.

I'm wondering if some of your changes aren't in fact calculations you can automate.

now THIS has me quite interested. im sure you are correct here. I'm going today to see if im allowed to share the DB (with peoples names changed of course...)