Thread: Any SQL GURUS or Acess Gurus here? Please

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

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 .

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

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

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.