Joins to get records greater than

[thart21]

I am trying to do a simple append query that pulls data from one table in which the "group_no" is > the same "group_no" field in another table. Both data types in the tables are set at Number, Double. I get no results when running the query and cannot figure out why that might be.



Table_1 Table_2
group_no gp_name group_no gp_name

100 Blue 100 Blue
200 Red 200 Red
300 Green 300 Green
400 Yellow 400 Yellow
500 Purple 500 Purple
600 Black
700 Orange
800 Teal
I need the query to pull group_no 600-800 only and have joined the tables on group_no with criteria for that field "> [Table_1].[group_no]" This should work but I must be missing something simple, would appreciate some insight.

Thanks

[RuralGuy]

Post your SQL view for us to see please.

[weekend00]

not sure what field you join on.
but criteria should be: (table_2.group_no is null) or (table_1.group_no>table2.group_no)

[thart21]

Joined on MO_MODEL_OFFERING_ID. Changed datatypes to both show Long Integer. I still get no data at all. If I enter the Max MO_MODEL_OFFERING_ID that is in the "tFcst_Wkbd_Step4D...." table i.e. criteria " > 116747", I get the data that I need.

SELECT Table1.MO_MODEL_OFFERING_ID, Table1.MO_SEASON_CD, Table1.MO_SEASON_YEAR
FROM Table1 LEFT JOIN tFcst_Wkbd_Step4D_Ready_to_Parse_with_FLM ON Table1.MO_MODEL_OFFERING_ID = tFcst_Wkbd_Step4D_Ready_to_Parse_with_FLM.MO_MODEL _OFFERING_ID
WHERE (([Table1].[MO_MODEL_OFFERING_ID]>[tFcst_Wkbd_Step4D_Ready_to_Parse_with_FLM].[MO_MODEL_OFFERING_ID]))
GROUP BY Table1.MO_MODEL_OFFERING_ID, Table1.MO_SEASON_CD, Table1.MO_SEASON_YEAR;

Thanks!

[weekend00]

Please consider the criteria I gave on post #3

[thart21]

I used the 2nd part of your suggested criteria. I can't use the IS NULL because there will be records in table2 that will have a NULL MO_MODEL_OFFERING_ID field which is ok. I just need the ones where the field in Table1 is greater than the same field in Table2. Adding the IS NULL criteria is giving me the entire tables records. I've tried to use the query wizard unmatched, adding this criteria with the same results. Thanks for your help.

[weekend00]

It will not give you the entire table. but only the ones without matching on join fields and the ones have group_no1 >group_no2


your criteria:
WHERE (([Table1].[MO_MODEL_OFFERING_ID]>[tFcst_Wkbd_Step4D_Ready_to_Parse_with_FLM].[MO_MODEL_OFFERING_ID]))
compares offering_id, not group_no

[thart21]

group_no and that scenario was just what I made up to explain what I was trying to do. The SQL that I posted is my actual field names.
I don't want records to be pulled that "don't have matching" records in table1. I only want the ones in table2 that are greater than the MAX .."offering_id" number in table1. For example (Sorry, I could not get the columns to space properly)
Table2 Table1
MO_OFFERING_ID MO_OFFERING_ID
1
2
3
4 4
5 5
6 6
7
8
9
10

I need to pull from Table2 - 7,8,9 and 10 and do not want 1,2,3. I hope this makes it more clear. By using the IS NULL criteria, it is pulling 1,2,3. Please let me know if I'm not explaining clearly enough. I can't figure out why it just won't give me all records where MO_OFFERING_ID in Table2 is > MO_OFFERING_ID in Table1. Thanks for sticking with me on this one. It seems simple and I know I've done it before. Do I need to use some type of MAX function?

Toni

[weekend00]

Do you mean that you want ID from tableA which is greater than or equal to any ID in tableB?

In this case, you can not use a join query. Please try this:
select distinct tableA.* from tableA,tableB where tableA.xxID>=tableB.xxID

[thart21]

I just need the id from tableA that is greater than the id in tableb, I don't want any that are equal, just greater than. So, in my previous example, if tableA has 1,2,3,4,5,6,7,8 and tableb has 4,5,6. I only want to pull from tableA id#'s 7 & 8. I do not want 1,2 or 3. I will try your suggestion, thanks for the help!

[weekend00]

only greater than is different. you mean greater than any ID in tableB right?

wait a moment, i will work it out for you.

SELECT tableA.* FROM TableA left JOIN TableB ON TableA.ID = TableB.ID
WHERE (((TableB.ID) Is Null)) and tableA.ID > (select min(ID) from tableB)