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

[AHMEDRASHED]

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

 

[June7]

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.

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

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.

 

[The_Doc_Man]

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]

@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

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

 

[The_Doc_Man]

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]

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