Comparing Tables

For the purpose of my question, the tables I am dealing with are named
as such:

RawData (houses the data on our customer's employees as we enter
it)
TheirData (houses data we will import that we receive from our
client)

The purpose here is to compare their data with our data, as we don't
always get all the information we need from the start. Monthly, they
will send us a list of employee deductions, which will inform us if we
have not enrolled someone in benefits they are being charged for.

My thought was to do an append query that would check RawData and
compare it with TheirData using social security numbers as an
identifier. If there is a discrepancy, append a new table,
Discrepancies, with a new record displaying the discrepancy. For
example, if we find Joe Smith (333-222-4444) in TheirData being
charged an amount for MedPlan1, but show him in RawData under the
MedPlan1 field as being charged $0.00 - this means he has not been
enrolled. I would then want to append Discrepancies with something
like, Joe Smith billed for MedPlan1 but not enrolled. The
Discrepancies table would then be used to generate a report.

Am I on the right track, and if so, how would I structure an append
query to do this?

Thanks in advance for your help!

magmike

Here's another thought...

Instead of appending, use a query to find "unmatched". It sounds like you
could use TheirData.Amt RawData.Amt as a criterion.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"magmike" wrote in message
...
For the purpose of my question, the tables I am dealing with are named
as such:

RawData (houses the data on our customer's employees as we enter
it)
TheirData (houses data we will import that we receive from our
client)

The purpose here is to compare their data with our data, as we don't
always get all the information we need from the start. Monthly, they
will send us a list of employee deductions, which will inform us if we
have not enrolled someone in benefits they are being charged for.

My thought was to do an append query that would check RawData and
compare it with TheirData using social security numbers as an
identifier. If there is a discrepancy, append a new table,
Discrepancies, with a new record displaying the discrepancy. For
example, if we find Joe Smith (333-222-4444) in TheirData being
charged an amount for MedPlan1, but show him in RawData under the
MedPlan1 field as being charged $0.00 - this means he has not been
enrolled. I would then want to append Discrepancies with something
like, Joe Smith billed for MedPlan1 but not enrolled. The
Discrepancies table would then be used to generate a report.

Am I on the right track, and if so, how would I structure an append
query to do this?

Thanks in advance for your help!

magmike

On Mar 15, 12:55*pm, "Jeff Boyce" wrote:
Here's another thought...

Instead of appending, use a query to find "unmatched". *It sounds like you
could use TheirData.Amt RawData.Amt as a criterion.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"magmike" wrote in message

...



For the purpose of my question, the tables I am dealing with are named
as such:

RawData * *(houses the data on our customer's employees as we enter
it)
TheirData * (houses data we will import that we receive from our
client)

The purpose here is to compare their data with our data, as we don't
always get all the information we need from the start. Monthly, they
will send us a list of employee deductions, which will inform us if we
have not enrolled someone in benefits they are being charged for.

My thought was to do an append query that would check RawData and
compare it with TheirData using social security numbers as an
identifier. If there is a discrepancy, append a new table,
Discrepancies, with a new record displaying the discrepancy. For
example, if we find Joe Smith (333-222-4444) in TheirData being
charged an amount for MedPlan1, but show him in RawData under the
MedPlan1 field as being charged $0.00 - this means he has not been
enrolled. I would then want to append Discrepancies with something
like, Joe Smith billed for MedPlan1 but not enrolled. The
Discrepancies table would then be used to generate a report.

Am I on the right track, and if so, how would I structure an append
query to do this?

Thanks in advance for your help!

magmike

That's a little simpler. Would that work when there are 16 different
fields I need to do this with, within one query? Or could that pose
other problems?

Now you have me a little nervous ... If there are "16 different fields" you
need to compare, think about posting a description of those tables here and
see what folks can come up with.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"magmike" wrote in message
...
On Mar 15, 12:55 pm, "Jeff Boyce" wrote:
Here's another thought...

Instead of appending, use a query to find "unmatched". It sounds like you
could use TheirData.Amt RawData.Amt as a criterion.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"magmike" wrote in message

