Thread: Any SQL GURUS or Acess Gurus here? Please

Originally Posted by FUD

If I add DISTINCT I get an error of missing operator

If I move it to the FIRST Item then it still provides multiple customer ID entries.

I think you'll have to put an "ORDER BY tblCustomers.cuID" at the end of the query - DISTINCT probably requires the elements it's distincting on to be adjacent in the result set.

This means the result set will be in the wrong order, but you should be able to save this as a view, then query the view and use that query to put things in the right order. Assuming Access can handle this level of SQL. Failing that, use your application to put it in the right order, or move to a real SQL database.

Other problems which are hopefully easy:

I'm having are converting 2 numerical fields to strings and concatenating them in the query output IE "chknum1" and "chknum2" are numerical but I need to see something like "110, 111" where 110 is chknum1 and 111 is chknum2

I am adding 2 fields but sometimes one of the fields is empty (null) and when that happens it's returning a null result. IE I'm adding chkAmt and chkAmt2, sometimes there's only 1 check so chkAmt2 never gets touched, leaving it with a null character in it. when the query comes across this it assigns NULL to the output field in the query.

Originally Posted by FUD

I am adding 2 fields but sometimes one of the fields is empty (null) and when that happens it's returning a null result. IE I'm adding chkAmt and chkAmt2, sometimes there's only 1 check so chkAmt2 never gets touched, leaving it with a null character in it. when the query comes across this it assigns NULL to the output field in the query.

null values are black holes - anything that comes near them turns into null. To fix this, you'll need to use a conditional expression for the second value. I don't remember the exact syntax, but something along the lines of CSTR(chkAmt2)+IIF(ISNULL(chkAmt2), '', ', '+CSTR(chkAmt2)).

Ahh, the rust comes off. After looking at your query for a bit, I have decided what I would do if I were writing this from as ASP perspective. This might help you in Access or it might not, because of two things...1) I don't know how to write for the access frontend and 2) I don't know if my "elegant solution" will work for Access.

Dirty solution:
append GROUP BY UserID to the end of your select statement.

set rs = currentdb.openrecordset()
dim lastID = -1 'or whatever will never be used as a customer ID
While NOT rs.EOF
if lastID <> rs.UserID then
response.Write
lastID = rs.UserID
end if
rs.MoveNext
End While

Elegant Solution: I was imagining in my head that I would use SELECT INTO and then do a distinct on the temporary table it produced, but that still doesn't really solve the problem.

The real problem is that select distinct is only really meant to answer questions like "How many unique flavors of ice cream have been purchased by the last 5000 customers." SELECT DISTINCT ice_cream_flavors FROM tblOrders. So it looks like something you'd want to solve in code if you don't know how to make Access handle the resulting data from myour query the way you want. Unfortunately, I don't know how to do that for you, since I dumped all of my SQL data into HTML / XML / Javascript.

Originally Posted by mparker762

I think you'll have to put an "ORDER BY tblCustomers.cuID" at the end of the query - DISTINCT probably requires the elements it's distincting on to be adjacent in the result set.

ORDER BY . . . . doesn't make a difference, it still returns multiple cuID recordsets.

I'd love to switch to a true SQL database but I don't have the applications or knowledge to do it and it would only be for getting this single CSV exported like I need. I took on this task based on the new software vendor's claims that this would be a 1 hour project tops. I've already spent over 36 hours on the thing already .

Why don't you care about the other records that are coming back? Is it really sufficient to return some random record for each cuID?

I think tomlinAS is right: distinct is returning each distinct record, not uniq'ing on just the one field, so it isn't likely to work the way you want. You'll need to do a GROUP BY with some innocuous aggregated column - if you don't care which cuID record comes back maybe you can aggregate using MAX(tblLoan.inAmt)?

I was unable to connect for a while.

Basically what is happing is this:

This is an Access database where the coders tried to do more than access can handle. A new program was purchased to take over the workload and all the customer information needs to be imported into the new program.

I have easily and successfully pulled out all active loans with only a couple hiccups (the driver's license field sometimes returns the value ABOVE or BELOW for some reason)

Now I need to pull all the OTHER customers' information out to be imported. My first step was to try to get some type of expression written that would avoid duplicate customer ID's. If I was able to do this I was then going to take it a step further and add further criteria that the cuID NOT match any cuID where the LoanStatus was active ("A")

With the hiccups tonight and my headache I think I'll head out for now. Thanks for all the help. I'll keep browsing my books and hope that something jumps out at me that I've possibly missed.

Glen F

From what you've told me it sounds like your query is trying to pull out too many fields that have nothing to do with the customer - if you're getting back multiple records per customer then something you're bringing back isn't "customer" information, it's "loan" information or some such -- this is why you can even distinguish the various result records from each other, there's something else in those records that's cluing you in.
If you get rid of those extraneous columns (so that all the records for a given customer are identical) then the distinct will probably work just fine.

Unfortunately, this is one of those places where access really bites you in the *ss, it's not really powerful enough for serious sql hacking, but just powerful enough to let you get into situations where you *need* serious sql hacking.

You're exactly right. I'm pulling a total of 75 fields from 5 tables. While only 20 to 30 are directly from the customer field, the new program requires valid information from the other tables as well in order to succesfully import. It's very particular. if a leading zero was dropped on a numerical value during the process of converting the query to a csv the new program craps out and won't finish the import.

What I'm going to do is try to save the active loans as a view then do a 2nd query to get all the nonactives and use the view to prevent any non-actives that have actives. then I'll manually delete all redundant entries in the excel analysis. Then I have to find a way to get all the leading zeros back in and I'll be set.

Everything else you've offered, the comparisons, isnulls etc. have been on the money. The rest is like you say, Access doesn't allow enough sql to do what I need.

Thanks.

Glen F

Have you tried a GROUP BY cuID with an innocuous aggregator like MAX or MIN on some numerical field?