Greater than?

[spoole]

Hi everyone!! I need some advice regarding the following query:
This is a big database with many tables and queries etc. but for simplicity lets say i have database with 2 tables (table1 and table2) both contain invoice numbers. table2 contains one record while table1 contains many. Ok simple so far but here comes the question, how can I write a query that selects all records from table1 that are greater than the value in table2, hope that makes sense? I've been trying to slog this out on my own but dont really know where start so any help will be appriciated
Thanks Steve.

 

[CraigDolphin]

Add both tables to your query and make sure they are joined by the invoice number field.
Add the field with values from table 1 to the grid. In the criteria row of the design grid for that field, place > [Table2]![ValueField]

Substituting the name of your table and field name accordingly.

 

[spoole]

ahhh thank you i did try that but didnt join the tables. Been working too much on As400 query today to see the simplest of answers in access

 

[Darren Bartrup]

Hi spoole,

Enter something along these lines into the SQL view of your query:

SELECT inv1
FROM Table1, Table2
WHERE inv1>inv2
ORDER BY inv1;

inv1 is in Table1 and inv2 in Table2.

Edit: To busy getting a user name and missed that it had already been answered. I wouldn't think you'd need a join if Table2 only has one record - more than one record and you get a cartesian product.