...



For the purpose of my question, the tables I am dealing with are named
as such:

RawData (houses the data on our customer's employees as we enter
it)
TheirData (houses data we will import that we receive from our
client)

The purpose here is to compare their data with our data, as we don't
always get all the information we need from the start. Monthly, they
will send us a list of employee deductions, which will inform us if we
have not enrolled someone in benefits they are being charged for.

My thought was to do an append query that would check RawData and
compare it with TheirData using social security numbers as an
identifier. If there is a discrepancy, append a new table,
Discrepancies, with a new record displaying the discrepancy. For
example, if we find Joe Smith (333-222-4444) in TheirData being
charged an amount for MedPlan1, but show him in RawData under the
MedPlan1 field as being charged $0.00 - this means he has not been
enrolled. I would then want to append Discrepancies with something
like, Joe Smith billed for MedPlan1 but not enrolled. The
Discrepancies table would then be used to generate a report.

Am I on the right track, and if so, how would I structure an append
query to do this?

Thanks in advance for your help!

magmike

That's a little simpler. Would that work when there are 16 different
fields I need to do this with, within one query? Or could that pose
other problems?

First order of business - I didn't design this database! I've
recommended a new approach,

but currently they don't want to spend the time or hours doing so, so
I have the task of

taking their design, along with our client's data (the only way they
are willing to send it

to us in) and come up with this one singular report.

The purpose of the report, is to point out discrepancies between their
data and ours. We get

our data as the individual managers at each location give it to us
(which of course doesn't

happen all the time). But their corporate payroll department has all
of it. For an example,

the payroll department may be deducting money out of an employees
paycheck for health

insurance, but if we have never been given their enrollment form -
they haven't been

enrolled!

The first table, RawData, has one record per employee. These are
fields in the first table

we are working with:

LocationCode (Txt field)
SSN (Txt field with input mask)
MedPlanDed. (Currency)
CorpMedPlanDed (Currency)
COLMedBrDed (Currency)
HSAAmt (Currency)
MedSuppDed (Currency)
RxDed (Currency)
DentalDed (Currency)
CorpDentalDed (Currency)
VisionDed (Currency)
BasicLifeDed (Currency)
BuyUpLifeDed (Currency)
STDDed (Currency)
AccDed (Currency)
CancerDed (Currency)
EECIDed (Currency)
SPCIDed (Currency)
ULDed (Currency)
OLDULDed (Currency)

We need the social sec number, of course, to identify the employee.
There are 57 locations,

and the goal is to sort the final report by location. The rest of the
fields are currency

format, where we enter the amount of the monthly deduction from their
paycheck for that

benefit. If they do not elect a particular benefit, the field will
read zero.

Here is where it gets tough. The fields in the clients payroll
spreadsheet is as follows:

LocationCode (Txt field)
SSN (Txt field with input mask)
Benefit (Currency)
Deduction (Currency)

Each record represents each benefit currently being deducted for.
There are usually multiple

records for each employee.

For example,

99TX, 333224444, Medical Plan 1, 86.43
99TX, 333224444, Corp Dental, 22.36
99TX, 333224444, Basic Life, 0.00
99TX, 333224444, Cancer Plan 2, 15.65

What really makes this difficult is that these locations are all over
the country and

therefore there are multiple plans under each category. Therefore, the
field in which we

record the deduction in our database (i.e. MedPlanDed or
CorpMedPlanDed), will apply to

multiple Benefit values in our clients data. Here they are grouped by
our fields:

MedPlanDed
Boone Union
Carter Union
Kentucky Non-Union Medical
Medical Plan 1
Medical Plan 2
Plan 1 80% HDHP
Plan 2 80% HDHP
Plan 3 KY 80% HDHP

CorpMedPlanDed
High Deductible Health Plan

COLMedBRDed
Medical Bridge

HSAAmt
HSA Employer Co
HSA Family Contribution
HSA Single Contribution

