Comparing dates in columns from linked table

[fredo97]

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

 

[Stefan Hoffmann]

hi fredo,

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

 

[Marshall Barton]

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

 

[fredo97]

It works!! Thanks so much Marshall, you rock !

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