Run 1 query if results from another query meet requirements (1 Viewer)

[tinyevil777]

Hey there,

The title might not make much sense, but thanks for viewing, i'll do my best to explain with as much detail as possible.

I am currently creating a database that has two tables.


Hold

• OrderNo
• Total

Goods

• OrderNo
• Total

I have already created a query that validates the OrderNo in Hold, against the OrderNo in Goods. What i want to now do, is run another query on all those records where the OrderNo matches!

Reason? It's a matching procedure.

Pretty easy i'm sure, i just don't know quite how to do it.

Thank you in advance for your replies and help, it really is appreciated!


Tom

 

[Brianwarnock]

Provided you join the tables on order number then the query will only select data from records where they match, a normal inner join that is, which is the default.

Brian

 

[tinyevil777]

Hi Brian, thanks for your reply.

Yes, that's what i found when i was testing the queries. Is there a way around this?

Is there a way of checking the Total field in Hold, if the OrderNo in Goods and Hold match? And if OrderNo in Goods and Hold do NOT match, then don't check the Total field?

 

[Brianwarnock]

I'm confused, if the order numbers don't match the records wont be selected and the Total wont(cant) be checked.

I feel that i'm not getting the full story but I'm off out now and wont be back till much later.

Brian

 

[tinyevil777]

I have uploaded my test db for your persual.


The tables of interest here are GRN and Hold.

The \pq query is populating the pqreason field with an X, if the criteria is met. It is the criteria in the query that is incorrect. Currently, if the OrderNo's don't match, then it is STILL populating the pqreason field. It should only be populating the pqreason field if the OrderNo's have matched, and the Total's between each record are not the same.

Does what i'm saying make sense?

 

[tinyevil777]

I've resolved this now, if you add in "<>[grn]![Total]" into the Criteria for the total, it will perform the action for all OrderNo's that match, but Total's differ. And if the OrderNo's don't match, it will skip it!

Because this is doing a "to the penny" check on the totals, would there be a way for me to add a £0.01 exception either way to the total validation?

Thanks for your help Brian by the way.