table

  • Thread starter Thread starter Kautzen
  • Start date Start date
K

Kautzen

At the end of our fiscal year we will need to change all of our Health
Insurance rates in data table. Is there a way of doing an update using a
make table query or some other mode of updating everyone easily. We have
about 150 staff on Health Insurance
 
Is there some sort of calculation that is used to update the data
eg
This year's costs X 5%

or if everyone different

Oh - it ",ay" an idea to crete a new table and transfer the insruance costs
for this year to it
The just add the new year's cost into another record - you'll then be able
to go back and see what the costs were at a date in the past - will also make
update the data each year much simpler
 
Is each individual's/organization's rate different? Is there a single rate
that all share?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
At the end of our fiscal year we will need to change all of our Health
Insurance rates in data table. Is there a way of doing an update using a
make table query or some other mode of updating everyone easily. We have
about 150 staff on Health Insurance
Click to expand...

An Update query seems called for... but as suggested, it would really help if
you describe your table structure. Having each employee's health insurance
rate stored separately in their personnel record seems like an incorrectly
normalized table structure, though!
 
Our Health Insurance has Medical costs which are standard depending on if you
have Employee only, Employee and Spouse, Employee and Children, or Employee
and Family and if you choose PPO or HMO. Vision and Dental are the similar,
but only one plan option, and the life insurance is just for employee's only
and at one rate. The Employee's pay the difference above the costs of
employee only. Hope this helps.
Many thanks
 
You have set me on a whole new path of thought. I will try the suggestion of
the increase as this would allow me to keep the rates for prior years. This
is the first year we have used Access for this. I kept all of the
information in an Excel spread sheet and so of course kept prior years, but
now it is a new format.
Your answer may be what I need.
Thanks
 
Jeff, I was not that clear in my post. We have standard rates that have
different breakdowns for what the employee wants, spouse, family, children
only( employee costs) and self (which is fully paid by company). We have
Medical, Dental, Vision and Life (employee only). Four our Medical we also
have HMO, PPO and Out of Area rates. I have this all set in a table and
another replly suggested I add the percentage to the beginning table and use
that so I can keep the different years rates available. I am going to see
what I can create in my test program. Any other info is appreciated.
 
Our Health Insurance has Medical costs which are standard depending on if you
have Employee only, Employee and Spouse, Employee and Children, or Employee
and Family and if you choose PPO or HMO. Vision and Dental are the similar,
but only one plan option, and the life insurance is just for employee's only
and at one rate. The Employee's pay the difference above the costs of
employee only. Hope this helps.
Many thanks
Click to expand...

I'd then suggest - as you're pursuing in response to my friends who also
responded - that you should have a table containing all these options; there
shouldn't be any dollar or percentage amounts in the employee table, just a
link to the insurance options table.
 
Kautzen said:
At the end of our fiscal year we will need to change all of our Health
Insurance rates in data table. Is there a way of doing an update using a
make table query or some other mode of updating everyone easily. We have
about 150 staff on Health Insurance
Click to expand...
 
iI am trying. I have set up a table for Medical, Dental Vision and Life all
with rates. It is working if the employee has not declined any coverage,
then my reports will not add up. I made a make table query to get all of the
names of everyone who has any type of insurance, so now I have captured all
the information, but I can't seem to get it to add up on the report if there
is a blank in one of the fields.
I have made the tables so that the difference between the 2008 rates 2009
rates will be entered (when we get them, but for now I have put fake amounts
in to work with). I can get the new amount, but no totals with the blanks.
I sure am hitting the help button a lot today.
Thanks for all of you help.
 
iI am trying. I have set up a table for Medical, Dental Vision and Life all
with rates. It is working if the employee has not declined any coverage,
then my reports will not add up. I made a make table query to get all of the
names of everyone who has any type of insurance, so now I have captured all
the information, but I can't seem to get it to add up on the report if there
is a blank in one of the fields.
Click to expand...

Sounds like you need some "Left Joins" in your queries. What is your current
table structure? Could you open the query you're using for the report in SQL
view and post it here?
I have made the tables so that the difference between the 2008 rates 2009
rates will be entered (when we get them, but for now I have put fake amounts
in to work with). I can get the new amount, but no totals with the blanks.
I sure am hitting the help button a lot today.
Thanks for all of you help.
Click to expand...

The NZ() function will turrn a Null into a Zero and will very likely be needed
in your queries. Anything plus NULL is NULL; a value plus NZ(Null) is just the
value + 0 or the value.
 
