Excel and Access DB question

Without getting into the merits of Access ;) I have an Access DB, from which I've exported a contact list in Excel. In the Access DB, each person has an organization assigned to them, and that organization is assigned an Org ID. Here's a quick sketch of how it looks in excel

last name	first name	org id
smith	john	1
jones	tom	2
davis	billy	2

I have another excel spreadsheet that contains all of the corresponding org IDs.

org ID	organization
1	ABC Inc.
2	XYZ Inc.

How can I replace all of the Org IDs in the sheet that contains the names (first example above), with the data from the sheet that contains the Org IDs?

That is to say, I want to have any instance of 'org id 1', replaced in the first sheet with ABC Inc, and any instance of 'org id 2' replaced with XYZ Inc.

Write a joined query in Access and export the results? i.e.

[code1275208b5b8]SELECT last_name, first_name, organzation
FROM contacts, orgs
WHERE orgs.org_id = contacts.org_id[/code1275208b5b8]

That's assuming 'contacts' is the table containing your contact info, and 'orgs' is you second organization table -- substitute as needed.

If you have contacts with null (empty) org_id's, you might need a left outer join to avoid dropping those contacts, rather than the implied inner join here. If so, let me know and I'll type that up (have to dust off the cobwebs on syntax since it's been ages since I wrote a left outer join).

Thanks Dmo - I appreciate the help.

Ahh...good old Access. We just finished up learning some Access in my K201 class a couple weeks ago. Now we are learning Excel.

Ya rly.

[quote3ff34ada03="doylnea"]Thanks Dmo - I appreciate the help.[/quote3ff34ada03]
So... did it work? Or are you just patting me on the head and sending me on my way? P

Yep, for the most part. There are about 600 contacts without orgs (and thus Org IDs), however, and when wrote the query, it only displays the contacts with an org ID.

I'm trying to figure out how to output the entire list, and at the bottom of the list show all of the contacts without orgs as well. That's still up in the air.

[ie0218c7857]Edit In a a quick and dirty way, I fixed the problem, by looking a little deeper. I had org ids of 0 assigned to the 600 or so folks, so I created a new org, with a new org number, did a find and replace for all org IDs of 0, and replaced that with the new org ID, and re-queried, and that generated an output that's alot more usable.[/ie0218c7857]

Now, I'm working on a compare in Excel, comparing a prior list with the most recent list to find duplicates, and have the duplicates entries deleted. That is, I have a list from May 2007 that I'd like to compare to a list from October 2007 (which contains more members than the 2005 list), and only show the unique members of the October 2007 list. Any help with that one?

[quotec82fbc9ef8="doylnea"]Yep, for the most part. There are about 600 contacts without orgs (and thus Org IDs), however, and when wrote the query, it only displays the contacts with an org ID.

I'm trying to figure out how to output the entire list, and at the bottom of the list show all of the contacts without orgs as well. That's still up in the air.

[ic82fbc9ef8]Edit In a a quick and dirty way, I fixed the problem, by looking a little deeper. I had org ids of 0 assigned to the 600 or so folks, so I created a new org, with a new org number, did a find and replace for all org IDs of 0, and replaced that with the new org ID, and re-queried, and that generated an output that's alot more usable.[/ic82fbc9ef8][/quotec82fbc9ef8]

Yeah, you needed that left outer join, then. Here would be the syntax for that (for future reference, as I guess you no longer need it since you kludged it with a 0 ID)

[code1c82fbc9ef8]SELECT last_name, first_name, organization
FROM contacts
LEFT OUTER JOIN orgs ON contacts.org_id = orgs.org_id
ORDER BY last_name[/code1c82fbc9ef8]

That should work, returning all contacts including those with null organizations. I don't recall if Access/JET SQL requires a table alias to reference columns in a table, or if you can just use the table name like I did in the join clause.

To get the null orgs grouped together, you might add a GROUP BY clause before the ORDER BY, i.e. [bc82fbc9ef8]GROUP BY organization, last_name, first_name[/bc82fbc9ef8]. But that will likely group the null orgs at the top instead of the bottom. I can't recall how or if it's even possible to move null group columns to the bottom.

[quotec82fbc9ef8="doylnea"]Now, I'm working on a compare in Excel, comparing a prior list with the most recent list to find duplicates, and have the duplicates entries deleted. That is, I have a list from May 2007 that I'd like to compare to a list from October 2007 (which contains more members than the 2005 list), and only show the unique members of the October 2007 list. Any help with that one?[/quotec82fbc9ef8]
It could be done with a macro, which I hate doing. I guess it's just the professional coder in me, but I cringe watching folks code up Excel hacks to manipulate data.

Did you generate both lists from the database, or is the May list something put together manually back then, and you just have the Octoboer data in the db? If you have both sets of data in the db somewhere, and you just need to know the additions since May, I would prefer to do that with SQL too. If this is something that you must do in Excel, and on an ongoing basis, then a macro that walks contacts from each list and compares them -- copying unique contacts to another list -- shouldn't be that hard. But it could get tricky if, as well as grabbing additions, you have to also detect old contacts being removed from the new list.

To get your NULLs at the bottom, you would do something similar to this in the ORDER BY clause.

ORDER BY NVL(organization,'zzzzzzzz')

Which would treat all nulls as the string 'zzzzzzzz' for sorting purposes. Not sure what Access's syntax would be for this, but you'd want to find the function that replaces NULL with a specified value.

[quote58ee57654a="dmorris68"][quote58ee57654a="doylnea"]Now, I'm working on a compare in Excel, comparing a prior list with the most recent list to find duplicates, and have the duplicates entries deleted. That is, I have a list from May 2007 that I'd like to compare to a list from October 2007 (which contains more members than the 2005 list), and only show the unique members of the October 2007 list. Any help with that one?[/quote58ee57654a]
It could be done with a macro, which I hate doing. I guess it's just the professional coder in me, but I cringe watching folks code up Excel hacks to manipulate data.

Did you generate both lists from the database, or is the May list something put together manually back then, and you just have the Octoboer data in the db? If you have both sets of data in the db somewhere, and you just need to know the additions since May, I would prefer to do that with SQL too. If this is something that you must do in Excel, and on an ongoing basis, then a macro that walks contacts from each list and compares them -- copying unique contacts to another list -- shouldn't be that hard. But it could get tricky if, as well as grabbing additions, you have to also detect old contacts being removed from the new list.[/quote58ee57654a]

No, the earlier May 2007 list is strictly a Excel list, so I can't use Access to compare them. I spent a good bit of time googling and trying various freeware/shareware solutions, but I couldn't make any of them work.

This will be an infrequent event, but it's a list that's large enough that I'd like not to do it by hand, or by eyeballing each row. I've already sorted alphabetically, which would make this a little easier to do by hand, but not easy enough to [i58ee57654a]actually do [/i58ee57654a]it by hand ;)

