I need a professional to review the database (1 Viewer)

AHMEDRASHED

Member
Local time
Today, 07:10
Joined
Feb 2, 2020
Messages
50
Hello everyone, I'm looking for help .

I need a professional to review the database (codes and the relationships) to ensure it's reliable to avoid future issues. I'm not a professional
I'm willing to pay a small fee if it's allowed her .

Thank you
 

Attachments

  • 04-10-2023.zip
    1.2 MB · Views: 77

June7

AWF VIP
Local time
Yesterday, 20:10
Joined
Mar 9, 2014
Messages
5,503
Not a pro but will throw in 2 cents.

I am concerned about your table relationships as set up in Relationships builder. They may be circular (https://www.codeproject.com/articles/38655/prevent-circular-references-in-database-design) which could cause issues in queries if you don't adjust queries that automatically use these established links.
1696465080234.png


For instance, in first query I opened, qryBillStatement, tblBills is linked via two paths to tblSupplies.

1696464726832.png

Don't just "drag and drop" and stop. Identify links appropriate for desired output and eliminate the others. Perhaps query should have two instances of tblCurrencies, one linked to each CurrencyID foreign key if the foreign keys are likely to have different values. Otherwise, decide which CurrencyID foreign key you want and ignore other, don't link to it.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:10
Joined
Feb 28, 2001
Messages
27,488
I will amplify June7's comments. If you have two paths to the same table indirectly from the same starting point, you completely confuse the Access wizards that help you do JOIN queries involving tables on either side of the split path. DEFINITELY not a good thing.

You have what appears to be multi-path relationships with tblCustomers and tblSelling, and between tblBills and tblSuppliers. It is possible to overspecify relationships and I think you did that. I can't tell you which one is right because you are the subject-matter expert.
 

AHMEDRASHED

Member
Local time
Today, 07:10
Joined
Feb 2, 2020
Messages
50
@June7 & @The_Doc_Man Thanks for your advice! I've made the changes you suggested to my database. Your help means a lot 👏
I will amplify June7's comments. If you have two paths to the same table indirectly from the same starting point, you completely confuse the Access wizards that help you do JOIN queries involving tables on either side of the split path. DEFINITELY not a good thing.

You have what appears to be multi-path relationships with tblCustomers and tblSelling, and between tblBills and tblSuppliers. It is possible to overspecify relationships and I think you did that. I can't tell you which one is right because you are the subject-matter expert.


i updated it as below


1696501023522.png



For instance, in first query I opened, qryBillStatement, tblBills is linked via two paths to tblSupplies.
1696501287949.png


 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:10
Joined
Feb 28, 2001
Messages
27,488
OK, understand that I don't know the detailed intent of your DB, though I can guess. Therefore, I have no idea whether this is CORRECT. However, this structure is simplified a lot and is FAR less likely to lead to Access having internal confusion when you create queries.

If you were not aware of these ideas, may I respectfully suggest that you do a little reading on Database Normalization? In particular, for the kind of problem we saw in your first diagram, the key concepts would be for parent/child tables (also referred to as independent/dependent tables.)

If you search this forum for Normalization, you could get some articles. If you search the general web, you have to make that Database Normalization because other disciplines use the word "Normalization" as well. If you qualify it by specifying Database Normalization then you will get LOTS of good reading materials. Skim through a few articles to see if any of them "connect" with you. It is a common enough topic that you should get literally thousands of potential hits. I would recommend starting with .EDU domain sites before branching off to other domains. Mostly because the .COM database sites often have something to sell you. (But not always!)
 

Ula_Stawska

New member
Local time
Today, 07:10
Joined
Oct 6, 2023
Messages
1
OK, understand that I don't know the detailed intent of your DB, though I can guess. Therefore, I have no idea whether this is CORRECT. However, this structure is simplified a lot and is FAR less likely to lead to Access having internal confusion when you create queries.

If you were not aware of these ideas, may I respectfully suggest that you do a little reading on Database Normalization? In particular, for the kind of problem we saw in your first diagram, the key concepts would be for parent/child tables (also referred to as independent/dependent tables.)

If you search this forum for Normalization, you could get some articles. If you search the general web, you have to make that Database Normalization because other disciplines use the word "Normalization" as well. If you qualify it by specifying Database Normalization then you will get LOTS of good reading materials. Skim through a few articles to see if any of them "connect" with you. It is a common enough topic that you should get literally thousands of potential hits. I would recommend starting with .EDU domain sites before branching off to other domains. Mostly because the .COM database sites often have something to sell you. (But not always!)
Awesome post
 

Users who are viewing this thread

Top Bottom