|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
--
Return email address is not as DEEP as it appears
|
|
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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
news:%235gq%23ai3GHA.3812[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > 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.
>
> --
> Return email address is not as DEEP as it appears
>
|
|
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" <spencer[ at ]chpdm.edu> wrote in message
news:uEtmdwj3GHA.1796[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > 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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
> news:%235gq%23ai3GHA.3812[ at ]TK2MSFTNGP06.phx.gbl...
>> 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.
>>
>> --
>> Return email address is not as DEEP as it appears
>>
>
>
|
|
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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
news:uKHK32k3GHA.4900[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > 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" <spencer[ at ]chpdm.edu> wrote in message
> news:uEtmdwj3GHA.1796[ at ]TK2MSFTNGP06.phx.gbl...
>> 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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
>> news:%235gq%23ai3GHA.3812[ at ]TK2MSFTNGP06.phx.gbl...
>>> 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.
>>>
>>> --
>>> Return email address is not as DEEP as it appears
>>>
>>
>>
>
>
|
|
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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
news:uKHK32k3GHA.4900[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > 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" <spencer[ at ]chpdm.edu> wrote in message
> news:uEtmdwj3GHA.1796[ at ]TK2MSFTNGP06.phx.gbl...
>> 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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
>> news:%235gq%23ai3GHA.3812[ at ]TK2MSFTNGP06.phx.gbl...
>>> 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.
>>>
>>> --
>>> Return email address is not as DEEP as it appears
>>>
>>
>>
>
>
|
|
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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
news:el2%23%23Ol3GHA.3464[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > 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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
> news:uKHK32k3GHA.4900[ at ]TK2MSFTNGP03.phx.gbl...
>> 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" <spencer[ at ]chpdm.edu> wrote in message
>> news:uEtmdwj3GHA.1796[ at ]TK2MSFTNGP06.phx.gbl...
>>> 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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
>>> news:%235gq%23ai3GHA.3812[ at ]TK2MSFTNGP06.phx.gbl...
>>>> 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.
>>>>
>>>> --
>>>> Return email address is not as DEEP as it appears
>>>>
>>>
>>>
>>
>>
>
>
|
|
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" <spencer[ at ]chpdm.edu> wrote in message
news:ue6meXl3GHA.1608[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
> news:uKHK32k3GHA.4900[ at ]TK2MSFTNGP03.phx.gbl...
>> 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" <spencer[ at ]chpdm.edu> wrote in message
>> news:uEtmdwj3GHA.1796[ at ]TK2MSFTNGP06.phx.gbl...
>>> 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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
>>> news:%235gq%23ai3GHA.3812[ at ]TK2MSFTNGP06.phx.gbl...
>>>> 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.
>>>>
>>>> --
>>>> Return email address is not as DEEP as it appears
>>>>
>>>
>>>
>>
>>
>
>
|
|
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" <spencer[ at ]chpdm.edu> wrote in message
news:ue6meXl3GHA.1608[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
> news:uKHK32k3GHA.4900[ at ]TK2MSFTNGP03.phx.gbl...
>> 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" <spencer[ at ]chpdm.edu> wrote in message
>> news:uEtmdwj3GHA.1796[ at ]TK2MSFTNGP06.phx.gbl...
>>> 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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
>>> news:%235gq%23ai3GHA.3812[ at ]TK2MSFTNGP06.phx.gbl...
>>>> 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.
>>>>
>>>> --
>>>> Return email address is not as DEEP as it appears
>>>>
>>>
>>>
>>
>>
>
>
|
|
Could I ask you please why the field Date is enclosed within square brackets
but ClientID is not?
[Quoted Text] > INSERT INTO T_Tasks (ClientID, [Date])
> SELECT TC.ClientID, #2006-04-05#
> FROM ......
|
|
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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
news:%23tD7vBm3GHA.4228[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > 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" <spencer[ at ]chpdm.edu> wrote in message
> news:ue6meXl3GHA.1608[ at ]TK2MSFTNGP04.phx.gbl...
>> 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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
>> news:uKHK32k3GHA.4900[ at ]TK2MSFTNGP03.phx.gbl...
>>> 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" <spencer[ at ]chpdm.edu> wrote in message
>>> news:uEtmdwj3GHA.1796[ at ]TK2MSFTNGP06.phx.gbl...
>>>> 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" <mind-the-gap[ at ]DEEPblueyonder.co.uk> wrote in message
>>>> news:%235gq%23ai3GHA.3812[ at ]TK2MSFTNGP06.phx.gbl...
>>>>> 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.
>>>>>
>>>>> --
>>>>> Return email address is not as DEEP as it appears
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
|
|
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" <no-one[ at ]nowhere.com> wrote in message
news:uxvgzCn3GHA.836[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > 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 ......
>
>
|
|
|