I am writing my first database app (windows form in c#) and I am using an access database.
I have 2 tables with the following fields:
payroll
--Store
--lastName
--PayFreq
--SSN (PK)
--HireDate
--EffectiveDate
--TermDate
--DeductionCode
--Deduction
--NAD CHAR
hrconnection
--Store
--lastName
--CoTime
--Plan
--Dependants
--HireDate
--EffectiveDate
--TermDate
--SSN (PK)
--Deduction
First two queries:
I want to find the records based on SSN that are in the payroll table and are not in the hrconnection tables and then find the record based on SSN that are in the hrconnection but are not in the paypal table
For these I came up with
SELECT hrconnection.Store, hrconnection.lastName, hrconnection.HireDate, hrconnection.EffectiveDate, hrconnection.TermDate, hrconnection.SSN, hrconnection.Deduction "
+ "FROM hrconnection LEFT JOIN payroll ON hrconnection.SSN = payroll.SSN "
+ "WHERE (((payroll.SSN) Is Null))";
and
SELECT payroll.Store, payroll.lastName, payroll.SSN, payroll.HireDate, payroll.EffectiveDate, payroll.TermDate, payroll.Deduction"
+ " FROM payroll LEFT JOIN hrconnection ON payroll.SSN=hrconnection.SSN"
+ " WHERE (((hrconnection.SSN) Is Null))";
They don't really come out right. So I thought I would post here for some help on the first queries and the following queries.
Next query:
I want to find data that does not match between hrconnection and payroll then output which fields do not match and list what record has the mismatch last name and SSN next to them.
Next query
I want to take all the records that do match 100% and are in both tables add the deduction field for all record and output a total deduction.
Next Query.
I want to output all records that match 100% and are in both tables that are within 45 days of the effective date and list how many days they are away from the effective date.
Any help on these queries would be great!
Thanks