Help with an access DB

[leeland]

OK, Iwill try to make this is as simple as possible.

My co-worker has an excel spreadsheet with 20,000 account numbers in it...it is the master list.

Each month she gets a new report of roughly 1,200 accounts and 2/3's of them are duplicates of what are in the master list.

she has been going through this list manually every month to delete the duplicates...which you can imaging takes over a day to do...I told her I would automate that process and do it in access....

HERE THE CATCH IS THOUGH

this is what she actually needs...from the report that she gets each month she needs to find only the new account numbers...so lets say this month that she has 600 account numbers but only 60 of them are brand new (i.e. not in the master list)...so there would be three tables

1 The Master List
2 The Temp List (which would house the monthly report)
3 The Dump Table (which would be used to dump all the new accounts to so that after she checks them she can add them to the Master List)

I have tried to do a query in access but I can't fiqure it out...so I need your help

select Temp_SS_Numbers.Temp_SS_Numbers
FROM Temp_SS_Numbers
OUTER JOIN Master_SS_List
ON Temp_SS_Numbers.Temp_SS_Numbers <> Master_SS_List.SS_Numbers;

I am getting a syntax error near the From Statement

I don't know what the deal is...and also I want to dump the results into the third table if that is possible so she so she can review them....

so I tried to do a query off of two tables and then put the results back into a third table but I keep on getting prompted for a parameter...

Here is a screen shot of the SQL I wrote...I am sure it is incomplete...but if someone could give me a hand on this it would be nice



Thanks,


Leeland

 

[bunker]

SELECT *
FROM [Temp_SS_Numbers]
WHERE [Temp_SS_Numbers].[Temp_SS_Numbers]
NOT IN (SELECT [Master_SS_List].[SS_Numbers] FROM [Master_SS_List]);

Try that. BTW, I wouldn't name the column the same as the table...can get confusing in your queries as to whether you are referring to the table or the column.

EDIT: Instead of a third table, just save the query so all she has to do is double click the query to see the new records.

 

[leeland]

Originally posted by: bunker
SELECT *
FROM [Temp_SS_Numbers]
WHERE [Temp_SS_Numbers].[Temp_SS_Numbers]
NOT IN (SELECT [Master_SS_List].[SS_Numbers] FROM [Master_SS_List]);

Try that. BTW, I wouldn't name the column the same as the table...can get confusing in your queries as to whether you are referring to the table or the column.

EDIT: Instead of a third table, just save the query so all she has to do is double click the query to see the new records.

Well that ran but I got no records what so ever back as a return set...looking at the SQL that looks like it should work...I mean it is checking each record from the temp table and saying that "select this record as long as it isn't present in the master list query"

Don't know why it doesn't work though

Any other suggestions ?

And I will change the names when I get this running...but your right it could get confusing


Lee

 

[bunker]

Is the numbers column the same format in both tables? i.e. is one text and one a number data type? Make sure they're identical in the table design.

Also, you sure she hasn't already added the numbers that are in the temp table? That would make them identical as well.

 

[leeland]

Originally posted by: bunker
Is the numbers column the same format in both tables? i.e. is one text and one a number data type? Make sure they're identical in the table design.

Also, you sure she hasn't already added the numbers that are in the temp table? That would make them identical as well.

They are both the same format...

I am sure that the numbers are not already in the master table...I did some work in excel to find out if the records were duplicated and I found that there should be 67 new records...

Here is what I did in excel. I did a countif(a1:a20000,a1) formula and that gave me a number...any number greater than 1 was a duplicate

Then I did a if statement on that column. if(b1 > 1, 1,0) this returned a single value to me which I then tallied up to get a count of how many dupliate records there were. It is crude but I think it worked to find the right number of records.

I guess at the moment I am stumped


Leeland

 

[leeland]

Originally posted by: bunker
Is the numbers column the same format in both tables? i.e. is one text and one a number data type? Make sure they're identical in the table design.

Also, you sure she hasn't already added the numbers that are in the temp table? That would make them identical as well.

Just to show you again...I got a request for parameter when I ran your query you posted ....


What do you make out of this ????

SQL Parameter


Leeland

 

[leeland]

Alright I found a menu in Access that does exactly what I was lookin for...find unmatched records...

here is the sql that it generated...I am not sure what the last part of it does so if anyone could give an expanation I would appreciate it


SELECT Temp_SS_List.Temp_SS
FROM Temp_SS_List
LEFT JOIN Master_SS_List
ON Temp_SS_List.Temp_SS = Master_SS_List.Master_SS
WHERE (((Master_SS_List.Master_SS) Is Null));



Thanks


Leeland

 

[bunker]

It joins the two tables together, then looks for records that don't have a match in the master list then selects just those records.

 

[ActuaryTm]

While I would wholheartedly agree with the automation (and moreover, with using Access), introducing a new application as a solution can unfortunately lead to problems - chiefiest being troubleshooting for the employee who does not understand the application enough to use and/or maintain such. Access would certainly be a very viable solution in this case, but a better solution given the end-user may be that which is outlined below.

Why not keep the original format (that being Excel), and merely do a vertical lookup (function VLOOKUP) of the new data in the master list? Use a logical statement (most likely, the IF function) to check the master list using VLOOKUP. Filter by a logical result (1 for true, 0 for false, or the like). Paste the resultant filtered data into the master list.

Should take all of a few minutes, if that.

 

[leeland]

Originally posted by: ActuaryTm
While I would wholheartedly agree with the automation (and moreover, with using Access), introducing a new application as a solution can unfortunately lead to problems - chiefiest being troubleshooting for the employee who does not understand the application enough to use and/or maintain such. Access would certainly be a very viable solution in this case, but a better solution given the end-user may be that which is outlined below.

Why not keep the original format (that being Excel), and merely do a vertical lookup (function VLOOKUP) of the new data in the master list? Use a logical statement (most likely, the IF function) to check the master list using VLOOKUP. Filter by a logical result (1 for true, 0 for false, or the like). Paste the resultant filtered data into the master list.

Should take all of a few minutes, if that.

I thought about that...but I don't know that she would feel comfortable enough with that either...so that is why I went with excel...I figured if I could get the master list in an access DB...then all I would need to do is construct a couple of queries that would do two things

1. look at the temp list...compare it to the master list and return a query of all the new account numbers...

2. Write a query that would take the new account numbers and then append them to the master list once she has identified the new account numbers (which she needs for a seperate report)

Leeland