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.

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.

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 UseridSELECT DISTINCT tblCustomers.cuID)

Thanks for your efforts.

Glen F

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

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.

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.