At a crossroad and clueless (1 Viewer)

[collins_jd]

Hello Experts! I am eager and excited at the prospect of solving my issues!
Long Time excel user. Brand New (3 months new) Access User. Been taking lessons with an MVP which was helped tremendously but I am a desperate biz owner with 8 staff working in my excel sheets. 7 of the 8 with limited to no excel knowledge. I know you understand my pain.
Started building the access system 3 months ago. Its close, will def want to add bells and whistles later but i need basic working functions for all staff like yesterday.

I see attachments as to what i currently have in place. Most work great. A couple of things worked and now dont. Several things i should delete bc Im not using at all. I think i may have too many relationships or redundant ones, possibly too many tables. I have just 2-3 things left to function before the next step of splitting and putting in MS Teams or Sharepoint. I guess before i post too many questions i figured you all could take a look and see if anything horrific stands out lol

I posted some of my biz info and what im looking to accomplish in my intro if that helps at all.

 

[jdraw]

Welcome to AWF, jd!
It appears you have been following Richard Rost based on your naming. If so, he is very experienced and has many often referenced youtube videos. As you are likely finding, Access and Excel are different animals. In order for members to offer advice, they will need to have more info about your business and processes. Whether you have enough tables and/or relationships, depends on your business facts; the entities involved and how they relate to one another.

 

[plog]

Tables -> Reports -> Forms

That's how you should develop in Access. Make sure you have the correct tables to accomodate your data, then make sure you can get the data out of those tables in the manner you need with reports, then build forms to get data into your tables. No sense building Michelangelo inspired forms if you are just throwing data into horrible tables that don't help you reach your end goal.

You should would work on reports and the queries that support them now. What questions do you ask of your data? Can you obtain those answers with your database? Can you compile invoices in the manner you want? Can you get the data out in all the ways you need? If not, fix your tables so that you can.

 

[Pat Hartman]

Looks reasonable for a start but you need to add RI before you start adding data. If you have data that is inconsistent, you won't be able to add RI so you'll need to fix the data first.

I know it is a pain to add data without forms, but you need some to validate your schema. It's a cart and horse thing. If you are using tab view, you might switch to form view. That allows you to have multiple objects open at one time which will make data creation much easier when you are doing it by hand so you have the IDs you need visible.

 

[XPS35]

I admire your courage to jump in at the deep end with Access. Your data model looks absolutely fine for a beginner.
Of course I do have a few questions and comments.

1. In many relationships, the property to enforce referential integrity is disabled. Normally you only turn it off in very exceptional cases. I see no reasons for this (for example with Patient - Office).
2. Why is OfficeID in the Order table? By the way, I can't fully assess that table because not all fields are visible.
3. What do you do when the selling price of a product changes? You can now only save the current price. That way you don't know at what price you sold a product at a certain point in the past.
4. Why are Form and FormID in the Product table? FormID suggests it references another table, but I don't see it.
5. The use of the ShippingCost table escapes me. The only thing you commit to it is ShipFree (yes or no?). Then there could only be one value per vendor and you can put it in that table.
6. I would expect that Dose is dependent on the patient (can differ per patient) and is therefore not a product data. The same may be true for AmntDispensed.
7. Because orders are linked to patients, I don't understand the name of the table Vendorinvoice. Patient ID does not belong in that table because you can find it through the order. I don't think VendorID should be in there at all. The InvoiceAmount is a derived data (sum of price minus discount times quantity). If there is only one invoice per order, you can include the remaining fields in the order table.

There are undoubtedly more points for improvement, but these were the first things that struck me. Don't mind my language. If it's crooked language, it's Google Translate's fault.

 

[Pat Hartman]

1. If RI cannot be enforced, it is because one of the tables does not have a PK or because the fields are not the same data type or you are trying to create a relationship on something other than PK to some data field

 

[MajP]

I do not understand the VendorT. If a Vendor can have many products then the Product table should have a vendorID FK. If a product could have many vendors it is correct, however, would find it hard to believe a product could be made by many people, but each vendor only produces one thing. More likely then it is a many to many and you need a junction table.

 

[The_Doc_Man]

I have to question priorities here. Looking at the diagram, you have to make a query from Patient to Order to OrderDetail to Product in order to know what your patient is taking. OK, that might be acceptable, but what question will you be asking most often? What meds does the patient take at this time? Or how many prescriptions (orders?) have been written for this patient? OR which prescriptions is the patient STILL taking?

I understand that from the business flow, you appear to have more or less duplicated your process, so I cannot fault the design. However, it still begs the question: What was the goal of this system? Business support or patient support? Clarify that for us and we will be better able to handle your questions.

 

[MarkK]

I wouldn't link to table that only provides one field.

 

[NauticalGent]

BTW JC, welcome to AWF!

Before we mange to scare you away, do you have a specific question in mind?