Anyone willing to look @ my relationships?

[MHadden]

I have been working for a few weeks now, but cannot seem to get the relationships working properly on this DBase. I am just now learning about normalization. When I started out, I only had one huge table. I have now separated it into several smaller tables. My problem is that I want to create a query based on these tables, that would emulate the previous structure. I can't seem to pull all of the information together like I want to & I feel like it is due to the relationships not being set up properly. In some cases, I end up with no data, and other trials, I end up with a cartesian product (my big word for the day).
Anyway, I am still learning, so any help you can offer is greatly appreciated! I will try attaching my DBase, or a bitmap of the table structure here, but if it doesn't work, I will gladly send it to anyone who is willing to look it over for me.

Thanks in advance, Michael

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[dhookom]

Mike,
Either post a screen shot of your relationship diagram on the web some place or take the time to type in your table and field names as well as the relationships.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fneily]

A couple of quick things before you post your table structures. When you create a query, you should see a connecting line between the tables in the query area. If you do not, you get your "cartesian product".
Also, try to keep field names consistent amongst your tables, eg. CustID should always be CustID. Try not using CustID in one table and CustomerID in another.
Though it still isn't as clear as I could explain it, you may want to read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

 

[MHadden]

Thanks to both of you.
Duane,
I agree, I should have done a better job at explaining my tables. I forgot that I couldn't just post it here, like some sites. I ran out of time to type it here, and don't have access to web space at the moment, but I will update this thread soon with the information. Thanks for pointing it out. I'll do better soon.

fneily,
Thanks for the link, I'll certainly check it out right away. I also appreciate the pointer about field names.

I'll be back later today...


MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[MHadden]

Sorry it took so long, but here is a link to a screenshot of the relationship window, as well as the full database if anyone has time & would like to look it over. I am still reading the fundamentals...

http://webpages.charter.net/haddensplace/

Thanks again for any help
Michael

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[dhookom]

You would never want to create a query with any more than tblCustomers and one other table. You can display and edit this information with a main form based on tblCustomers and subforms for the other tables.

I also assume the last group of fields in tblCustomers will be abandoned.

I don't care much for tblRx since it looks like you are storing values in field names. If you want to add another Rx to your application, you should never need to add fields or controls.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MHadden]

Thanks.
The reason for the last few fields in tblCustomers, is that I was trying to use them for linking, rather than linking all of the other tables via Customer_ID. I tried it, but it didn't seem to work properly. I left them for now, thinking that I may be advised, by someone in this forum, to go back to that original plan. If not, then, yes, I would delete them as they will serve no purpose.

I started out with only one table & used it for a few years before learning that it would be better to have separate ones. Now that I have created them, I want to create a query of all the tables, then use that to import the data from the old table.
In my database, I have already created a main form based on tblCustomers. This form has subforms for all of the other tables. Since you suggested that I do it that way, I'm glad to see that I'm on the right track.

Another reason I'm hoping to create the query is that I have reports that are based on the (Original) table, and I have another reporting structure that's based on a mail merge that works so smoothly based on the "one table" design, that I hoped to use it for these purposes only.

One person in a different forum suggested using sub reports, which is a great idea, but for the mail merge, I have no clue how to make it work.

I Hope I didn't bore you with all these details, but since you've taken the time to help, I thought it only sensible to give as many details as possible.

Again - Thanks a Million,
Michael





MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[MHadden]

Oh yeah, I should have also mentioned the purpose of tblRx. I use this for patients that have been prescribed supplemental home oxygen. The prescription is based on an order, that is stated as XX LPM with XX being the numeric value & LPM meaning "liters per minute". When this is prescribed, it will be stated as Continuous use, PRN - or as needed, Nocturnal - or while sleeping, or during exercise, etc.
While I completely see your point, I have been in the home oxygen business for slightly over 14 years, and have never seen a prescription that did not fall within these parameters.
That said, I don't want to appear unwilling to accept suggestions, I just haven't found a better way as of this time.

Thanks - lots,
Michael

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[dhookom]

Are the fields in tblRX mutually exclusive or are the each y/n or other fields where multiple can be applied/selected/used.

Aren't there possibly multiple records in most tables related to a single record in tblCustomers?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MHadden]

I think I understand your questions, but to be sure, I'll answer them this way...
It would be possible for a physician to prescribe oxygen for a patient to use at 2 LPM during exertion, or while exercising, and 1 LPM while sleeping, then give them the option of turning it up to 3 as needed, based on how they are feeling at that moment.
My data entry fields would look like this:
O2_Cont O2_Exercise O2_Rest O2_Sleep O2_PRN
2 1 3

The fields are setup as text fields with 5 spaces (in case the doc. say 2 - 3 lpm). They can be left blank, as well they are not dependant on one another. Probably more info. than you asked for, but I wanted to be sure.

As for question #2:
Absolutely! There will almost always be more than one record from the other tables related to each customer. That's where I start having problems...
Not knowing the best way to relate one table to another, seems to be my biggest hangup at the moment.
When I first started out, I actually had fields in the table named Item_1 Description_1 then Item_2 Description_2, etc.

Thanks for taking so much time to help me. I hope my explanations are making sense...


MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[Crowley16]

just some quick thoughts on the schema without giving it too much analysis...

1)
I'd be tempted to split out the different contacts from the customers table, ie, have a contacts and contactTypes tables so that:

Contacts (ContactID PK, CustomerID FK, ContactTypeID FK, name, description, telNumber, telNumber2, fax...)

ContactTypes (ContactTypeID PK, Type) <-- e.g. EmergencyContact, Employer, CustomerOwnContactDetails

just in case you need more contacts info in the future.

2)
there's a 1-many relationship between customer and account, meaning one customer can have multiple accounts, but each account can ONLY be for one customer, does this make sense? Could one account be used to pay for multiple customers, i.e. 2 customers in the same family?

3)
The Payor, is this the person who is paying? If so, then should this be linked to the order or the account table instead?

4)
The Order table, should that also be linked to the Account table??

5)
The service table, should that be linked to the order table, or the account table instead of the customer table?

6)
The names of the other tables doesn't really say what they are for, so hard to know.

Generally, it's difficult to give detailed examinations for other people's schemas because you don't know what purpose the tables serve and what are thier real relationships.

I would suggest you first start by mapping out the entire business process from beginning to end, then identify each step within the process, and each individual piece of data required for each step, then try to reconcile these into tables...

--------------------
Procrastinate Now!

 

[dhookom]

I now have a better understanding of your O2 fields. I would have normalized this table so that each LPM number creates a new record rather than having separate fields. That's my opinion ;-)

It looks like you already have a start at the relationships based around tblCustomers. Again, there should never be a need to join all tables together in a single query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]