Any SQL GURUS or Acess Gurus here? Please

Don't know if anyone can answer this for me or not but figured I'd try.

I have built a query using the graphical interface in an access database. essentially the query selects Tons of fields from 5 tables within a relationship. I want to SELECT DISTINCT on the third field (which comes from a different table than the first two). It has to display third within the query results. Unfortunately the first field is the unique identifier and the Third field, which is the customer ID number will show repeats. basically I want a single record from each customer with closed transactions but it has to be in a specific format with fields from all 5 tables I'm using

Any suggestions? I've already tried adding [SELECT DISTINCT table.field] in between the commas of the parent select statement but it gives me an error when I try to run the query of ulawful use of brackets. () gives me syntax error when trying to save the query.

Any help is appreciated. Thanks.

Glen F

I did SQL 2000 for a couple of years, stuff like this. Couple of questions:

1) Is this actually a SQL database or is it an access database? Access doesn't support SQL very well.

2) If it's SQL, could you post the tables in question with some dummy data?

If it's access I'm afraid I can't help you. When I was hard coding in access for a business I worked for after WSU, I actually got an error once that stated "This SQL function not supported in Access." Hah. It was a right outer join or other specific join, IIRC.

Edit the SQL directly and make it do what you want. The GUI is easy but annoyingly limited.

Quote:

---

1) Is this actually a SQL database or is it an access database? Access doesn't support SQL very well.

---

It's an Access 2002 mdb which exclusively uses SQL for queries. The graphical query builder is simply a front end for "standardized" SQL expressions.

Quote:

---

2) If it's SQL, could you post the tables in question with some dummy data?

---

Here's a gob of SQL for you right out of the SQL window from the DB (including inner joins etc ;).

SELECT tblLoan.lnID AS LoanID, tblLoan.lnTypeLoan AS LoanType, tblCustomers.cuID AS UserID, tblLoan.lnStatus AS LoanStatus, tblLoan.lnAmt AS LoanAmount, tblLoan.[lnCheckAmt]+[lnCheckAmt2] AS CheckAmount, tblLoan.[lnChkAmt]+[lnChkAmt2]-[lnAmt] AS Expr2, tblLoan.lnAPR, tblLoan.lnStDate, tblLoan.[lnstdate]+[lnperiod] AS Expr3, tblLoan.lnPeriod, tblBank.bkName, tblBank.bkPhone, [FutureField] AS Expr4, tblCustomers.[cuFname]+" "+[cuLname] AS Expr5, tblBank.bkRouteNum, tblBank.bkChkAcctNo, tblLoan.lnChkNo, [OtherBankInfo] AS Expr6, [LoanNotes] AS Expr7, [DateCreated] AS Expr8, [DateModified] AS Expr9, tblCustomers.cuFName, tblCustomers.cuMName, tblCustomers.cuLName, tblCustomers.cuSName, tblCustomers.cuAddr1, tblCustomers.cuAddr2, tblCustomers.cuCity, tblCustomers.cuState, tblCustomers.cuZip, tblCustomers.cuEmail, tblCustomers.cuHomePh, tblCustomers.cuOtherPh, tblCustomers.cuSSNo, tblCustomers.cuDOB, tblCustomers.cuDriversLic, tblCustomers.cuDriversExp, tblCustomers.cuState, [StateID] AS Expr10, [StateIDstate] AS Expr11, [StateIDexp] AS Expr12, [MilitaryID] AS Expr13, [MilitaryIDexp] AS Expr14, [CurrentAddressSince] AS Expr15, [RentOwn] AS Expr16, tblCustomers.cuLandLord, tblCustomers.cuLandLordPhone, [OtherIncome] AS Expr17, tblEmp.empName, tblEmp.empAddr1, tblEmp.empCity, tblEmp.empState, tblEmp.empPhone, [OtherIncome2] AS Expr18, [HireDate] AS Expr19, [PayCycle] AS Expr20, tblEmp.empDirectDepYes, tblEmp.empGrossSalary, tblReferences.[FName] & " " & [Lname] AS Expr21, tblReferences.Relation, tblReferences.HomePhone, [Ref1Other] AS Expr22, tblReferences.[Ref2FName] & " " & [Ref2Lname] AS Expr23, tblReferences.Ref2Relation, tblReferences.Ref2HomePhone, [Ref2Other] AS Expr24, [BestTimeToCall] AS Expr25, [BestPhoneNumber] AS Expr26, tblCustomers.cuCampaign, tblCustomers.cuReferredBy, [UserProfileCreated] AS Expr27, [UserProfileModified] AS Expr28, tblCustomers.cuComments, [CustomerStatus] AS Expr29, [UpdateOnly] AS Expr30
FROM (((tblCustomers INNER JOIN tblBank ON tblCustomers.cuID = tblBank.bkCuID) INNER JOIN tblEmp ON tblCustomers.cuID = tblEmp.empCuID) INNER JOIN tblLoan ON tblCustomers.cuID = tblLoan.lnCuID) INNER JOIN tblReferences ON tblCustomers.cuID = tblReferences.refCuID;

