Access Database Design (1 Viewer)

chineloogbonna

Registered User.
Local time
Today, 00:10
Joined
Jul 30, 2018
Messages
65
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

I’m here to help
Staff member
Local time
Yesterday, 23:10
Joined
Oct 29, 2018
Messages
21,322
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

Wino Moderator
Staff member
Local time
Yesterday, 23:10
Joined
Aug 30, 2003
Messages
36,115
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

Registered User.
Local time
Today, 00:10
Joined
Jul 30, 2018
Messages
65
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.:eek:
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:10
Joined
Oct 29, 2018
Messages
21,322
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.:eek:
Well, since Paul brought it up, do you also have a specific form and/or report for each of those queries?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:10
Joined
Oct 29, 2018
Messages
21,322
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

Registered User.
Local time
Today, 00:10
Joined
Jul 30, 2018
Messages
65
No. I have something like..

Forms
frmPickUpList
frmLogMyWork
frmAllProjects

Queries
qryProjectAssignee
qryProjectList_Distinct
qryProjectStatus
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:10
Joined
Oct 29, 2018
Messages
21,322
No. I have something like..

Forms
frmPickUpList
frmLogMyWork
frmAllProjects

Queries
qryProjectAssignee
qryProjectList_Distinct
qryProjectStatus
Okay. Sounds good.
 

Mark_

Longboard on the internet
Local time
Yesterday, 23:10
Joined
Sep 12, 2017
Messages
2,111
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.
 

Users who are viewing this thread

Top Bottom