Access 2010 SQL query does not group

I am working on a MS Access 2010 Database and I have a ODBC connection to a table.  The table is setup as a linked table.  I am using VBA to bring in some data for sales orders based on the shipment-id.  The table that I am getting this information has all the line items for each order, so I get many lines for each order.  I am trying to group the data so that I only get each order number once.  However I am having some difficulty getting this to work.  I don't understand why my query will not group the data.  Here is my query syntax:

                      "SELECT HIS00SCH_SHIPFIL.HSHPNO, HIS00SCH_SHIPFIL.HSONUM " & _
                      "FROM HIS00SCH_SHIPFIL " & _
                      "WHERE HIS00SCH_SHIPFIL.HSHPNO = '" & strQryNbr & "' " & _
                      "GROUP BY HIS00SCH_SHIPFIL.HSONUM,HIS00SCH_SHIPFIL.HSHPNO;"

What am I doing wrong when I execure this?  Thank you for you help!
Wade PrestonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
hmm, it does group your data. The question is : what data do you get and what have you expected?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I am trying to group the data so that I only get each order number once.
If your table contains multiple lines with the same OrderNumber, then you will have a difficult time doing this in a query, and I would question the usability of a query that could do this.

What's your ultimate goal? Are you trying to create a Report that shows this? If so, create two reports - one that contains the OrderNumber, and a second that contains all Shipment lines. Embed the second report into the first one, and make sure the "link" fields are set to OrderNumber.

When you view that report, you should see each OrderNumber listed one time, and the subreport for each OrderNumber should show you all lines associated with the ORderNumber.
0
Wade PrestonAuthor Commented:
The goal of this is to compress the records down to one unique sales order number with the shipto number.  Here is an example of the results I get from this query:

HSHPNO      HSONUM
ABQ032          15114581  
ABQ032          15118213  
ABQ032          15118213  
ABQ032          15122902  
ABQ032          15122903  
ABQ032          15122903  
ABQ032          15122903  
ABQ032          15122903  
ABQ032          15122903  
ABQ032          15122903  
ABQ032          15123296  
ABQ032          15123296  
ABQ032          15123296  
ABQ032          15123296  
ABQ032          15123296  
ABQ032          15123296  
ABQ032          15123296  
ABQ032          15124698  
ABQ032          15124701  
ABQ032          15124701  
ABQ032          15124701  
ABQ032          15124701  
ABQ032          15124701  
ABQ032          15124701  
ABQ032          15124701  
ABQ032          15124701  
ABQ032          15124701  
ABQ032          15124701  
ABQ032          15124701  
ABQ032          15127109  
ABQ032          15127109  
ABQ032          15127109  
ABQ032          15127109  
ABQ032          15127109  
ABQ032          15127109  

As you can see in the right hand column, I have multiple sales order numbers that are not unique.  I can run this same type of SQL statement against a SQL server and it works fine.  I don't care about the sales order lines.  I only want the sales order numbers.  I am dropping the part number, qty, etc. as I only need to get the sales order numbers from this table so I can get the correct data from another table that does not have the shipto number.  I hope that makes more sense.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ste5anSenior DeveloperCommented:
Check the data type of HSONUM in the linked table definition.

I guess you'r expected result is:

HSHPNO              HSONUM
 ABQ032              15114581  
 ABQ032              15118213  
 ABQ032              15122902  
 ABQ032              15122903  
 ABQ032              15123296  
 ABQ032              15124698  
 ABQ032              15124701  
 ABQ032       15127109
0
Wade PrestonAuthor Commented:
Yes, that is the result that I am looking for.  What am I looking for in the data type?  The type for both fields are text.  I am not sure what you are getting at with this, please explain.
0
ste5anSenior DeveloperCommented:
Maybe that is already the problem. GROUP BY does not remove spaces. Try

"SELECT Trim(S.HSONUM) AS HSONUM, Trim(S.HSHPNO) AS HSHPNO " & _
"FROM HIS00SCH_SHIPFIL S " & _
"WHERE S.HSHPNO = '" & strQryNbr & "' " & _
"GROUP BY Trim(S.HSONUM), Trim(S.HSHPNO);"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
It is possible that there are invisible trailing characters that are getting in the way.  Try changing the query to this:

"SELECT HIS00SCH_SHIPFIL.HSHPNO, Left(HIS00SCH_SHIPFIL.HSONUM, 8) " & _
                       "FROM HIS00SCH_SHIPFIL " & _
                       "WHERE HIS00SCH_SHIPFIL.HSHPNO = '" & strQryNbr & "' " & _
                       "GROUP BY Left(HIS00SCH_SHIPFIL.HSONUM,8),HIS00SCH_SHIPFIL.HSHPNO;"
0
Wade PrestonAuthor Commented:
To: PatHarman, thanks for the post.  The Left function will not work as I could have 7 or 8 characters for the order number.

To: ste5an,  I have tried the trim function and when I do, I get the  error message "ODBC--call failed."  Do you know what that is all about and how do I fix it?  I believe that you are correct that the extra characters may not work.
0
PatHartmanCommented:
I suggested the Left() rather than Trim() because Trim() only gets rid of ZLS characters and you may have other values.  Run a query that does a Len() and select anything with a length > 8 or < 7 to see if there is actually something that Trim or Left needs to remove.
0
Wade PrestonAuthor Commented:
I tried the selection criteria as you suggested to see if I get anything with a length greater than 8 or less than 7 and nothing is selected when I run it that way.  That being said, I don't believe that trimming the data is going to help.  Would it have anything to do with both of the data fields being indexed?

Is there a way to put the data into a record set and then remove the duplicate lines? (Just trying to think of ways around this.)
0
PatHartmanCommented:
If the lengths are consistent then there is nothing to trim.  Try deleting the existing query, compacting the database and then recreating it.  It is possible that the query is corrupted.
0
Wade PrestonAuthor Commented:
One of our requirements for this database changed and therefore this query is no longer necessary.  Thanks to all who participated in this discussion.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.