One thing I did consider was some conditional formatting, which I've used to great success with lucrocash for a spreadsheet we update daily, but I'd really like to see the duplicate rows deleted in this case.

coughbumpcough

So you have one list in Excel and one in Access -- why not copy the Access list into the Exel list and then run a "Delete Duplicate Rows" macro and then you are done...

[quoteb85f504f8a="TryinToGetPaid"]So you have one list in Excel and one in Access -- why not copy the Access list into the Exel list and then run a "Delete Duplicate Rows" macro and then you are done...[/quoteb85f504f8a]

I have one giant list in excel, sorted alphabetically, by name, then org. I don't have a delete duplicate rows macro, or I would have used it already. Do you have one?

I would assume the ordinary "delete duplicate row" macros would simply delete the excess row(s) and leave one. He wants to extract the rows from the newer list that do not exist in the old list. So essentially he wants to use the old list to "mask" out rows in the new list.

Now if you have or can get a delete duplicate rows macro that deletes ALL instances of a duplicated row (including the original, leaving none), then that would do what you want if you have both lists merged into one.

EDIT Actually, if it were possible to have rows in the old list that didn't appear in the new list, then the result would be rows unique to either list, not just the new one. That might not be what you want. But if you know that everything in the May list also exists in the October list, then you could take the shortcut.