MedSuppDed
Med Sup (2yrs+)
Med Sup 3 (6-24mo.)
Med Supp 3 (3-6mo.)

RxDed
Plan 3 RX
Plan 3 RX Generic

DentalDed
Dental Pro 1
Dental Pro 2

CorpDentalDed
Plan 1 Dental

VisionDed
Vision Plan

BasicLifeDed
Basic Life (Employer Provided)
Basic Life (Employer Provided Plan 1)
Union Basic Life (Employer Provided)

BuyUpLifeDed
Group Term Life (X2)DMS
Group Term Life(X1)
Group Term LIfe(X1)DMS
Group Term Life(X2)

STDDed
Pre Tax Disibility

AccDed
Accident

CancerDed
Cancer Insurance

EECIDed
SPECIAL NOTE: They only have one value for both EECIDed and
SPCIDed

although we have them separated. However, their dollar amount should
add up to the total of

both these fields, and if not, that is when we want it on the report
anyhow.

SPCIDed
SPECIAL NOTE: See notes above

ULDed
Universal Life

OLDULDed
American Fidelity

If the employee has a deduction for any of the listed options under
MedPlanDed, the

deduction amount will go into the MedPlanDed field in our database.
Therefore, when

comparing thei payroll data with our database, if the record field
"Benefit" is any of those

listed above under MedPlanDed, then we want to compare the Deduction
field with our

MedPlanDed field, and if they do not match, report that discrepancy in
the query which will

be used in a report.

Does that make more sense now?

Thanks in advance for you help!

magmike

On Mar 17, 6:33*pm, magmike wrote:
First order of business - I didn't design this database! I've
recommended a new approach,

but currently they don't want to spend the time or hours doing so, so
I have the task of

taking their design, along with our client's data (the only way they
are willing to send it

to us in) and come up with this one singular report.

The purpose of the report, is to point out discrepancies between their
data and ours. We get

our data as the individual managers at each location give it to us
(which of course doesn't

happen all the time). But their corporate payroll department has all
of it. For an example,

the payroll department may be deducting money out of an employees
paycheck for health

insurance, but if we have never been given their enrollment form -
they haven't been

enrolled!

The first table, RawData, has one record per employee. These are
fields in the first table

we are working with:

* * * * LocationCode *(Txt field)
* * * * SSN *(Txt field with input mask)
* * * * MedPlanDed. *(Currency)
* * * * CorpMedPlanDed *(Currency)
* * * * COLMedBrDed *(Currency)
* * * * HSAAmt *(Currency)
* * * * MedSuppDed *(Currency)
* * * * RxDed *(Currency)
* * * * DentalDed *(Currency)
* * * * CorpDentalDed *(Currency)
* * * * VisionDed *(Currency)
* * * * BasicLifeDed *(Currency)
* * * * BuyUpLifeDed *(Currency)
* * * * STDDed *(Currency)
* * * * AccDed *(Currency)
* * * * CancerDed (Currency)
* * * * EECIDed *(Currency)
* * * * SPCIDed *(Currency)
* * * * ULDed *(Currency)
* * * * OLDULDed *(Currency)

We need the social sec number, of course, to identify the employee.
There are 57 locations,

and the goal is to sort the final report by location. The rest of the
fields are currency

format, where we enter the amount of the monthly deduction from their
paycheck for that

benefit. If they do not elect a particular benefit, the field will
read zero.

Here is where it gets tough. The fields in the clients payroll
spreadsheet is as follows:

* * * * LocationCode (Txt field)
* * * * SSN *(Txt field with input mask)
* * * * Benefit *(Currency)
* * * * Deduction *(Currency)

Each record represents each benefit currently being deducted for.
There are usually multiple

records for each employee.

For example,

* * * * 99TX, 333224444, Medical Plan 1, 86.43
* * * * 99TX, 333224444, Corp Dental, 22.36
* * * * 99TX, 333224444, Basic Life, 0.00
* * * * 99TX, 333224444, Cancer Plan 2, 15.65

What really makes this difficult is that these locations are all over
the country and

