Action query - help please

J

Jack Sheet

Hi all.
I have a database with two tables:
T_Clients
T_Tasks

T_Clients has two fields:
ClientID = autonumber, primary key
ClientName = text, indexed, no duplicates

T_Tasks has three fields:
TaskID = autonumber, primary key
ClientID = number
Date = date/time

There is a one(T_Clients)-to-many(T_Tasks) relationship between ClientID
fields with enforced referencial integrity.

I wish to run a query to add records to T_Tasks based on the following
rules:
For each value of ClientID in the T_Clients table, if there does not already
exist a corresponding record in T_Tasks wherein the value of the Date field
= 05 April 2006, then create such a record (with date 05 April 2006), but if
there does already exist such a record then ignore that value of ClientID.

So, which of "Update" query or "Append" query should I use, and what is the
syntax of the criteria expression, please? I hardly ever do action queries,
as you can tell. It "sounds" like an "Append" query but the help notes
indicate that that should be used for appending one table to another, but I
just want to add records to an existing table.

Thanks in advance.
 
J

John Spencer

If you are adding records you use an append query.
If you are changing the content of an existing record(s) you use an UPDATE
query.

You need to identify which clients have a record on that date

That query would look like:
SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#

You need to use the above query to find those that DO NOT exist. That query
would look like:
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID

So combining all that into one query, you end up with
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID


You could save each of the queries and them use the query as if it were a
table.
So save the first query as TT
Then the second query reads
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN TT
ON TC.ClientID = TT.ClientID

Save that as TC and the third query reads
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM TC
 
J

Jack Sheet

John, thank you very much. I don't understand the code at present but am
committed to doing so. My first step in understanding it will be to get the
code to work, to ensure that I am working with correct code and data. At
the moment I cannot get it to work and describe below my problem.

I have entered 4 records in T_Clients:
ClientID ClientName
1 Client1
2 Client2
3 Client3
4 Client4

I have entered 2 records in T_Tasks
(I am in UK where date formats are dd/mm/yyyy)
TaskID ClientID Date
1 1 05/04/2006
2 3 05/04/2006
3 2 31/12/2005

The desired end result will be a table T_Tasks showing
TaskID ClientID Date
1 1 05/04/2006
2 3 05/04/2006
3 2 31/12/2005
4 2 05/04/2006
5 4 05/04/2006

(I am not bothered if TaskID 4 and TaskID 5 are reversed).

I have saved a query "TC" which states
Select ClientID
From T_Tasks
Where T_Tasks.Date = #2006-04-05#

Incidentally, when I save, close and reopen the query the code is changed
slightly to:
Select ClientID
From T_Tasks
Where T_Tasks.Date = #4/5/2006#;

I assume that this change is irrelevant, as running the query produces
expected results, showing just two records clientID 1 and 3. So far so
good.

