Question Trouble with relationships. (1 Viewer)

[drarnoldm]

I am building a small database for a customer. They have a funeral home and needs to keep track of customers and their payments and Balances. I created the tables and primary keys and thought my relationships were correct. My form works well but when I create a query or report I get: source table open in exclusive mode. I read all threads about this but still cannot figure out my problem. Please help.

Thank you,
Arnold

 

[Ranman256]

Exclusive mode is not a bad thing unless you want to share with other users.
All you need is 3 tables.
TCustomer
TBilling
TPayments.

TBilling------
BillID,CustID,Item,qty,Amt,Date

TPayments------
CustID,Date,PayAmt, memo,billID

 

[drarnoldm]

I have now created the 3 tables as suggested. But which fields should be the primary key ??? And also how are the relationships joined ? I tried several differant ways but it makes it much worse than before ???

 

[jdraw]

Does your Customer offer different services?
Does he keep records of Services performed?

You may need another/more table(s).

See this generic funeral home data model from Barry Williams' site for ideas.

 

[drarnoldm]

Thank you for your reply and patience. I have reviewed your post but it fails to show the relationships and how they connect. My database is not even close to that deep, but If I can see how the relationships join to each table, I can take it from there. Thank you...
I have uploaded my database in previous post for you to see.

 

[jdraw]

A few things to consider:

The free generic data model is based on a typical set up. Yours may be more detailed or less detailed.
You can pick the pieces you need to fit your situation.

Whenever you are designing a database, you work from a set of requirements. The requirements are often derived from a description of the "business". The description includes facts that are used to identify relationships.

eg. A Customer may make 1 or many Orders
An Order may be for 1 or Many Products

Assign meaningful names to your tables and fields.
--Account and Record are not representative of the subject (in my view)
You are dealing with a Customer who has purchased a Good and/or Service for which he will be Invoiced and for which he is required to make a Payment(s).


See this for a basic approach to DatabaseDesign.

Work through this tutorial from RogersAccessLibrary to experience designing database (entities and relationships) from a clear description of requirements.

 

[drarnoldm]

I have done everything you suggested. Please look at my database to see what I am doing wrong. Query still does not work... Thank you...

 

[jdraw]

Sorry to disagree.
You need a description of what this "business is about" and its scope. And you need to build a database design with tables and relationships based on the facts of the business.
I can see from your record table that your data is not normalized.
I also see entities for which you are recording info, but you have placed within the record table.

eg: owner of Lot
section
lot
block
graves....

Based on your current design, I'm thinking this is a first database. Experienced Access people will advise you to adopt a naming convention that does NOT allow embedded spaces in field and object names. It will save you from many syntax errors.

Lot and Lot 2
Block and Block 2 are representative of a non-normalized structure.

Normalization is key to a successful relational database.

Did you work through the tutorial from RogersAccessLibrary? It takes about 30-60 minutes, but you will learn from it.

Here is a link to a commercial cemetery software site. You can see some screens and reports they offer. May give you some insight for your database.

If you are strictly dealing with customers and payments, then this model may be best suited to your needs --some adjustments to be expected.

Good luck with your project.