[code15bbf04b04e]
Public Sub DeleteDuplicateRows()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DeleteDuplicateRows
' This will delete duplicate records, based on the Active Column. That is,
' if the same value is found more than once in the Active Column, all but
' the first (lowest row number) will be deleted.
'
' To run the macro, select the entire column you wish to scan for
' duplicates, and run this procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Set Rng = Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns(ActiveCell.Column))

Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")

N = 0
For R = Rng.Rows.Count To 2 Step -1
If R Mod 500 = 0 Then
Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
End If

V = Rng.Cells(R, 1).Valueurl==http://=http:///url
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.
' Rather than pass in the variant, you need to pass in vbNullString explicitly.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If V = vbNullString Then
If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then
Rng.Rows(R).EntireRow.Deleteurl==http://=http:///url
N = N + 1
End If
Else
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(R).EntireRow.Deleteurl==http://=http:///url
N = N + 1
End If
End If
Next R

EndMacro:

Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(N)

End Sub[/code15bbf04b04e]

[quotee93d8dbd38="dmorris68"]EDIT Actually, if it were possible to have rows in the old list that didn't appear in the new list, then the result would be rows unique to either list, not just the new one. That might not be what you want. But if you know that everything in the May list also exists in the October list, then you could take the shortcut.[/quotee93d8dbd38]

Yes, the October list is essentially additions to the May list, so everyone that's in the May list exists in the October list.

TTGP, you missed my post that I squeezed in ahead of you. )

He doesn't want to leave one row if there's a dupe. He wants only the rows from list B that don't exist in list A.

As I mentioned earlier, it would still be trivial to code up as a macro if you could make certain assumptions about the data (all of list A is assumed to exist in list B, for example).

EDIT

[quote3d90a438c5="doylnea"]Yes, the October list is essentially additions to the May list, so everyone that's in the May list exists in the October list.[/quote3d90a438c5]
That makes it much easier to write a macro for, then. If you don't come up with something soon, I'll grit my teeth and write you one. You'll have to compensate me for the damage writing VBScript does to my psyche, though. P

[quote52585e2090="TryinToGetPaid"]macro[/quote52585e2090]

Hrm, maybe not...investigating.

So I may have been unclear. If a person is on the May list, and the October list, I'd like both (all) instances of their appearance to be deleted, essentially leaving me with the additions, not just one instance, as that essentially creates the October list all over again.

Did I misunderstand that you wanted only the newly added rows in the October list? Because TTGF's macro leaves one common row, which should result in the October list, unchanged.

[quote00574b5c14="dmorris68"]Did I misunderstand that you wanted only the newly added rows in the October list? Because TTGF's macro leaves one common row, which should result in the October list, unchanged.[/quote00574b5c14]
Yeah, see my edit oops

http//www.sobolsoft.com/excelunique/

Found this. I am looking for a macro that does the same thing

Edit I assume this is Pre-Excel 07?

[quotec2274eb15a="TryinToGetPaid"]http//www.sobolsoft.com/excelunique/

Found this. I am looking for a macro that does the same thing[/quotec2274eb15a]

Yeah I tried that last night, and the trial seems to work, but the results are limited by the trial version, so I can't tell if it does everything I'd like.

WINNER!!
http//members.iinet.net.au/~brettdj/

It is freeware and has all the option you would need.

It's definitely closer, but the unique command is kind of kludgy, and not quite working correctly. In theory, if I knew what I was doing, this would be a pretty simple thing to code.

"If A appears in Sheet [A], more than once, then delete all instances"

Did you Choose Delete Duplicates?

It may be best to copy into another Excel Sheet, and run it. It deletes all instances of a duplicate (both the dupe and the original leaving only the uniques) I just tested it and it worked fine.

MAKE SURE YOU CLICK THE CHECK BOX OR THE ORIGINAL WILL NOT BE DELETED.