I then saved a query TT which says:
Select TC.ClientID
From T_Clients as TC Left Join
(Select ClientID
From T_Tasks
Where T_Tasks.Date = #2006-04-05#) as TT
On TC.ClientID = TT.ClientID

Now I run into problems:

I run TT query and from what you say I should expect it to show 2 records,
with ClientID 2 and 4 respectively. Instead it displays 4 records, with
ClientID 1, 2, 3 and 4 respectively, in that order, ie every record in
T_Clients.

If I view the saved SQL code in the query TT I find that Access has changed
the grammar slightly, to the following:

SELECT TC.ClientID
FROM T_Clients AS TC LEFT JOIN
[select ClientID from T_Tasks Where T_Tasks.Date = #2006-04-05#].
AS TT ON TC.ClientID=TT.ClientID;

(I entered carriage returns to prevent newsreader wordwrap at confusing
points).

I have not proceeded further, pending any advice you may have so far.

Thanks



John Spencer said:
If you are adding records you use an append query.
If you are changing the content of an existing record(s) you use an
UPDATE query.

You need to identify which clients have a record on that date

That query would look like:
SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#

You need to use the above query to find those that DO NOT exist. That
query would look like:
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID

So combining all that into one query, you end up with
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID


You could save each of the queries and them use the query as if it were a
table.
So save the first query as TT
Then the second query reads
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN TT
ON TC.ClientID = TT.ClientID

Save that as TC and the third query reads
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM TC
Click to expand...
 
J

Jack Sheet

Incidentally I had an idea which I thought ought to work but didn't, and I
would like to know why not.

I created a query TTNew which simply said
SELECT T_Clients.ClientID
FROM T_Clients
WHERE [T_Clients].[ClientID] not in ([TC].[CLientID]);

To recap, the intention was to select all instances of ClientID in the table
T_Clients for which there was no matching entry in the query TC.

The query saved OK, but when I ran it I get prompted to enter a value for
ClientID. I was not expecting that prompt and was stumped.


Jack Sheet said:
John, thank you very much. I don't understand the code at present but am
committed to doing so. My first step in understanding it will be to get
the code to work, to ensure that I am working with correct code and data.
At the moment I cannot get it to work and describe below my problem.

I have entered 4 records in T_Clients:
ClientID ClientName
1 Client1
2 Client2
3 Client3
4 Client4

I have entered 2 records in T_Tasks
(I am in UK where date formats are dd/mm/yyyy)
TaskID ClientID Date
1 1 05/04/2006
2 3 05/04/2006
3 2 31/12/2005

The desired end result will be a table T_Tasks showing
TaskID ClientID Date
1 1 05/04/2006
2 3 05/04/2006
3 2 31/12/2005
4 2 05/04/2006
5 4 05/04/2006

(I am not bothered if TaskID 4 and TaskID 5 are reversed).

I have saved a query "TC" which states
Select ClientID
From T_Tasks
Where T_Tasks.Date = #2006-04-05#

Incidentally, when I save, close and reopen the query the code is changed
slightly to:
Select ClientID
From T_Tasks
Where T_Tasks.Date = #4/5/2006#;

I assume that this change is irrelevant, as running the query produces
expected results, showing just two records clientID 1 and 3. So far so
good.

I then saved a query TT which says:
Select TC.ClientID
From T_Clients as TC Left Join
(Select ClientID
From T_Tasks
Where T_Tasks.Date = #2006-04-05#) as TT
On TC.ClientID = TT.ClientID

Now I run into problems:

I run TT query and from what you say I should expect it to show 2 records,
with ClientID 2 and 4 respectively. Instead it displays 4 records, with
ClientID 1, 2, 3 and 4 respectively, in that order, ie every record in
T_Clients.

If I view the saved SQL code in the query TT I find that Access has
changed the grammar slightly, to the following:

SELECT TC.ClientID
FROM T_Clients AS TC LEFT JOIN
[select ClientID from T_Tasks Where T_Tasks.Date = #2006-04-05#].
AS TT ON TC.ClientID=TT.ClientID;

(I entered carriage returns to prevent newsreader wordwrap at confusing
points).

I have not proceeded further, pending any advice you may have so far.

Thanks



John Spencer said:
If you are adding records you use an append query.
If you are changing the content of an existing record(s) you use an
UPDATE query.

You need to identify which clients have a record on that date

That query would look like:
SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#

You need to use the above query to find those that DO NOT exist. That
query would look like:
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID

So combining all that into one query, you end up with
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID


You could save each of the queries and them use the query as if it were a
table.
So save the first query as TT
Then the second query reads
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN TT
ON TC.ClientID = TT.ClientID

Save that as TC and the third query reads
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM TC


Jack Sheet said:
Hi all.
I have a database with two tables:
T_Clients
T_Tasks

T_Clients has two fields:
ClientID = autonumber, primary key
ClientName = text, indexed, no duplicates

T_Tasks has three fields:
TaskID = autonumber, primary key
ClientID = number
Date = date/time

There is a one(T_Clients)-to-many(T_Tasks) relationship between ClientID
fields with enforced referencial integrity.

I wish to run a query to add records to T_Tasks based on the following
rules:
For each value of ClientID in the T_Clients table, if there does not
already exist a corresponding record in T_Tasks wherein the value of the
Date field = 05 April 2006, then create such a record (with date 05
April 2006), but if there does already exist such a record then ignore
that value of ClientID.

So, which of "Update" query or "Append" query should I use, and what is
the syntax of the criteria expression, please? I hardly ever do action
queries, as you can tell. It "sounds" like an "Append" query but the
help notes indicate that that should be used for appending one table to
another, but I just want to add records to an existing table.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

My error. I forgot to include the WHERE clause. The sample should have
read.

SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID
WHERE TT.ClientID is NULL


INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID
WHERE TT.ClientID is NULL

The re-format of the date is no problem. It is just Access forcing the date
into the US format of mm-dd-yyyy which it has to do in queries where you are
using a literal value. The yyyy mm dd format is unambiguous.



Jack Sheet said:
John, thank you very much. I don't understand the code at present but am
committed to doing so. My first step in understanding it will be to get
the code to work, to ensure that I am working with correct code and data.
At the moment I cannot get it to work and describe below my problem.

I have entered 4 records in T_Clients:
ClientID ClientName
1 Client1
2 Client2
3 Client3
4 Client4

I have entered 2 records in T_Tasks
(I am in UK where date formats are dd/mm/yyyy)
TaskID ClientID Date
1 1 05/04/2006
2 3 05/04/2006
3 2 31/12/2005

The desired end result will be a table T_Tasks showing
TaskID ClientID Date
1 1 05/04/2006
2 3 05/04/2006
3 2 31/12/2005
4 2 05/04/2006
5 4 05/04/2006

(I am not bothered if TaskID 4 and TaskID 5 are reversed).

I have saved a query "TC" which states
Select ClientID
From T_Tasks
Where T_Tasks.Date = #2006-04-05#

Incidentally, when I save, close and reopen the query the code is changed
slightly to:
Select ClientID
From T_Tasks
Where T_Tasks.Date = #4/5/2006#;

I assume that this change is irrelevant, as running the query produces
expected results, showing just two records clientID 1 and 3. So far so
good.

I then saved a query TT which says:
Select TC.ClientID
From T_Clients as TC Left Join
(Select ClientID
From T_Tasks
Where T_Tasks.Date = #2006-04-05#) as TT
On TC.ClientID = TT.ClientID

Now I run into problems:

I run TT query and from what you say I should expect it to show 2 records,
with ClientID 2 and 4 respectively. Instead it displays 4 records, with
ClientID 1, 2, 3 and 4 respectively, in that order, ie every record in
T_Clients.

If I view the saved SQL code in the query TT I find that Access has
changed the grammar slightly, to the following:

SELECT TC.ClientID
FROM T_Clients AS TC LEFT JOIN
[select ClientID from T_Tasks Where T_Tasks.Date = #2006-04-05#].
AS TT ON TC.ClientID=TT.ClientID;

(I entered carriage returns to prevent newsreader wordwrap at confusing
points).

I have not proceeded further, pending any advice you may have so far.

Thanks



John Spencer said:
If you are adding records you use an append query.
If you are changing the content of an existing record(s) you use an
UPDATE query.

You need to identify which clients have a record on that date

That query would look like:
SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#

You need to use the above query to find those that DO NOT exist. That
query would look like:
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID

So combining all that into one query, you end up with
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID


You could save each of the queries and them use the query as if it were a
table.
So save the first query as TT
Then the second query reads
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN TT
ON TC.ClientID = TT.ClientID

Save that as TC and the third query reads
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM TC


Jack Sheet said:
Hi all.
I have a database with two tables:
T_Clients
T_Tasks

T_Clients has two fields:
ClientID = autonumber, primary key
ClientName = text, indexed, no duplicates

T_Tasks has three fields:
TaskID = autonumber, primary key
ClientID = number
Date = date/time

There is a one(T_Clients)-to-many(T_Tasks) relationship between ClientID
fields with enforced referencial integrity.

I wish to run a query to add records to T_Tasks based on the following
rules:
For each value of ClientID in the T_Clients table, if there does not
already exist a corresponding record in T_Tasks wherein the value of the
Date field = 05 April 2006, then create such a record (with date 05
April 2006), but if there does already exist such a record then ignore
that value of ClientID.

So, which of "Update" query or "Append" query should I use, and what is
the syntax of the criteria expression, please? I hardly ever do action
queries, as you can tell. It "sounds" like an "Append" query but the
help notes indicate that that should be used for appending one table to
another, but I just want to add records to an existing table.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Actually that was close. The problem is that you don't have TCin the FROM
clause of the main query and didn't put it in the IN clause

What you might have used would have been
SELECT T_Clients.ClientID
FROM T_Clients
WHERE [T_Clients].[ClientID] not in ( SELECT [TC].[ClientID] FROM TC);


Jack Sheet said:
Incidentally I had an idea which I thought ought to work but didn't, and I
would like to know why not.

I created a query TTNew which simply said
SELECT T_Clients.ClientID
FROM T_Clients
WHERE [T_Clients].[ClientID] not in ([TC].[CLientID]);

To recap, the intention was to select all instances of ClientID in the
table T_Clients for which there was no matching entry in the query TC.

The query saved OK, but when I ran it I get prompted to enter a value for
ClientID. I was not expecting that prompt and was stumped.


Jack Sheet said:
John, thank you very much. I don't understand the code at present but am
committed to doing so. My first step in understanding it will be to get
the code to work, to ensure that I am working with correct code and data.
At the moment I cannot get it to work and describe below my problem.

I have entered 4 records in T_Clients:
ClientID ClientName
1 Client1
2 Client2
3 Client3
4 Client4

I have entered 2 records in T_Tasks
(I am in UK where date formats are dd/mm/yyyy)
TaskID ClientID Date
1 1 05/04/2006
2 3 05/04/2006
3 2 31/12/2005

The desired end result will be a table T_Tasks showing
TaskID ClientID Date
1 1 05/04/2006
2 3 05/04/2006
3 2 31/12/2005
4 2 05/04/2006
5 4 05/04/2006

(I am not bothered if TaskID 4 and TaskID 5 are reversed).

I have saved a query "TC" which states
Select ClientID
From T_Tasks
Where T_Tasks.Date = #2006-04-05#

Incidentally, when I save, close and reopen the query the code is changed
slightly to:
Select ClientID
From T_Tasks
Where T_Tasks.Date = #4/5/2006#;

I assume that this change is irrelevant, as running the query produces
expected results, showing just two records clientID 1 and 3. So far so
good.

I then saved a query TT which says:
Select TC.ClientID
From T_Clients as TC Left Join
(Select ClientID
From T_Tasks
Where T_Tasks.Date = #2006-04-05#) as TT
On TC.ClientID = TT.ClientID

Now I run into problems:

I run TT query and from what you say I should expect it to show 2
records, with ClientID 2 and 4 respectively. Instead it displays 4
records, with ClientID 1, 2, 3 and 4 respectively, in that order, ie
every record in T_Clients.

If I view the saved SQL code in the query TT I find that Access has
changed the grammar slightly, to the following:

SELECT TC.ClientID
FROM T_Clients AS TC LEFT JOIN
[select ClientID from T_Tasks Where T_Tasks.Date = #2006-04-05#].
AS TT ON TC.ClientID=TT.ClientID;

(I entered carriage returns to prevent newsreader wordwrap at confusing
points).

I have not proceeded further, pending any advice you may have so far.

Thanks



John Spencer said:
If you are adding records you use an append query.
If you are changing the content of an existing record(s) you use an
UPDATE query.

You need to identify which clients have a record on that date

That query would look like:
SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#

You need to use the above query to find those that DO NOT exist. That
query would look like:
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID

So combining all that into one query, you end up with
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID


You could save each of the queries and them use the query as if it were
a table.
So save the first query as TT
Then the second query reads
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN TT
ON TC.ClientID = TT.ClientID

Save that as TC and the third query reads
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM TC


Hi all.
I have a database with two tables:
T_Clients
T_Tasks

T_Clients has two fields:
ClientID = autonumber, primary key
ClientName = text, indexed, no duplicates

T_Tasks has three fields:
TaskID = autonumber, primary key
ClientID = number
Date = date/time

There is a one(T_Clients)-to-many(T_Tasks) relationship between
ClientID fields with enforced referencial integrity.

I wish to run a query to add records to T_Tasks based on the following
rules:
For each value of ClientID in the T_Clients table, if there does not
already exist a corresponding record in T_Tasks wherein the value of
the Date field = 05 April 2006, then create such a record (with date 05
April 2006), but if there does already exist such a record then ignore
that value of ClientID.

So, which of "Update" query or "Append" query should I use, and what is
the syntax of the criteria expression, please? I hardly ever do action
queries, as you can tell. It "sounds" like an "Append" query but the
help notes indicate that that should be used for appending one table to
another, but I just want to add records to an existing table.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
J

Jack Sheet

Thank you so much. That worked (eventually!). It is going to take me a
while to work out WHY it works, but that is part of the fun.

I say "eventually" because I had a minor hitch. I simply copied and pasted
your code into the SQL area of a new query, saved it and ran it, and got an
error message where it was apparently trying to find an external database.
I re-examined the SQL code and found that Access had changed it to:

INSERT INTO T_Tasks ( ClientID, [Date] )
SELECT TC.ClientID, #4/5/2006#
FROM T_Clients AS TC LEFT JOIN [SELECT ClientID FROM T_Tasks
WHERE T_Tasks].[Date = #2006-04-05#] AS TT ON TC.ClientID=TT.ClientID
WHERE TT.ClientID is NULL;

Anyway, I then manually edited it back to what it was before I first saved
it, re-saved it and next time it ran sweet as a nut. If I view the saved
code NOW it shows

INSERT INTO T_Tasks ( ClientID, [Date] )
SELECT TC.ClientID, #4/5/2006#
FROM T_Clients AS TC LEFT JOIN [SELECT ClientID FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#]. AS TT ON TC.ClientID=TT.ClientID
WHERE TT.ClientID is NULL;

Ah well, definitely a step forward. Now to work out the logic :)

John Spencer said:
My error. I forgot to include the WHERE clause. The sample should have
read.

SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID
WHERE TT.ClientID is NULL


INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID
WHERE TT.ClientID is NULL

The re-format of the date is no problem. It is just Access forcing the
date into the US format of mm-dd-yyyy which it has to do in queries where
you are using a literal value. The yyyy mm dd format is unambiguous.



Jack Sheet said:
John, thank you very much. I don't understand the code at present but am
committed to doing so. My first step in understanding it will be to get
the code to work, to ensure that I am working with correct code and data.
At the moment I cannot get it to work and describe below my problem.

I have entered 4 records in T_Clients:
ClientID ClientName
1 Client1
2 Client2
3 Client3
4 Client4

I have entered 2 records in T_Tasks
(I am in UK where date formats are dd/mm/yyyy)
TaskID ClientID Date
1 1 05/04/2006
2 3 05/04/2006
3 2 31/12/2005

The desired end result will be a table T_Tasks showing
TaskID ClientID Date
1 1 05/04/2006
2 3 05/04/2006
3 2 31/12/2005
4 2 05/04/2006
5 4 05/04/2006

(I am not bothered if TaskID 4 and TaskID 5 are reversed).

I have saved a query "TC" which states
Select ClientID
From T_Tasks
Where T_Tasks.Date = #2006-04-05#

Incidentally, when I save, close and reopen the query the code is changed
slightly to:
Select ClientID
From T_Tasks
Where T_Tasks.Date = #4/5/2006#;

I assume that this change is irrelevant, as running the query produces
expected results, showing just two records clientID 1 and 3. So far so
good.

I then saved a query TT which says:
Select TC.ClientID
From T_Clients as TC Left Join
(Select ClientID
From T_Tasks
Where T_Tasks.Date = #2006-04-05#) as TT
On TC.ClientID = TT.ClientID

Now I run into problems:

I run TT query and from what you say I should expect it to show 2
records, with ClientID 2 and 4 respectively. Instead it displays 4
records, with ClientID 1, 2, 3 and 4 respectively, in that order, ie
every record in T_Clients.

If I view the saved SQL code in the query TT I find that Access has
changed the grammar slightly, to the following:

SELECT TC.ClientID
FROM T_Clients AS TC LEFT JOIN
[select ClientID from T_Tasks Where T_Tasks.Date = #2006-04-05#].
AS TT ON TC.ClientID=TT.ClientID;

(I entered carriage returns to prevent newsreader wordwrap at confusing
points).

I have not proceeded further, pending any advice you may have so far.

Thanks



John Spencer said:
If you are adding records you use an append query.
If you are changing the content of an existing record(s) you use an
UPDATE query.

You need to identify which clients have a record on that date

That query would look like:
SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#

You need to use the above query to find those that DO NOT exist. That
query would look like:
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID

So combining all that into one query, you end up with
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID


You could save each of the queries and them use the query as if it were
a table.
So save the first query as TT
Then the second query reads
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN TT
ON TC.ClientID = TT.ClientID

Save that as TC and the third query reads
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM TC


Hi all.
I have a database with two tables:
T_Clients
T_Tasks

T_Clients has two fields:
ClientID = autonumber, primary key
ClientName = text, indexed, no duplicates

T_Tasks has three fields:
TaskID = autonumber, primary key
ClientID = number
Date = date/time

There is a one(T_Clients)-to-many(T_Tasks) relationship between
ClientID fields with enforced referencial integrity.

I wish to run a query to add records to T_Tasks based on the following
rules:
For each value of ClientID in the T_Clients table, if there does not
already exist a corresponding record in T_Tasks wherein the value of
the Date field = 05 April 2006, then create such a record (with date 05
April 2006), but if there does already exist such a record then ignore
that value of ClientID.

So, which of "Update" query or "Append" query should I use, and what is
the syntax of the criteria expression, please? I hardly ever do action
queries, as you can tell. It "sounds" like an "Append" query but the
help notes indicate that that should be used for appending one table to
another, but I just want to add records to an existing table.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
J

Jack Sheet

Incidentally, is there a good reason for using what appears to me to be a
complicated expression for extracting TT rather than the WHERE field NOT IN
expression method also seems to work (and which I can understand without too
much brainache) (see other branch of thread)?

John Spencer said:
My error. I forgot to include the WHERE clause. The sample should have
read.

SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID
WHERE TT.ClientID is NULL


INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID
WHERE TT.ClientID is NULL

The re-format of the date is no problem. It is just Access forcing the
date into the US format of mm-dd-yyyy which it has to do in queries where
you are using a literal value. The yyyy mm dd format is unambiguous.



Jack Sheet said:
John, thank you very much. I don't understand the code at present but am
committed to doing so. My first step in understanding it will be to get
the code to work, to ensure that I am working with correct code and data.
At the moment I cannot get it to work and describe below my problem.

I have entered 4 records in T_Clients:
ClientID ClientName
1 Client1
2 Client2
3 Client3
4 Client4

I have entered 2 records in T_Tasks
(I am in UK where date formats are dd/mm/yyyy)
TaskID ClientID Date
1 1 05/04/2006
2 3 05/04/2006
3 2 31/12/2005

The desired end result will be a table T_Tasks showing
TaskID ClientID Date
1 1 05/04/2006
2 3 05/04/2006
3 2 31/12/2005
4 2 05/04/2006
5 4 05/04/2006

(I am not bothered if TaskID 4 and TaskID 5 are reversed).

I have saved a query "TC" which states
Select ClientID
From T_Tasks
Where T_Tasks.Date = #2006-04-05#

Incidentally, when I save, close and reopen the query the code is changed
slightly to:
Select ClientID
From T_Tasks
Where T_Tasks.Date = #4/5/2006#;

I assume that this change is irrelevant, as running the query produces
expected results, showing just two records clientID 1 and 3. So far so
good.

I then saved a query TT which says:
Select TC.ClientID
From T_Clients as TC Left Join
(Select ClientID
From T_Tasks
Where T_Tasks.Date = #2006-04-05#) as TT
On TC.ClientID = TT.ClientID

Now I run into problems:

I run TT query and from what you say I should expect it to show 2
records, with ClientID 2 and 4 respectively. Instead it displays 4
records, with ClientID 1, 2, 3 and 4 respectively, in that order, ie
every record in T_Clients.

If I view the saved SQL code in the query TT I find that Access has
changed the grammar slightly, to the following:

SELECT TC.ClientID
FROM T_Clients AS TC LEFT JOIN
[select ClientID from T_Tasks Where T_Tasks.Date = #2006-04-05#].
AS TT ON TC.ClientID=TT.ClientID;

(I entered carriage returns to prevent newsreader wordwrap at confusing
points).

I have not proceeded further, pending any advice you may have so far.

Thanks



John Spencer said:
If you are adding records you use an append query.
If you are changing the content of an existing record(s) you use an
UPDATE query.

You need to identify which clients have a record on that date

That query would look like:
SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#

You need to use the above query to find those that DO NOT exist. That
query would look like:
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID

So combining all that into one query, you end up with
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID


You could save each of the queries and them use the query as if it were
a table.
So save the first query as TT
Then the second query reads
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN TT
ON TC.ClientID = TT.ClientID

Save that as TC and the third query reads
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM TC


Hi all.
I have a database with two tables:
T_Clients
T_Tasks

T_Clients has two fields:
ClientID = autonumber, primary key
ClientName = text, indexed, no duplicates

T_Tasks has three fields:
TaskID = autonumber, primary key
ClientID = number
Date = date/time

There is a one(T_Clients)-to-many(T_Tasks) relationship between
ClientID fields with enforced referencial integrity.

I wish to run a query to add records to T_Tasks based on the following
rules:
For each value of ClientID in the T_Clients table, if there does not
already exist a corresponding record in T_Tasks wherein the value of
the Date field = 05 April 2006, then create such a record (with date 05
April 2006), but if there does already exist such a record then ignore
that value of ClientID.

So, which of "Update" query or "Append" query should I use, and what is
the syntax of the criteria expression, please? I hardly ever do action
queries, as you can tell. It "sounds" like an "Append" query but the
help notes indicate that that should be used for appending one table to
another, but I just want to add records to an existing table.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
J

Jack Sheet

Could I ask you please why the field Date is enclosed within square brackets
but ClientID is not?
 
J

John Spencer

The reason is speed. If you have a large set of records, then the speed of
NOT IN (...) will be significantly slower than the more complicated query.
If you have a few thousand records you may not see any real performance
difference - you humans are so S... L......O.........W.


Jack Sheet said:
Incidentally, is there a good reason for using what appears to me to be a
complicated expression for extracting TT rather than the WHERE field NOT
IN expression method also seems to work (and which I can understand
without too much brainache) (see other branch of thread)?

John Spencer said:
My error. I forgot to include the WHERE clause. The sample should have
read.

SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID
WHERE TT.ClientID is NULL


INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID
WHERE TT.ClientID is NULL

The re-format of the date is no problem. It is just Access forcing the
date into the US format of mm-dd-yyyy which it has to do in queries where
you are using a literal value. The yyyy mm dd format is unambiguous.



Jack Sheet said:
John, thank you very much. I don't understand the code at present but
am committed to doing so. My first step in understanding it will be to
get the code to work, to ensure that I am working with correct code and
data. At the moment I cannot get it to work and describe below my
problem.

I have entered 4 records in T_Clients:
ClientID ClientName
1 Client1
2 Client2
3 Client3
4 Client4

I have entered 2 records in T_Tasks
(I am in UK where date formats are dd/mm/yyyy)
TaskID ClientID Date
1 1 05/04/2006
2 3 05/04/2006
3 2 31/12/2005

The desired end result will be a table T_Tasks showing
TaskID ClientID Date
1 1 05/04/2006
2 3 05/04/2006
3 2 31/12/2005
4 2 05/04/2006
5 4 05/04/2006

(I am not bothered if TaskID 4 and TaskID 5 are reversed).

I have saved a query "TC" which states
Select ClientID
From T_Tasks
Where T_Tasks.Date = #2006-04-05#

Incidentally, when I save, close and reopen the query the code is
changed slightly to:
Select ClientID
From T_Tasks
Where T_Tasks.Date = #4/5/2006#;

I assume that this change is irrelevant, as running the query produces
expected results, showing just two records clientID 1 and 3. So far so
good.

I then saved a query TT which says:
Select TC.ClientID
From T_Clients as TC Left Join
(Select ClientID
From T_Tasks
Where T_Tasks.Date = #2006-04-05#) as TT
On TC.ClientID = TT.ClientID

Now I run into problems:

I run TT query and from what you say I should expect it to show 2
records, with ClientID 2 and 4 respectively. Instead it displays 4
records, with ClientID 1, 2, 3 and 4 respectively, in that order, ie
every record in T_Clients.

If I view the saved SQL code in the query TT I find that Access has
changed the grammar slightly, to the following:

SELECT TC.ClientID
FROM T_Clients AS TC LEFT JOIN
[select ClientID from T_Tasks Where T_Tasks.Date = #2006-04-05#].
AS TT ON TC.ClientID=TT.ClientID;

(I entered carriage returns to prevent newsreader wordwrap at confusing
points).

I have not proceeded further, pending any advice you may have so far.

Thanks



If you are adding records you use an append query.
If you are changing the content of an existing record(s) you use an
UPDATE query.

You need to identify which clients have a record on that date

That query would look like:
SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#

You need to use the above query to find those that DO NOT exist. That
query would look like:
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID

So combining all that into one query, you end up with
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM T_Clients as TC LEFT JOIN
(SELECT ClientID
FROM T_Tasks
WHERE T_Tasks.Date = #2006-04-05#) as TT
ON TC.ClientID = TT.ClientID


You could save each of the queries and them use the query as if it were
a table.
So save the first query as TT
Then the second query reads
SELECT TC.ClientID
FROM T_Clients as TC LEFT JOIN TT
ON TC.ClientID = TT.ClientID

Save that as TC and the third query reads
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM TC


Hi all.
I have a database with two tables:
T_Clients
T_Tasks

T_Clients has two fields:
ClientID = autonumber, primary key
ClientName = text, indexed, no duplicates

T_Tasks has three fields:
TaskID = autonumber, primary key
ClientID = number
Date = date/time

There is a one(T_Clients)-to-many(T_Tasks) relationship between
ClientID fields with enforced referencial integrity.

I wish to run a query to add records to T_Tasks based on the following
rules:
For each value of ClientID in the T_Clients table, if there does not
already exist a corresponding record in T_Tasks wherein the value of
the Date field = 05 April 2006, then create such a record (with date
05 April 2006), but if there does already exist such a record then
ignore that value of ClientID.

So, which of "Update" query or "Append" query should I use, and what
is the syntax of the criteria expression, please? I hardly ever do
action queries, as you can tell. It "sounds" like an "Append" query
but the help notes indicate that that should be used for appending one
table to another, but I just want to add records to an existing table.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Date is a reserved word and that is just a precaution on my part. Date is a
function that returns the current date.
I always try to give Date fields a name that indicates there function. For
instance, I would have named the date field
TaskDate
CompletionDate
ExecDate or even
dteDate


Jack Sheet said:
Could I ask you please why the field Date is enclosed within square
brackets but ClientID is not?
INSERT INTO T_Tasks (ClientID, [Date])
SELECT TC.ClientID, #2006-04-05#
FROM ......
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query produces unpredicted result 1
Update query syntax 2
Which field to include? 8
Relationships problems 4
Update query syntax help 2
Update query help please 2
Append query syntax requested 3
First append query 7

Top