I am not sure how to do the posting you asked about. I did some print
screens and put them into an excel spread sheet, but I can't seem to find a
way to copy here. So I copied the SQL information. I am not sure how to get
it any better.


SELECT [Ins Test 1].[Employee#], [Last Name] & ", " & [First Name] AS
Expr1, [Ins Test 1].[program Number], [Ins Test 1].[Date information
entered], [Ins Test 1].[Edit Date], [Ins Test 1].[Eligability Date], [Ins
Test 1].[End Date], [Ins Test 1].Medical, [Ins Test 1].[Declined Med], [Ins
Test 1].Dental, [Ins Test 1].[Declined Dental], [Ins Test 1].Vision, [Ins
Test 1].[Declined Vision], [Ins Test 1].[Life Coverage], [Ins Test
1].[Declined Life], [Ins Test 1].[Active/Inactive], [Drop Down Medical].[Co $
M], [Drop Down Medical].[Emp $ M], [Drop Down Medical].[2009 Co $ M], [Drop
Down Medical].[2009 Emp $ M], [Drop Down Medical].[2010 Co $ M], [Drop Down
Medical].[2010 Emp $ M], [Drop Down Medical].[2011 Co $ M], [Drop Down
Medical].[2011 Emp $ M], [Drop Down Medical].[2012 Co $ M], [Drop Down
Medical].[2012 Emp $ M], [Drop Down Medical].[2013 Co $ M], [Drop Down
Medical].[2013 Emp $ M], [Drop Down Dental].[Co $ D], [Drop Down
Dental].[Emp $ D], [Drop Down Dental].[2009 Co $ D], [Drop Down
Dental].[2009 Emp $ D], [Drop Down Dental].[2010 Co $ D], [Drop Down
Dental].[2010 Emp $ D], [Drop Down Dental].[2011 Co $ D], [Drop Down
Dental].[2011 Emp $ D], [Drop Down Dental].[2012 Co $ D], [Drop Down
Dental].[2012 Emp $ D], [Drop Down Dental].[2013 Co $ D], [Drop Down
Dental].[2013 Emp $ D], [Drop Down Vision].[Co $ V], [Drop Down Vision].[Emp
$ V], [Drop Down Vision].[2009 Co $ V], [Drop Down Vision].[2009 Emp $ V],
[Drop Down Vision].[2010 Co $ V], [Drop Down Vision].[2010 Emp $ V], [Drop
Down Vision].[2011 Co $ V], [Drop Down Vision].[2011 Emp $ V], [Drop Down
Vision].[2012 Co $ V], [Drop Down Vision].[2012 Emp $ V], [Drop Down
Vision].[2013 Co $ V], [Drop Down Vision].[2013 Emp $ V], [Drop Down
Life].[Co $ L], [Drop Down Life].[Emp $ L], [Drop Down Life].[2009 Co $ L],
[Drop Down Life].[2009 Emp $ L], [Drop Down Life].[2010 Co $ L], [Drop Down
Life].[2010 Emp $ L], [Drop Down Life].[2011 Co $ L], [Drop Down
Life].[2011 Emp $ L], [Drop Down Life].[2012 Co $ L], [Drop Down Life].[2012
Emp $ L], [Drop Down Life].[2013 Co $ L], [Drop Down Life].[2013 Emp $ L]
INTO [Insurance Table for Auto Date]
FROM ((([Ins Test 1] LEFT JOIN [Drop Down Medical] ON [Ins Test 1].Medical =
[Drop Down Medical].[type M]) LEFT JOIN [Drop Down Dental] ON [Ins Test
1].Dental = [Drop Down Dental].[type D]) LEFT JOIN [Drop Down Vision] ON [Ins
Test 1].Vision = [Drop Down Vision].[type V]) LEFT JOIN [Drop Down Life] ON
[Ins Test 1].[Life Coverage] = [Drop Down Life].[type L];



I think I am treading into uncharted waters here and may be over my head.
 
John, I got it. I had kept trying to get the Nz function to work in the
query and could not do it. I finally went to the report and did it in the
totals, and it works. Your information has been very good. I would
recommend you to anyone. Hopefully you will have more insight for me
whenever you can. Again Thanks!!
 
SELECT [Ins Test 1].[Employee#], [Last Name] & ", " & [First Name] AS
Expr1, [Ins Test 1].[program Number], [Ins Test 1].[Date information
entered], [Ins Test 1].[Edit Date], [Ins Test 1].[Eligability Date], [Ins
Test 1].[End Date], [Ins Test 1].Medical, [Ins Test 1].[Declined Med], [Ins
Test 1].Dental, [Ins Test 1].[Declined Dental], [Ins Test 1].Vision, [Ins
Test 1].[Declined Vision], [Ins Test 1].[Life Coverage], [Ins Test
1].[Declined Life], [Ins Test 1].[Active/Inactive], [Drop Down Medical].[Co $
M], [Drop Down Medical].[Emp $ M], [Drop Down Medical].[2009 Co $ M], [Drop
Down Medical].[2009 Emp $ M], [Drop Down Medical].[2010 Co $ M], [Drop Down
Medical].[2010 Emp $ M], [Drop Down Medical].[2011 Co $ M], [Drop Down
Medical].[2011 Emp $ M], [Drop Down Medical].[2012 Co $ M], [Drop Down
Medical].[2012 Emp $ M], [Drop Down Medical].[2013 Co $ M], [Drop Down
Medical].[2013 Emp $ M], [Drop Down Dental].[Co $ D], [Drop Down
Dental].[Emp $ D], [Drop Down Dental].[2009 Co $ D], [Drop Down
Dental].[2009 Emp $ D], [Drop Down Dental].[2010 Co $ D], [Drop Down
Dental].[2010 Emp $ D], [Drop Down Dental].[2011 Co $ D], [Drop Down
Dental].[2011 Emp $ D], [Drop Down Dental].[2012 Co $ D], [Drop Down
Dental].[2012 Emp $ D], [Drop Down Dental].[2013 Co $ D], [Drop Down
Dental].[2013 Emp $ D], [Drop Down Vision].[Co $ V], [Drop Down Vision].[Emp
$ V], [Drop Down Vision].[2009 Co $ V], [Drop Down Vision].[2009 Emp $ V],
[Drop Down Vision].[2010 Co $ V], [Drop Down Vision].[2010 Emp $ V], [Drop
Down Vision].[2011 Co $ V], [Drop Down Vision].[2011 Emp $ V], [Drop Down
Vision].[2012 Co $ V], [Drop Down Vision].[2012 Emp $ V], [Drop Down
Vision].[2013 Co $ V], [Drop Down Vision].[2013 Emp $ V], [Drop Down
Life].[Co $ L], [Drop Down Life].[Emp $ L], [Drop Down Life].[2009 Co $ L],
[Drop Down Life].[2009 Emp $ L], [Drop Down Life].[2010 Co $ L], [Drop Down
Life].[2010 Emp $ L], [Drop Down Life].[2011 Co $ L], [Drop Down
Life].[2011 Emp $ L], [Drop Down Life].[2012 Co $ L], [Drop Down Life].[2012
Emp $ L], [Drop Down Life].[2013 Co $ L], [Drop Down Life].[2013 Emp $ L]
INTO [Insurance Table for Auto Date]
FROM ((([Ins Test 1] LEFT JOIN [Drop Down Medical] ON [Ins Test 1].Medical =
[Drop Down Medical].[type M]) LEFT JOIN [Drop Down Dental] ON [Ins Test
1].Dental = [Drop Down Dental].[type D]) LEFT JOIN [Drop Down Vision] ON [Ins
Test 1].Vision = [Drop Down Vision].[type V]) LEFT JOIN [Drop Down Life] ON
[Ins Test 1].[Life Coverage] = [Drop Down Life].[type L];



I think I am treading into uncharted waters here and may be over my head.
Click to expand...

There's some folks around here who know where the stepping stones are... and
we've got a good supply of floatation devices! <bg>

One step you REALLY REALLY should take is to normalize your Drop Down tables
some more. Having the year in fieldnames is "committing spreadsheet" and will
make future queries much more complex. For one thing, as soon as 2014 figures
are available you'll need to restructure your tables, all your queries, all
your reports, all your forms... OUCH!

The year of a policy *IS DATA*. You should have four *records*, one for each
year, with a PolicyYear field containing 2009, 2010 etc. If you need to see
all the years' data in a spreadsheet format, for a report say, you can use a
Crosstab query to convert the "tall-thin" normalized design into a "wide-flat"
display.
 
I will try the stepping stones for defining the tables to be easier to use.
For a basically self taught I am sure learning a lot from you. Thanks
--
Kautzen


John W. Vinson said:
SELECT [Ins Test 1].[Employee#], [Last Name] & ", " & [First Name] AS
Expr1, [Ins Test 1].[program Number], [Ins Test 1].[Date information
entered], [Ins Test 1].[Edit Date], [Ins Test 1].[Eligability Date], [Ins
Test 1].[End Date], [Ins Test 1].Medical, [Ins Test 1].[Declined Med], [Ins
Test 1].Dental, [Ins Test 1].[Declined Dental], [Ins Test 1].Vision, [Ins
Test 1].[Declined Vision], [Ins Test 1].[Life Coverage], [Ins Test
1].[Declined Life], [Ins Test 1].[Active/Inactive], [Drop Down Medical].[Co $
M], [Drop Down Medical].[Emp $ M], [Drop Down Medical].[2009 Co $ M], [Drop
Down Medical].[2009 Emp $ M], [Drop Down Medical].[2010 Co $ M], [Drop Down
Medical].[2010 Emp $ M], [Drop Down Medical].[2011 Co $ M], [Drop Down
Medical].[2011 Emp $ M], [Drop Down Medical].[2012 Co $ M], [Drop Down
Medical].[2012 Emp $ M], [Drop Down Medical].[2013 Co $ M], [Drop Down
Medical].[2013 Emp $ M], [Drop Down Dental].[Co $ D], [Drop Down
Dental].[Emp $ D], [Drop Down Dental].[2009 Co $ D], [Drop Down
Dental].[2009 Emp $ D], [Drop Down Dental].[2010 Co $ D], [Drop Down
Dental].[2010 Emp $ D], [Drop Down Dental].[2011 Co $ D], [Drop Down
Dental].[2011 Emp $ D], [Drop Down Dental].[2012 Co $ D], [Drop Down
Dental].[2012 Emp $ D], [Drop Down Dental].[2013 Co $ D], [Drop Down
Dental].[2013 Emp $ D], [Drop Down Vision].[Co $ V], [Drop Down Vision].[Emp
$ V], [Drop Down Vision].[2009 Co $ V], [Drop Down Vision].[2009 Emp $ V],
[Drop Down Vision].[2010 Co $ V], [Drop Down Vision].[2010 Emp $ V], [Drop
Down Vision].[2011 Co $ V], [Drop Down Vision].[2011 Emp $ V], [Drop Down
Vision].[2012 Co $ V], [Drop Down Vision].[2012 Emp $ V], [Drop Down
Vision].[2013 Co $ V], [Drop Down Vision].[2013 Emp $ V], [Drop Down
Life].[Co $ L], [Drop Down Life].[Emp $ L], [Drop Down Life].[2009 Co $ L],
[Drop Down Life].[2009 Emp $ L], [Drop Down Life].[2010 Co $ L], [Drop Down
Life].[2010 Emp $ L], [Drop Down Life].[2011 Co $ L], [Drop Down
Life].[2011 Emp $ L], [Drop Down Life].[2012 Co $ L], [Drop Down Life].[2012
Emp $ L], [Drop Down Life].[2013 Co $ L], [Drop Down Life].[2013 Emp $ L]
INTO [Insurance Table for Auto Date]
FROM ((([Ins Test 1] LEFT JOIN [Drop Down Medical] ON [Ins Test 1].Medical =
[Drop Down Medical].[type M]) LEFT JOIN [Drop Down Dental] ON [Ins Test
1].Dental = [Drop Down Dental].[type D]) LEFT JOIN [Drop Down Vision] ON [Ins
Test 1].Vision = [Drop Down Vision].[type V]) LEFT JOIN [Drop Down Life] ON
[Ins Test 1].[Life Coverage] = [Drop Down Life].[type L];



I think I am treading into uncharted waters here and may be over my head.
Click to expand...

There's some folks around here who know where the stepping stones are... and
we've got a good supply of floatation devices! <bg>

One step you REALLY REALLY should take is to normalize your Drop Down tables
some more. Having the year in fieldnames is "committing spreadsheet" and will
make future queries much more complex. For one thing, as soon as 2014 figures
are available you'll need to restructure your tables, all your queries, all
your reports, all your forms... OUCH!

The year of a policy *IS DATA*. You should have four *records*, one for each
year, with a PolicyYear field containing 2009, 2010 etc. If you need to see
all the years' data in a spreadsheet format, for a report say, you can use a
Crosstab query to convert the "tall-thin" normalized design into a "wide-flat"
display.
Click to expand...
 
John,
Just to let you know, I re-did all of the tables with the Policy Year as
"data" and put some fake numbers in for the 2009 year to test my reports. I
made three queries to get the complete data I wanted, but it is really
working fine. I know there is a lot for me to still refine, but this is a
great start. I just wanted to thank you again and let you know "its
working". :)
Carol
--
Kautzen


John W. Vinson said:
SELECT [Ins Test 1].[Employee#], [Last Name] & ", " & [First Name] AS
Expr1, [Ins Test 1].[program Number], [Ins Test 1].[Date information
entered], [Ins Test 1].[Edit Date], [Ins Test 1].[Eligability Date], [Ins
Test 1].[End Date], [Ins Test 1].Medical, [Ins Test 1].[Declined Med], [Ins
Test 1].Dental, [Ins Test 1].[Declined Dental], [Ins Test 1].Vision, [Ins
Test 1].[Declined Vision], [Ins Test 1].[Life Coverage], [Ins Test
1].[Declined Life], [Ins Test 1].[Active/Inactive], [Drop Down Medical].[Co $
M], [Drop Down Medical].[Emp $ M], [Drop Down Medical].[2009 Co $ M], [Drop
Down Medical].[2009 Emp $ M], [Drop Down Medical].[2010 Co $ M], [Drop Down
Medical].[2010 Emp $ M], [Drop Down Medical].[2011 Co $ M], [Drop Down
Medical].[2011 Emp $ M], [Drop Down Medical].[2012 Co $ M], [Drop Down
Medical].[2012 Emp $ M], [Drop Down Medical].[2013 Co $ M], [Drop Down
Medical].[2013 Emp $ M], [Drop Down Dental].[Co $ D], [Drop Down
Dental].[Emp $ D], [Drop Down Dental].[2009 Co $ D], [Drop Down
Dental].[2009 Emp $ D], [Drop Down Dental].[2010 Co $ D], [Drop Down
Dental].[2010 Emp $ D], [Drop Down Dental].[2011 Co $ D], [Drop Down
Dental].[2011 Emp $ D], [Drop Down Dental].[2012 Co $ D], [Drop Down
Dental].[2012 Emp $ D], [Drop Down Dental].[2013 Co $ D], [Drop Down
Dental].[2013 Emp $ D], [Drop Down Vision].[Co $ V], [Drop Down Vision].[Emp
$ V], [Drop Down Vision].[2009 Co $ V], [Drop Down Vision].[2009 Emp $ V],
[Drop Down Vision].[2010 Co $ V], [Drop Down Vision].[2010 Emp $ V], [Drop
Down Vision].[2011 Co $ V], [Drop Down Vision].[2011 Emp $ V], [Drop Down
Vision].[2012 Co $ V], [Drop Down Vision].[2012 Emp $ V], [Drop Down
Vision].[2013 Co $ V], [Drop Down Vision].[2013 Emp $ V], [Drop Down
Life].[Co $ L], [Drop Down Life].[Emp $ L], [Drop Down Life].[2009 Co $ L],
[Drop Down Life].[2009 Emp $ L], [Drop Down Life].[2010 Co $ L], [Drop Down
Life].[2010 Emp $ L], [Drop Down Life].[2011 Co $ L], [Drop Down
Life].[2011 Emp $ L], [Drop Down Life].[2012 Co $ L], [Drop Down Life].[2012
Emp $ L], [Drop Down Life].[2013 Co $ L], [Drop Down Life].[2013 Emp $ L]
INTO [Insurance Table for Auto Date]
FROM ((([Ins Test 1] LEFT JOIN [Drop Down Medical] ON [Ins Test 1].Medical =
[Drop Down Medical].[type M]) LEFT JOIN [Drop Down Dental] ON [Ins Test
1].Dental = [Drop Down Dental].[type D]) LEFT JOIN [Drop Down Vision] ON [Ins
Test 1].Vision = [Drop Down Vision].[type V]) LEFT JOIN [Drop Down Life] ON
[Ins Test 1].[Life Coverage] = [Drop Down Life].[type L];



I think I am treading into uncharted waters here and may be over my head.
Click to expand...

There's some folks around here who know where the stepping stones are... and
we've got a good supply of floatation devices! <bg>

One step you REALLY REALLY should take is to normalize your Drop Down tables
some more. Having the year in fieldnames is "committing spreadsheet" and will
make future queries much more complex. For one thing, as soon as 2014 figures
are available you'll need to restructure your tables, all your queries, all
your reports, all your forms... OUCH!

The year of a policy *IS DATA*. You should have four *records*, one for each
year, with a PolicyYear field containing 2009, 2010 etc. If you need to see
all the years' data in a spreadsheet format, for a report say, you can use a
Crosstab query to convert the "tall-thin" normalized design into a "wide-flat"
display.
Click to expand...
 
Back
Top