Data validation, a big project!

[tinyevil777]

Hey there,

Thanks for taking the time to help me thus far with all my questions.

Also, if this is in the wrong area, please feel free to move it Moderators. I put it in here as it covers queries, reports and SQL.

I have a new project under my remit, I’ll do my best to explain it as detailed as possible:

Tables:

1. POST
2. HOLD
3. SPECIAL
4. WEB

Fields:

POST
· Ref
· No
· Code
· Date
· Goods
· VAT
· Total
HOLD
· Ref
· Code
· No
· Date
· Goods
· VAT
· Total
· PO
· Del
SPECIAL
· Ref
· No
· Code
· PO
· Date
WEB
· Ref
· Name
· RefDate
· PO
· PODate
· Goods
REC
· Ord
· OrdDate
· Goods
· VAT
· Total

On a monthly basis, reports are generated from POS software and will be imported into the necessary tables. I need to run queries to validate certain data across a few tables. i.e.

Process 1: Do any records in “PO” in HOLD match any records in “PO” in SPECIAL.

Process 2: Do any records in “PO” in HOLD match any records in “ORD” in REC, if so, display an X in a report.

Process 3: Do any records in “PO” in HOLD match any records in “ORD” in REC, if so, validate the “GOODS”, “VAT” and “TOTAL” records against each other. I need a report to display every record in the criteria set, but then any record that “GOODS” and/or “VAT” and/or “TOTAL” doesn’t match, display an X.

There are other matching processes I need to consider, however these are the fundamentals, other processes will hopefully be implemented at stage 2 or 3 of development.


I’ve searched this forum to try and find what I need, however nothing hits the nail on the head. Thus far, I’ve (kind of) been able to return a report for Process 1 that loosely meets my requirements, it’s abit flakey though, as it only meets criteria that is 100% correct in a field. Sometimes the data from the SPECIAL report is messy, and may have a tilde in between several numbers. I believe if I use the “like” feature in a SQL select statement, I might be able to pick this up, but I don’t really have the know how at the moment.


Thanks for taking the time to read this, I hope someone can help me! This is a great project which I hope someone would like to help me with. If you need any other further info, please let me know!


Thank you in advance!


Tom