Thread: 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.

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, ...")?

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.

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).

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.

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.