Access Database Design

[chineloogbonna]

Good Afternoon Access Experts,
I was wonder if you guys wouldn't mind passing on some Database creation tips.

I have a database that has 25 forms, 15 linked tables, and 60 queries. Does this sound like an excessive amount of queries?

I have so many queries, sometimes I forget what I used certain ones for. Of course, generally I can check the design and figure it out.

However, wondering if anyone has any tips or tricks of the trade that can help with form and/or query organization?

Thanks for any help you can provide!

 

[theDBguy]

Hi. For me, it's all a matter of personal preference. I always say go with what is comfortable with you. If you think you need to create all those queries to make it work, then you should do it - until you had a lot of time using the database and found a better way to do what you want. I don't think there is a hard number for the amount of objects required between a good and a bad database design. Just my 2 cents...

 

[pbaldy]

If you're using hard coded criteria and have queries like "SalesDataForJoe", "SalesDataForMary", it's too many, you should be using a single parameter query with a criteria for the salesperson. That said, the app needs what it needs. One app may need 2 queries, another might need 200. If they serve unique purposes, it's probably an appropriate number.

 

[chineloogbonna]

Thanks guys! That was helpful! This is my first large database creation and I was starting to get worried I too many queries. But yes, they all do serve a purpose and are single parameter queries.

 

[theDBguy]

Thanks guys! That was helpful! This is my first large database creation and I was starting to get worried I too many queries. But yes, they all do serve a purpose and are single parameter queries.

Well, since Paul brought it up, do you also have a specific form and/or report for each of those queries?

 

[chineloogbonna]

Yes, some forms have multiple queries.

 

[theDBguy]

Yes, some forms have multiple queries.

Hi. Not quite what I meant. For example, let's say you have the following three queries:

qryCustomerPaul
SELECT * FROM Customers WHERE CustomerName='Paul'

qryCustomerJohn
SELECT * FROM Customers WHERE CustomerName='John'

qryCustomerJane
SELECT * FROM Customers WHERE CustomerName='Jane'

I was wondering if you might three forms named something like:
frmCustomerPaul
frmCustomerJohn
frmCustomerJane

And perhaps also three reports such as:
rptCustomerPaul
rptCustomerJohn
rptCustomerJane

Does that make sense?

 

[chineloogbonna]

No. I have something like..

Forms
frmPickUpList
frmLogMyWork
frmAllProjects

Queries
qryProjectAssignee
qryProjectList_Distinct
qryProjectStatus

 

[theDBguy]

No. I have something like..

Forms
frmPickUpList
frmLogMyWork
frmAllProjects

Queries
qryProjectAssignee
qryProjectList_Distinct
qryProjectStatus

Okay. Sounds good.

 

[chineloogbonna]

Thanks for checking!

 

[theDBguy]

Thanks for checking!

That's out job. Good luck with your project.

 

[Mark_]

Rule of thumb I use is "Can I clearly identify what each object is used for". If you find you have objects that you can't tell what they are for/if used, you should start verifying what is or isn't needed any more. This often happens when your working on a system that has been in use for a while, but business needs mean some pieces are no longer required.

For what you have posted, I'd normally be looking to the queries first. You also haven't listed how many reports you have. You will often find one or more queries are needed for a given report/form.