<this field> is less than <another field>

[kodamawu]

i'm trying to make comparisons between different fields, and i'm trying to make a query that only displays where field 1 is less than field 2, but i can't figure out how to input names for it to compare, only raw data.

 

[homage]

Re: [this field] is less than [another field]

I have qryA and tbl1 that do not have any joins. tb1 only has one field so I don't think there's a problem with that. I'm trying to get the solution to only show results when qryA.[this field] is less than tbl1.[another field]. This is what I put

SELECT qryA.Date, CountOfqryA.Emp,qryA.Hrs
FROM qryA.Hrs
WHERE qryA.Label="Labor"
GROUP BY qryA.Date,qryA.Hrs
HAVING qryA.Hrs>0 and qryA.Hrs<tbl1.PT_Hrs

It's asking me to the Enter Parameter Value for tbl1.PT_Hrs.

I also tried

SELECT qryA.Date, CountOfqryA.Emp,qryA.Hrs
FROM qryA.Hrs
WHERE (qryA.Label="Labor"), qryA.Hrs<tbl1.PT_Hrs
GROUP BY qryA.Date,qryA.Hrs
HAVING qryA.Hrs>0

but that gave me a syntax error.

 

[plog]

1st Query: You can't reference a field from a datasource (query or table) that query has never heard about. You need to tell the query how this datasource fits in by either including it in the FROM clause or in a JOIN clause. My guess is your HAVING clause should be your INNER JOIN clause.

2nd Query: Your WHERE clause isn't formed correctly. I assume the comma means you want both criteria to be applied. Instead of a comma you use the AND keyword. Of course since tbl1 isn't referenced in the FROM or JOIN clause you are going to be right where you were with the 1st query.



Now when those fixes make your queries work, but not produce the data you expect, post back here sample data--including starting data, what you are getting and what you expect.

 

[homage]

Yes, I just needed the "And" and it worked!
There is no join.

Thanks you!!