MS Access and FileMaker...

Query Help

Please support our MS Access and FileMaker Pro advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Nov 2008

Posts: 4

Reputation: Shuco is an unknown quantity at this point

Solved Threads: 0

Shuco

Offline

Newbie Poster

Query Help

Nov 27th, 2008

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

Help with Code Tags

(Toggle Plain Text)

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

Help with Code Tags

(Toggle Plain Text)

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

•

Join Date: Mar 2007

Posts: 306

Reputation: timothybard is an unknown quantity at this point

Solved Threads: 26

timothybard timothybard is offline

Offline

Posting Whiz

Re: Query Help

Nov 27th, 2008

What are the results of the two queries and how does it not match what you are expecting?

http://www.timothybard.com
http://www.oliveirabard.com

•

Join Date: Nov 2008

Posts: 4

Reputation: Shuco is an unknown quantity at this point

Solved Threads: 0

Shuco

Offline

Newbie Poster

Re: Query Help

Nov 27th, 2008

The results I want is to show the mismatched records. It might do this , but does not look right as far as the first two queries and the others I am stuck on where to even go with them.