If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Comparing dates in columns from linked table
Hi,
I'm new to Access/SQL and have a problem with a query that I just cant seem to be able to solve. So, I have an Access database containing client number, security name and date, that information has to be updated every month, the source for the update being an excel file that I link to the Access database. But I dont want to overwrite on the existing data so what i wanted to do was add the current month's data, when it's not already in there. What I did was an insert query which inserts the data from the excel file to the table in the Access database only if the column containing the date of the data is different from what is already in the database. Here is what I came up with and it is not workin and i dont understand why: INSERT INTO DatabaseTable ( Client, Security, Date ) SELECT ExcelTable.Client, ExcelTable.[Security Description], ExcelTable.Date FROM ExcelTable WHERE (((Exists (SELECT 1 FROM DatabaseTable WHERE DatabaseTable.Date = ExcelTable.Date)=false))); The query doesnt insert any rows and looks as though it considers the dates from the two tables are identical even though they are not. Can anyone help please ? Thanks, Fred |
#2
|
|||
|
|||
Comparing dates in columns from linked table
hi fredo,
On 07.02.2010 13:58, fredo97 wrote: What I did was an insert query which inserts the data from the excel file to the table in the Access database only if the column containing the date of the data is different from what is already in the database. Here is what I came up with and it is not workin and i dont understand why: INSERT INTO DatabaseTable ( Client, Security, Date ) SELECT ExcelTable.Client, ExcelTable.[Security Description], ExcelTable.Date FROM ExcelTable WHERE (((Exists (SELECT 1 FROM DatabaseTable WHERE DatabaseTable.Date = ExcelTable.Date)=false))); Check your WHERE condition, it should be WHERE NOT EXISTS ( SELECT * FROM DatabaseTable WHERE DatabaseTable.[Date] = ExcelTable.[Date] ); Also check the data type of to field in the linked Excel table. Date() is a function in VBA, thus you should also use the square brackets. mfG -- stefan -- |
#3
|
|||
|
|||
Comparing dates in columns from linked table
fredo97 wrote:
I'm new to Access/SQL and have a problem with a query that I just cant seem to be able to solve. So, I have an Access database containing client number, security name and date, that information has to be updated every month, the source for the update being an excel file that I link to the Access database. But I dont want to overwrite on the existing data so what i wanted to do was add the current month's data, when it's not already in there. What I did was an insert query which inserts the data from the excel file to the table in the Access database only if the column containing the date of the data is different from what is already in the database. Here is what I came up with and it is not workin and i dont understand why: INSERT INTO DatabaseTable ( Client, Security, Date ) SELECT ExcelTable.Client, ExcelTable.[Security Description], ExcelTable.Date FROM ExcelTable WHERE (((Exists (SELECT 1 FROM DatabaseTable WHERE DatabaseTable.Date = ExcelTable.Date)=false))); The query doesnt insert any rows and looks as though it considers the dates from the two tables are identical even though they are not. That approach would work if the subquery's where clause also checked for matching Client fields. However, I think this approach is cleaner and probably faster: INSERT INTO DatabaseTable ( Client, Security, Date ) SELECT ExcelTable.Client, ExcelTable.[Security Description], ExcelTable.Date FROM ExcelTable LEFT JOIN DatabaseTable ON DatabaseTable.Date = ExcelTable.Date And DatabaseTable.Client = ExcelTable.Client WHERE DatabaseTable.Client Is Null -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Comparing dates in columns from linked table
It works!! Thanks so much Marshall, you rock !
Marshall Barton wrote: I'm new to Access/SQL and have a problem with a query that I just cant seem to be able to solve. [quoted text clipped - 19 lines] The query doesnt insert any rows and looks as though it considers the dates from the two tables are identical even though they are not. That approach would work if the subquery's where clause also checked for matching Client fields. However, I think this approach is cleaner and probably faster: INSERT INTO DatabaseTable ( Client, Security, Date ) SELECT ExcelTable.Client, ExcelTable.[Security Description], ExcelTable.Date FROM ExcelTable LEFT JOIN DatabaseTable ON DatabaseTable.Date = ExcelTable.Date And DatabaseTable.Client = ExcelTable.Client WHERE DatabaseTable.Client Is Null |
Thread Tools | |
Display Modes | |
|
|