Told you it was a bear. the tblCustomers.cuID close to the beginning is the one I want to do the qualifier for weeding out repetitive records. Online examples show that I should be able to replace the "Userid:tblCustomers.cuID" with Userid:(SELECT DISTINCT tblCustomers.cuID)

Thanks for your efforts.

Glen F

Quote:

---

Originally Posted by mparker762

Edit the SQL directly and make it do what you want. The GUI is easy but annoyingly limited.

---

Yea I'm trying to but it's giving me errors.

So if you change the "..., tblCustomers.cuID AS UserID, ..." to "..., DISTINCT tblCustomers.cuID as UserID, ..." what syntax error do you get?

And what happens if you move that to be the first column in the select clause (e.g. "SELECT DISTINCT tblCustomers.cuID AS UserID, tblLoan.lnID AS LoanID, tblLoan.lnTypeLoan AS LoanType, ...")?

This is the service I am offering if provided with printouts of the tables. :)

Order now, as the offer's only good for another hour and 30 minutes. Offer will be reissued in 16 hours, when my internet access time returns. Offer subject to arbitrary disruption by third parties in turbans. ;)

Gah, that post took 15 minutes to...post. Don't know why.

Can you list the actual fields in each table, and then explain to me in English what you are trying to select? Trying to hack through that statement is terrible. (Alternately, if you can tab format it that would work too, but I think the tables would work better).

Quote:

---

Originally Posted by mparker762

So if you change the "..., tblCustomers.cuID AS UserID, ..." to "..., DISTINCT tblCustomers.cuID as UserID, ..." what syntax error do you get?

And what happens if you move that to be the first column in the select clause (e.g. "SELECT DISTINCT tblCustomers.cuID AS UserID, tblLoan.lnID AS LoanID, tblLoan.lnTypeLoan AS LoanType, ...")?

---

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. IE the DISTINCT doesn't seem to be switching on the cuID field. Plus if it did work it would be out of order and I'd have to export to exel in order to manually move the field back so It would be in proper order for exporting to CSV. Unfortunately excel drops leading zeros in numerical values that I need them in and doesn't have an easy way to prevent that, which causes major problems during data validation when the CSV is being imported to the new program.

Quote:

---

Originally Posted by TomlinAS

Gah, that post took 15 minutes to...post. Don't know why.

---

I was out quite a few minutes also. I probably missed you. Sorry. I could have used the extra help too. :(

Quote:

---

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.

Quote:

---

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(<heinous select string here>)
dim lastID = -1 'or whatever will never be used as a customer ID
While NOT rs.EOF
if lastID <> rs.UserID then
response.Write <heinous amount of selected variables>
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.

Quote:

---

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?

Quote:

---

Originally Posted by mparker762

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

---

No that's going beyond my skills. I did pick up 2 more sql references that I'm going through trying to figure it all out ;).

Thanks.

Glen F

OK, here's how it works:

Say you've got a table (call it Table1) with two columns (name, value), with the following records:

name value
foo 1
foo 2
foo 3
bar 4
bar 5
bar 6


If you just want to get one record for 'foo' and one record for 'bar' then you use GROUP BY with an aggregator that tells hime what to do with the group. Normally this is used for things like reports where you want to calculate an average or something like that, but you can also use selection-type aggregates like MIN or MAX.

So if you only want to get one record for each name, then "SELECT name, MAX(value) FROM Table1 ORDER BY name GROUP BY name" and you'll get the result set:

name value
foo 3
bar 6

handed back to you.

In your case your query is a lot more complicated but you may be able to make it work. You'll have to make each non-grouped column an aggregate function though, so put each customer field in the "group by " clause and use an aggregate function for each of the non-customer fields. If it doesn't matter what value is in those fields but they have to be in the result set, then try selecting an innocuous literal "SELECT name, MAX(value), 0 AS loanamt, ' ' AS loanbank GROUP BY name" - something like that.


Edit: Another way to do this: Create a view with select cuID, other customer fields, MAX(key-for-join-table-1) as k1, max(key-for-join-table-2) as k2 from customers group by cuID, other customer fields" and save it as "uniqueCustomers". Then use this in your main query instead of your customer table, and join to your secondary tables on both cuid and k1/k2 whatever, this way you get a customer view with a single loan or other 1-n table's foreign key already in it so when you join to the other tables you don't get a 1-n join.

