sql problem

[bulrush]

Access 2003 on WinXP

Brief database layout:
MM is a master table linked to MD via a field GID. MD is a detail table with one or more records per MM record.

I'm generating an SQL string on-the-fly to be the source of a listbox control. The SQL statement uses aggregate functions so I can get the last job listed on a MM (master) record. After constructing the SQL I set the Listbox.Rowsource to the SQL string. Here is the string:

SELECT  MM.GID, MM.NameOfProduce1, MM.UPCPLU, MM.Foodcat, Max(MD.Jobnum) AS LastJob 
FROM MM LEFT JOIN MD ON MM.GID = MD.GID 
GROUP BY MM.GID, MM.NameOfProduce1, MM.UPCPLU, MM.FoodCat, LastJob HAVING ( 
(len(Jobnum)>0) and (MM.FoodCat='000 WetVeg') and (nz(MM.C3x5)>0) 
 )  ORDER BY MM.NameOfProduce1;

At the bottom of my form with the Listbox control I have a text box listing this sql statement for debugging purposes. So the form does not actually throw an error, but I get no records showing in the listbox.

Does anyone see anything wrong with the SQL statement?

 

[MarkK]

I would open the query designer, go to SQL view and paste in that query text and see what happens when you run it. The most likely cause by a long shot is that no records satisfy the criteria you specifed in the HAVING clause.
Maybe you need to remove constraints until you get records, and then add them back in one at a time until you understand the problem.
Did that help?
Cheers,

 

[bulrush]

1. I am not familiar with aggregate functions so any error I get in the query designer is meaningless to me. That's why I posted here.

2. Yes there are records that satisfy the critiera. But because I'm not an expert at aggregate functions I suspect my design of the SQL statement is incorrect, and that's why I posted here.

 

[MarkK]

OK, so what errors do you get in the query designer?

 

[boblarson]

Well, posting here is good but it is a back and forth. We do expect you do try what we've said, and lagbolt suggested something. So, you should go try it.

Copy your SQL and then go to a new query and don't add any tables to it. Just close the dialog that comes up. Then click on VIEW > SQL View and then paste your SQL into the box. Next, try going to DESIGN VIEW and see if it lets you. If not post back with the error and we'll go from there.

But the first thing I see is that you have a problem with your grouping:

SELECT  MM.GID, MM.NameOfProduce1, MM.UPCPLU, MM.Foodcat, Max(MD.Jobnum) AS LastJob 
FROM MM LEFT JOIN MD ON MM.GID = MD.GID 
GROUP BY MM.GID, MM.NameOfProduce1, MM.UPCPLU, MM.FoodCat, [COLOR=red][B]Max(MD.Jobnum)[/B][/COLOR]  HAVING ( 
(len(Jobnum)>0) and (MM.FoodCat='000 WetVeg') and (nz(MM.C3x5)>0) 
 )  ORDER BY MM.NameOfProduce1;

You can't use an alias in the GroupBy.

 

[MSAccessRookie]

Access 2003 on WinXP

Brief database layout:
MM is a master table linked to MD via a field GID. MD is a detail table with one or more records per MM record.

I'm generating an SQL string on-the-fly to be the source of a listbox control. The SQL statement uses aggregate functions so I can get the last job listed on a MM (master) record. After constructing the SQL I set the Listbox.Rowsource to the SQL string. Here is the string:

SELECT  MM.GID, MM.NameOfProduce1, MM.UPCPLU, MM.Foodcat, Max(MD.Jobnum) AS LastJob 
FROM MM LEFT JOIN MD ON MM.GID = MD.GID 
GROUP BY MM.GID, MM.NameOfProduce1, MM.UPCPLU, MM.FoodCat, LastJob HAVING ( 
(len([COLOR=red][B]Jobnum[/B][/COLOR])>0) and (MM.FoodCat='000 WetVeg') and (nz(MM.C3x5)>0) 
 )  ORDER BY MM.NameOfProduce1;

At the bottom of my form with the Listbox control I have a text box listing this sql statement for debugging purposes. So the form does not actually throw an error, but I get no records showing in the listbox.

Does anyone see anything wrong with the SQL statement?

I noticed that the Table Alias (MD) has been left off of the Jobnum Field in the HAVING statement. I do not believe that this should be causing any issues, unless both Tables have a Field named Jobnum, but I wanted to point it out in case.

 

[bulrush]

The problem was I was not supposed to use an alias in a HAVING clause. I'm still learning these aggregate "gotchas". Thanks for the help.