Query Aggregation Problem

[Geoff]

Hi

I have 2 tables, one is the data download from online banking and the second
one is with filtered date.

From table 1 I want to insert date into table 2 only if the date is greater
than the date of data in table 1.

I wrote this query

INSERT INTO tblnationwideflex (transdate, payee,[in], out, balance)
SELECT tblNationwideImport.Field1,
tblNationwideImport.Field2,
tblNationwideImport.Field3,
tblNationwideImport.Field4,
tblNationwideImport.Field5

FROM tblNationwideImport

where Max(tblNationwideImport.Field1) > Max(tblnationwideflex.transdate);

but access will not allow aggregation in the where clause.

Any suggestions would be appreciated.

Thank you and merry Christmas

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

PERHAPS what you want is

INSERT INTO tblnationwideflex (transdate, payee,[in], out, balance)
SELECT tblNationwideImport.Field1,
tblNationwideImport.Field2,
tblNationwideImport.Field3,
tblNationwideImport.Field4,
tblNationwideImport.Field5
FROM tblNationwideImport
WHERE tblNationwideImport.Field1 > DMax("TransDate","tblnationwideflex")

Using a subquery may also work

INSERT INTO tblnationwideflex (transdate, payee,[in], out, balance)
SELECT tblNationwideImport.Field1,
tblNationwideImport.Field2,
tblNationwideImport.Field3,
tblNationwideImport.Field4,
tblNationwideImport.Field5
FROM tblNationwideImport
WHERE tblNationwideImport.Field1 > (SELECT Max(TransDate) FROM tblNationWideFlex)

In any case, I would try running the SELECT portion of the queries FIRST to
see if they are returning the records you want to insert. If not, then modify
the SELECT queries until they do return what you want and then turn them into
append (insert) queries.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Geoff]

Thanks John for pointing me in the right direction.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way
you expect.

PERHAPS what you want is

INSERT INTO tblnationwideflex (transdate, payee,[in], out, balance)
SELECT tblNationwideImport.Field1,
tblNationwideImport.Field2,
tblNationwideImport.Field3,
tblNationwideImport.Field4,
tblNationwideImport.Field5
FROM tblNationwideImport
WHERE tblNationwideImport.Field1 > DMax("TransDate","tblnationwideflex")

Using a subquery may also work

INSERT INTO tblnationwideflex (transdate, payee,[in], out, balance)
SELECT tblNationwideImport.Field1,
tblNationwideImport.Field2,
tblNationwideImport.Field3,
tblNationwideImport.Field4,
tblNationwideImport.Field5
FROM tblNationwideImport
WHERE tblNationwideImport.Field1 > (SELECT Max(TransDate) FROM
tblNationWideFlex)

In any case, I would try running the SELECT portion of the queries FIRST
to see if they are returning the records you want to insert. If not, then
modify the SELECT queries until they do return what you want and then turn
them into append (insert) queries.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi

I have 2 tables, one is the data download from online banking and the
second one is with filtered date.

From table 1 I want to insert date into table 2 only if the date is
greater than the date of data in table 1.

I wrote this query

INSERT INTO tblnationwideflex (transdate, payee,[in], out, balance)
SELECT tblNationwideImport.Field1,
tblNationwideImport.Field2,
tblNationwideImport.Field3,
tblNationwideImport.Field4,
tblNationwideImport.Field5

FROM tblNationwideImport

where Max(tblNationwideImport.Field1) >
Max(tblnationwideflex.transdate);

but access will not allow aggregation in the where clause.

Any suggestions would be appreciated.

Thank you and merry Christmas