Coming at this from another angle, you say that this was originally built in access and is now being moved to a more robust (or at least new) platform? Is that correct?

Can you not just use the new platform, open a connection to the access database, open a connection to your new database, and do a series of CREATE TABLE and SELECT INTO statements?

If not, bummer. And I wasn't trying to blame you for using access or anything, I know how the little projects go and how little they pay attention to programmers.

Sounds like mparker knows views & access a little better than I, best of luck solving your quandary :D

I think I'm going to just give it up. I've tried creating a table from the query using create as and also select into. I made a new query and in the sql view I typed:

Select * into exporttable from originalquery; and ran it. It did exactly the same thing as the graphical table builder that is supposed to build a table from a query. It did the query but gave me nothing else, no table, nothing.

If I could have pulled it into it's own table I would have then been able to select distinct on the cuID and get unique records and the rest would have been easy from there.

I just won't be able to get paid on this job. I capped the hours at $265.00 anyways so at least I'm not losing a huge chuck. This is when having a policy of "we deliver or we don't get paid" kinda sucks :p.

Oh well. Thanks for all the suggestions. It's my own fault for not generating enough work to keep me savvy with databases and sql expressions.

Not sure if you care at this point since it sounds like the towel may have been thrown, but in case Rocky hasn't had enough yet...

SELECT INTO only selects into a temporary table. You'd want to create your new table first, with all of the fields you want, then select into, parse through the recordset and for each unique customer ID, write the new values into the new table and update it.

If Rocky's had enough, then say goodbye and take an aspirin or two, heh!

well I got my hopes up then dashed again.

I managed to pull all these fields into a single table with over 4000 rows/records. then I tried to select distinct cuID into a new table and it didnt work. Any quick ideas?

Glen F

Quote:

---

Originally Posted by FUD

well I got my hopes up then dashed again.

I managed to pull all these fields into a single table with over 4000 rows/records. then I tried to select distinct cuID into a new table and it didnt work. Any quick ideas?

Glen F

---

The problem is that SELECT DISTINCT only tosses out duplicate *rows*, not rows with duplicate fields, i.e. the DISTINCT doesn't apply to the field but to the query itself. It's as if you had two select statements: SELECT and SELECTDISTINCT.

So you need to break your problem into two queries: One that selects the cuID and the record ID's for each of the other tables in the join, and nothing else. This query can be made distinct on cuID by using GROUP BY on cuID and selecting MAX(other-id) on the other record id's. This will get you an arbitrary record ID for the other tables, but it sounds like that's ok as long as you have a valid one. This query can be put in a view for convenience. The point of this is to get a single cuID record and valid id's for each other table. Then you can join this table with the customer and loan and whatever tables using a 1:1 join so you can get all the information you need for this CSV without getting multiple customer records (you may need to join on (customer id, other-table-id) to get a 1:1 join, but this should work because the other-table-id is valid for that customer because of the way it was fetched in the view query.

I thought I had replied to this but I guess it didn't take. so here goes again ;).

I'm not following the explanation fully fully. I completely understand how distinct compares entire rows. I've gotten all the info down to a single table with 4400 rows/records (there abouts) and don't see how the multiple tables weigh in now.

I couldn't find a way to extract the row number for any manipulation purposes. Access assigns "group by" to every single field added by default and I couldn't get min or max to work on anthing I tried.

I ended up getting a work around after discussing things with the developers of the new program. They sent an updated program that allowed me to do it in 2 imorts by blanking out loan info and importing customer&bank information only, then I was able to simply sort on loan start and due dates with loan status information to get the active loans. I used this second query to create the file for the 2nd import.

We'll see how things go as they test and verify the data inside the new program.

Glad to hear you've found a solution to your quandary. If you have to deal with SQL in the future, may want to look into setting up a SQL dev box with linux and importing databases there for complicated management; then you could use scripts to manipulate your database instead of Access' clunky UI. In the end I suspect you'll be happy to see this one go on down the river :)

Quote:

---

Originally Posted by TomlinAS

If you have to deal with SQL in the future, may want to look into setting up a SQL dev box with linux and importing databases there for complicated management;

---

I've got mysql loaded on linux but have never had to try to use it for work. I think I'm going to find a good mysql book and some sample databases and see what kind of skills I can pick up.

That's not a bad idea if you're planning to do much with SQL in the future. There were a lot of ways to solve this problem, but none of them were the sort of thing you can pull off with the point-and-grunt query interface in Access.

Quote:

---

Originally Posted by mparker762

That's not a bad idea if you're planning to do much with SQL in the future. There were a lot of ways to solve this problem, but none of them were the sort of thing you can pull off with the point-and-grunt query interface in Access.

---

don't forget to wipe ;).