Crystal,
Not that I expect you to design my whole date base but I thought I would
toss this out. I have redesigned my database. I now have separate tables
for
Customers
Insurance Type
Carriers
Agents
Monthly Commission in formation.
Commission Month
Commission Year
Here is how it works in my head,
Each Customer can have up to 6 Insurance types, with associated carrier
Each Insurance type can have up to 5 agents, each with month separate
commission schedule for each line and each customer.
So when I multiply that out each customer has could have a maximum of over
30 pieces of information associated with it.
I was reading your book and it says that I should use a form to update only
one table at a time. I cannot figure out how to get the information into
the data base.
I would like one form in which I could pull up the customer, fill in the
lines of insurance they have and then for each line of insurance I would
fill in the associated agents and commission. This one form would be
through a query and updating multiple tables. Is that OK? I still have not
figured out how to do this on one form when all the information is in
separate tables.. Forms were easy when my tables were designed like excel
spreadsheets J but they were large. It seems like I need a multi part
form, if there is such a thing.
I tried something last night but the query would not pull up any records
when some of the tables were blank. I will have to work on that.
Thanks,
Bill Singer
MN
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Crystal
Sent: Monday, November 28, 2011 2:12 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Union Query -- redesign
Hi Bill,
> "Well that is not an easy fix."
it is not a fix -- it is a redesign, and a major one. Your whole way of
thinking must change. You are making a common mistake that is made by those
who come to Access from Excel. You need to unlearn the way you organize
data and think about normalizing (read the Normalization chapter in Access
Basics). If you do not structure your data properly, you will not be able
to take advantage of all the power that Access can give you.
"Primary Key" and "Foreign Key" are critical concepts in Access and have to
do with relationships. These terms are explained in my video tutorials
watch these videos:
Learn Access by Crystal (playlist)
http://www.youtube.com/view_play_list?p=1B2705CCB40CA4CA
and read this book:
Access Basics by Crystal (Bill Mosca's site)
http://thatlldoit.com
Free 100-page book that covers essentials in Access
Warm Regards,
Crystal
*
(: have an awesome day :)
*
________________________________
From: Bill Singer <Bill.Singer@at-group.net
<mailto:Bill.Singer%40at-group.net> >
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Sent: Monday, November 28, 2011 10:17 AM
Subject: RE: [MS_AccessPros] Union Query
Crystal,
Well that is not an easy fix. It will take a while to get my brain around
how to relate all of these tables to the main customer.
Currently I have one table with columns for each of the following, plus more
CustomerName
HealthCarrier
HealthCommission
DentalCarrier
DentalCommission
LifeCarrier
LifeCommission
Etc.
It sounds like I have designed my database incorrectly. (it is like an
excel spreadsheet) It is a good thing I am not under a deadline.
I will start making the transition to multiple tables for each piece of
information and see how far I get before I get stuck. It might take me a
while to get it figured out.and then I will wake up at 3:00 in the morning
and will not be able to sleep.
I do not understand what FK=Foreign Key means or how you are suggesting I
use it.
Thanks very much,
Bill Singer
MN
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Crystal
Sent: Sunday, November 27, 2011 8:04 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] Union Query
Hi Bill,
you should only be storing insurance carrier in one field. Make a related
table to store details of each type of insurance. For instance:
Carriers
- CarrierID, autonumber -- PK
- CarrierName, text
InsTypes
- InsTypID, autonumber -- PK
- InsType, text (ie: Life Insurance, Short Term Disability Insurance, Long
Term Disability Insurance, Dental Insurance)
Customers
-CustID, autonumber -- PK
- etc
Policies
- PolicyID, autonumber - PK
- InsTypID, long -- FK to InsTypes
- CustID, long -- FK to Customers
- CarrierID, long -- FK to Carriers
- etc
PK = Primary Key
FK = Foreign Key
Warm Regards,
Crystal
http://www.YouTube.com/LearnAccessByCrystal
*
(: have an awesome day :)
*
________________________________
From: "Bill.Singer@at-group.net <mailto:Bill.Singer%40at-group.net>
<mailto:Bill.Singer%40at-group.net> "
<Bill.Singer@at-group.net <mailto:Bill.Singer%40at-group.net>
<mailto:Bill.Singer%40at-group.net> >
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Sent: Sunday, November 27, 2011 5:32 PM
Subject: [MS_AccessPros] Union Query
I have found that a UNION Query will allow me to see all 6 pieces of
information from six different colums of information in one place. I have an
insurance carrier that writes Life Insurance, Short Term Disability
Insurance, Long Term Disability Insurance and Dental Insurance. I have the
Carrier Name for each of these lines of insurance stored in seperate colums
of a table. I want to view them all in one form at one time and the UNION
QUERY will do that just fine... except I want to be able to edit the
commission information in the UNION QUERY all on one form. The UNION QUERY
will not let me edit. Does anybody have any ideas how to pull all six pieces
of information together and show it on one form and have the ability to edit
it?
I hope my question is clear.
Thanks,
Bill in MN, where the cold means it is programming season.
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
_____
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1873 / Virus Database: 2101/4634 - Release Date: 11/23/11
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
_____
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1873 / Virus Database: 2101/4644 - Release Date: 11/28/11
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar