Hi Karl,
Still struggling with this working. I get a message stating "...can't
append all records in the append query... it didn't add 111713 records
to the table due to key violations".
I had to change the query slightly as it wanted the table names in
where the field wasn't unique, so this is the query that I'm currently
using:
INSERT INTO tblResolved1stTime ( [Case-ID], [Created-By], [Closed-By],
[Create-Date], [Closed-Date], Resolved1stTime )
SELECT [P2P-Request].[Case-ID], [P2P-Request].[Created-By], [P2P-
Request].[Closed-By], [P2P-Request].[Create-Date], [P2P-Request].
[Closed-Date], IIf(DateDiff("n",[P2P-Request].[Create-Date],[P2P-
Request].[Closed-Date])<=60 And [P2P-Request].[Closed-By]=[P2P-
Request].[Created-By],"Yes","") AS Expr1
FROM [P2P-Request];
Anything obvious? If not, no worries as the first query you did seems
to work a treat. Just will do a delete on the table first and then run
the query.
Also, is it possible @ all to limit the records to people who are part
of a group?
I've got a further table call 'Users' and within there are two fields:
'Full-Name' and 'Primary-Group'. If I was to say that I only want
people in the 'Created-By' field who have a 'Primary-Group' of
Helpdesk, is this possible?
Thanks again for your help, really appreciated.
Al.
Try this --
INSERT INTO tblResolved1stTime ( [Case-ID], [Created-By], [Closed-By],
[Create-Date], [Closed-Date], Resolved1stTime )
SELECT [P2P-Request].[Case-ID], [P2P-Request].[Created-By],
[P2P-Request].[Closed-By], [P2P-Request].[Create-Date],
[P2P-Request].[Closed-Date],
IIf(DateDiff("n",[Create-Date],[Closed-Date])<=60 And
[Closed-By]=[Created-By],"Yes","") AS Expr1
FROM [P2P-Request], tblResolved1stTime
WHERE ((([P2P-Request].[Case-ID])<>[tblResolved1stTime].[Case-ID]));
--
KARL DEWEY
Build a little - Test a little
Karl,
This is brilliant. Does get a problem though when trying to update
the table:
- Includes all of the records so it states each time it's going to
update 11,000 etc.. rows (not just 8 for example if that's all that
was missing)
- If I then click on 'Yes' it states: "...Can't append all the
records to the apend query, due to key violations"
I have set the indexed value on the Case-ID (unique field) to be "Yes
(No Duplicates)" as per your suggestion.
Any ideas? Worse case scenario this is still really good and quick so
I could just do a delete all entries from the table first and then do
the query but if this is possible to do automatically by just doing
the missing records this would be appreciated.
On Oct 8, 4:22 pm, KARL DEWEY <
[email protected]>
wrote:
Case-ID, from P2P-Request, is Not already in tblResolved1stTime.
Set the Indexed property of tblResolved1stTime to Yes (No Duplicates)..
Try this query --
INSERT INTO tblResolved1stTime ( [Case-ID], [Created-By], [Closed-By],
[Create-Date], [Closed-Date], Resolved1stTime )
SELECT [P2P-Request].[Case-ID], [P2P-Request].[Created-By],
[P2P-Request].[Closed-By], [P2P-Request].[Create-Date],
[P2P-Request].[Closed-Date],
IIf(DateDiff("n",[Create-Date],[Closed-Date])<=60 And
[Closed-By]=[Created-By],"Yes","") AS Expr1
FROM [P2P-Request];
:
Hi,
Hope you can help. I need a query to do the following:
Query all records from P2P-Request where Closed-Date is Not Null (this
part I can do).
With this query, create/update table tblResolved1stTime with all of
the fields in the P2P-Request table (field names will be the exact
same in tblResolved1stTime) and the following field:
- Resolved1stTime (This needs to be populated with yes if the
difference between closed and created is 1 hour or less AND Closed-By
= Created-By)
Where:
- Case-ID, from P2P-Request, is Not already in tblResolved1stTime.
Fields (all within P2P-Request):
Case-ID
Created-By
Closed-By
Create-Date
Closed-Date
Thanks in advance for your help, al.- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
