Comparing Tables

[magmike]

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

 

[Jeff Boyce]

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]

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.

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?

 

[Jeff Boyce]

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.

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.

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?

 

[magmike]

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

 

[Steve Sanford]

I've got an idea of what you want, ie a discrepancy report, but I'm not sure
what problem you need help with.

Is it importing the payroll data, comparing the data, creating the
query/report????

 

[magmike]

I've got an idea of what you want, ie a discrepancy report, but I'm not sure
what problem you need help with.

Is it importing the payroll data, comparing the data, creating the
query/report????

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

























































- Show quoted text -

Importing is no issue. Making the query to compare the data so it can
be used in one report is what I am struggling with. I am trying to
avoid making a query for each deduction field, because I am not
certain how to join them all into one report grouped by location code.

 

[magmike]

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 valuefor 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.

 

[Steve Sanford]

Do you have a query now?
What are the table names and field names of the tables?

Maybe you could provide some sample data and expected results??

 

[magmike]

Do you have a query now?
What are the table names and field names of thetables?

Maybe you could provide some sample data and expected results??

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)






- Show quoted text -

The description of the two tables along with fields are above in a
previous post in this same thread. And example of the report would be
as follows:

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

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

 

[magmike]

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

 

[Steve Sanford]

I see the table name "RawData" and it's structure (the database table I
think), but I don't see the table name and structure for the Payroll data.

Just curious.... do you have other fields?? Anything with payroll dates?

My approach would be to create a new FE (until I got this working - so I
didn't mess up the production FE) named "PayrollDiscrepancies". I would link
to the two tables "RawData" and the payroll data. Create a new table in the
FE named "tblDiscrepancies". This will be the data for the report.

The first step would be to zap the temp table "tblDiscrepancies".

db.Execute "DELETE * FROM tblDiscrepancies"


Then open (DAO) recordsets on the database table and a the payroll data
ordered by SSN.


Step thru the payroll recordset comparing the amount of the payroll benefit
to the database table benefit. If they are not the same, insert a record into
"tblDiscrepancies".

If the SSN is not in the database (using the .NoMatch property) then insert
a record into the table "tblDiscrepancies" with the note "Employee not in
database".

After you have reached the EOF of the payroll recordset, then do a find
unmatched query comparing the database table to the payroll table. If the SSN
is not found in the payroll tabel, insert a record into the table
"tblDiscrepancies" with the note "Employee not on payroll".

If you wanted the records in the report to always have "MedPlanDed" as the
first record of everyone, add a column "BenOrder" and insert 1 when you
process "MedPlanDed", 2 when you process "CorpMedPlanDed",....

The query for the report would be grouped by "Location", "SSN", and
"BenOrder", sorted asc..

HTH