Unmatched record lookup

[MICKI0220]

I have a table that has 3 fields. This table lists the combinations of earning codes/depat/phases that are correct for payroll. I need a form, query or report that will match this table with the transactions table to make sure that a data entry person has not input the combinations incorrectly....So what I am asking it to do is to link these three fields in each table to look for a match.

The psuedo code would be something like this

loop each record to verify match.

If transaction.dept <> a dept record in the static table then do nothing and look at next record,
then look at the next field which is phase and see if the dept and phase combo do not match....if they don't add the whole record from the transaction table to a results list
if the combo dept and phase match, then look at the earnings code, if they don't match a record combo in the static table, the entire transaction record to a resulsts list.....
Show the results lists in a form or query
end process

I hope someone can help me with this.

Micki

 

[RuralGuy]

Are you doing validation on data entry?

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 ac10 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit

 

[TheAceMan1]

How are ya MICKI0220 . . .

Your comparsion logic is not quite making sense. You say:

[blue]If transaction.dept <> a dept record in the static table then do nothing and [purple]look at next record[/purple],
then look at the next field which is phase and [purple]see if the dept and phase combo do not match[/purple][/blue]

Hmmm ... compare one field in one record then two in the next record. Apparently one or both tables are not normalized. I was expecting each record in the static table to represent a combination. This doesn't seem to be so.

Whats needed is a view of some typical values in this static table ... along with its tablename.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MICKI0220]

TheAceMan1

This is what the table looks like

Dept Phase PayCode

CONBEN CF50 P07
CONFND CF10 REG
CONFND CF10 OVT
CONFND CF10 DBL
FHOL HOL
FHOL P01

So, given that my static table looks like this, with many more entries, when a person enters a dept, if that dept is in this table then it must be combined with one of the combinations of dept/phase/paycode listed as a record....if it isn't I need it to either flag as an error or just add it to a record set that the person will see at the end of the file loop as in as a results list. I hope that makes more sense. To clarify the non static table better, it is a temporary table that stays there until they post the payroll. So before they post the payroll, they will run this little check.

 

[MICKI0220]

RuralGuy......

Yes, I am doing a validation of data entry.

 

[MICKI0220]

Okay here is the complete table, I want to verify the input table against. Basically, three of the fields in the temp table need to match up completely, meaning that the dept/phase/paycode, must be equal to one of these records across the board....if the combination is incorrect (meaning the combination is not found as a record in this table) then I need to let the user know that they keyed in a wrong combination. The matching fields in the temp table are dept = szDepartmentId_tr.....Phase = szSearch_key...Paycode = szEarnCode_tr...The tables name is PrePostComp. I hope this explains is better. I am even trying Case statements, but I am not that strong in Case.

DEPT PHASE PAYCODE

CONBEN CF50 P07
CONFND CF10 DBL
CONFND CF10 HOL
CONFND CF10 OVT
CONFND CF10 REG
CONPED CP10 DBL
CONPED CP10 HOL
CONPED CP10 OVT
CONPED CP10 REG
CONSUB CF40 P01
CONTRH CF20 REG
CONTRK CF45 P02
CONTRM CF30 P03
CPDBEN CP50 P07
CPDSUB CP40 P01
CPDTRH CP20 REG
CPDTRK CP45 P02
CPDTRM CP30 P03
FHOL HOL
FHOL P01
FIELD FC10 DBL
FIELD FC10 HOL
FIELD FC10 OVT
FIELD FC10 REG
FLDBEN FC50 P07
PAINT FP10 DBL
PAINT FP10 HOL
PAINT FP10 OVT
PAINT FP10 REG
PNTBEN FP50 P07
PNTSUB FP40 P01
PNTTRH FP20 REG
PNTTRK FP45 P02
PNTTRM FP30 P03
SUB FC40 P01
TRAVHR FC20 REG
TRAVMI FC30 P03
TRUCK FC45 P02

 

[TheAceMan1]

MICKI0220 . . .

What you need is a function that tells you if the form fields match the static table. The function would open a recordset, based on a query/SQL that makes the comparsions ... and return true or false as to wether a match occured. I believe I can give you this SQL, howerevr to makes things easier code wise ... you stil havn't given the name of the static table!?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MICKI0220]

The static table is called SafetyCheck

 

[TheAceMan1]

MICKI0220 . . .

Sorry to get back so late.

I have a query ready for this, however I'm still a little confused on the table names. You've mentioned the following tables:

[blue]Transactions
Temp
Static
PrePostComp
SafteyCheck[/blue]

I'm not quite sure who's who. my current guess is:

[tt][blue]rePostComp.DEPT & TempTableName.szDepartmentId_tr
PrePostComp.PHASE & TempTableName.szSearch_key
PrePostComp.PAYCODE & TempTableName.szEarnCode_tr[/blue][/tt]

Please correct the above please. I'll also need the primarykey field names.

[blue]Your Thoughts? . . .[/blue]


See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MICKI0220]

safetyCheck.Phase .....should = PrePostComp.szSearch_key
SafetyCheck.Dept ....should = PrePostComp.szDepartmentId_tr
SafetyCheck.PAYCODE ....should = PrePostComp.szEarnCode_tr

Thank you

Micki

 

[TheAceMan1]

MICKI0220 . . .

... and the [blue]PrimaryKeyNames[/blue] ?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

C'mon MICKI0220 . . .

... and the PrimaryKeyNames are?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

You may try this sort of query:

SELECT T.szDepartmentId_tr, T.szSearch_key, T.szEarnCode_tr
FROM PrePostComp T LEFT JOIN safetyCheck S
ON S.Dept = T.szDepartmentId_tr AND S.Phase = T.szSearch_key AND S.PAYCODE = T.szEarnCode_tr
WHERE S.Dept IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

Howdy PHV . . .

I came up with the same thing using the [blue]Find UnMatched Query Wizard[/blue]. However ... since there's no way to identify a record, I just wanted to include the [blue]primarykey[/blue]

[blue]SELECT [purple][b]PrimaryKeyName[/b][/purple], T.szDepartmentId_tr, T.szSearch_key, T.szEarnCode_tr ...[/blue]

Don't know what happened to [blue]MICKI0220[/blue] ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MICKI0220]

sorry, I am in the middle of a conversion and this got put on the back burner....my primary keys are a combination of the department and the earning code.

 

[PHV]

So, the query I've suggested 9 Sep 11 23:10 should suffice, doesn't it ?