If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Comparing Tables
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 |
#3
|
|||
|
|||
Comparing Tables
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? |
#4
|
|||
|
|||
Comparing Tables
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? |
#5
|
|||
|
|||
Comparing Tables
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 |
#6
|
|||
|
|||
Comparing Tables
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. |
#7
|
|||
|
|||
Comparing Tables
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 |
Thread Tools | |
Display Modes | |
|
|