therefore there are multiple plans under each category. Therefore, the
field in which we

record the deduction in our database (i.e. MedPlanDed or
CorpMedPlanDed), will apply to

multiple Benefit values in our clients data. Here they are grouped by
our fields:

* * * * MedPlanDed
* * * * * * * * Boone Union
* * * * * * * * Carter Union
* * * * * * * * Kentucky Non-Union Medical
* * * * * * * * Medical Plan 1
* * * * * * * * Medical Plan 2
* * * * * * * * Plan 1 80% HDHP
* * * * * * * * Plan 2 80% HDHP
* * * * * * * * Plan 3 KY 80% HDHP

* * * * CorpMedPlanDed
* * * * * * * * High Deductible Health Plan

* * * * COLMedBRDed
* * * * * * * * Medical Bridge

* * * * HSAAmt
* * * * * * * * HSA Employer Co
* * * * * * * * HSA Family Contribution
* * * * * * * * HSA Single Contribution

* * * * MedSuppDed
* * * * * * * * Med Sup (2yrs+)
* * * * * * * * Med Sup 3 (6-24mo.)
* * * * * * * * Med Supp 3 (3-6mo.)

* * * * RxDed
* * * * * * * * Plan 3 RX
* * * * * * * * Plan 3 RX Generic

* * * * DentalDed
* * * * * * * * Dental Pro 1
* * * * * * * * Dental Pro 2

* * * * CorpDentalDed
* * * * * * * * Plan 1 Dental

* * * * VisionDed
* * * * * * * * Vision Plan

* * * * BasicLifeDed
* * * * * * * * Basic Life (Employer Provided)
* * * * * * * * Basic Life (Employer Provided Plan 1)
* * * * * * * * Union Basic Life (Employer Provided)

* * * * BuyUpLifeDed
* * * * * * * * Group Term Life (X2)DMS
* * * * * * * * Group Term Life(X1)
* * * * * * * * Group Term LIfe(X1)DMS
* * * * * * * * Group Term Life(X2)

* * * * STDDed
* * * * * * * * Pre Tax Disibility

* * * * AccDed
* * * * * * * * Accident

* * * * CancerDed
* * * * * * * * Cancer Insurance

* * * * EECIDed
* * * * * * * * SPECIAL NOTE: *They only have one value for both EECIDed and
SPCIDed

although we have them separated. However, their dollar amount should
add up to the total of

both these fields, and if not, that is when we want it on the report
anyhow.

* * * * SPCIDed
* * * * * * * * SPECIAL NOTE: *See notes above

* * * * ULDed
* * * * * * * * Universal Life

* * * * OLDULDed
* * * * * * * * American Fidelity

If the employee has a deduction for any of the listed options under
MedPlanDed, the

deduction amount will go into the MedPlanDed field in our database.
Therefore, when

comparing thei payroll data with our database, if the record field
"Benefit" is any of those

listed above under MedPlanDed, then we want to compare the Deduction
field with our

MedPlanDed field, and if they do not match, report that discrepancy in
the query which will

be used in a report.

Does that make more sense now?

Thanks in advance for you help!

magmike

BTW - I mistakenly listed "Benefit" as a Currency field, but it is a
text field for a description of the benefit being deducted for.

Okay, that post didn't display well, so I have modified it here by
displaying it comma delimmited. I've also numbered each record
result.

Dallas, TX Branch Discrepancies
SSN, Benefit, Payroll Ded, Database Deduction, Notes
(1) 415-33-1111, Medical Plan 1, 86.79, 0.00,
(2) 415-33-1111, Dental Plan, 23.76, 0.00,
(3) 387-11-3333, Basic Life, 0.00, , Employee not in database
(4) 428-66-9999, , , , Employee not on payroll

Los Angelas, CA Branch Discrepancies
SSN, Benefit, Payroll Ded, Database Deduction, Notes
(1) 876-00-8888, Cancer, 15.71, 6.98,
(2) 654-99-7777, Medical Plan 2, 0.00, 76.98,


magmike