Confused.... Make Table

[mralmackay]

I've been trying to work on a query to do resolved 1st time based on
some recommendations through the groups.

However... this isn't still quite working as I need it to. So far
I've done the following:
INSERT INTO tblResolved1stTime ( [Case-ID], [Created-By], [Closed-By],
[Create-Date], [Closed-Date], Resolved1stTime, [Primary-Group] )
SELECT DISTINCT [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","No") AS [Resolved 1st Time], User.
[Primary-Group]
FROM [User] LEFT JOIN [P2P-Request] ON User.[Full-Name] = [P2P-
Request].[Created-By]
WHERE ((([P2P-Request].[Case-ID]) Is Not Null) AND ((User.[Primary-
Group]) Like "%Help%"));

This inserts records successfully into the table specified
(tblResolved1stTime) the first time I run it. However when I run it
when new records have been created (in P2P-Request) I only want it to
post records in where the Case-ID within P2P-Request doesn't already
exist in tblResolved1stTime.

Case-ID is a unique field in each table.

Any suggestions on how I can do this part? Thanks in advance, Al.

 

[mralmackay]

Extra note on this. The error that I get is related to Key Violations
for x number of records (basically all the records that are already in
the table).

Thanks, Al.

 

[Allen Browne]

Use the unmatched query wizard to identify the records that are not present.

If you can't do that, an alternative would be to use a subquery in the WHERE
clause to skip the records that already exist. If you are not used to
subqueries, here's an introduction:
http://allenbrowne.com/subquery-01.html

I think you understand why the key violations are occurring, so you probably
don't need this explanation of what to look for:
http://allenbrowne.com/casu-19.html

A less desirable way to do it might be to use Execute without dbFailOnError.
This just ignores (doesn't report) the errors, and still gives you a count
of the actual number inserted. If Execute is new, see:
http://allenbrowne.com/ser-60.html

As a side comment, you have a table named User, which is a reserved word.
JET may have trouble interpreting this query, particularly as the table
appears in the WHERE clause without square brackets around the name. You may
find life easier if you avoid using reserved words as the names of tables or
fields. There's a few thousand names to avoid, so it may help to refer to a
list like this when designing tables:
http://allenbrowne.com/AppIssueBadWord.html#U

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Extra note on this. The error that I get is related to Key Violations
for x number of records (basically all the records that are already in
the table).

Thanks, Al.

I've been trying to work on a query to do resolved 1st time based on
some recommendations through the groups.

However... this isn't still quite working as I need it to. So far
I've done the following:
INSERT INTO tblResolved1stTime ( [Case-ID], [Created-By], [Closed-By],
[Create-Date], [Closed-Date], Resolved1stTime, [Primary-Group] )
SELECT DISTINCT [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","No") AS [Resolved 1st Time], User.
[Primary-Group]
FROM [User] LEFT JOIN [P2P-Request] ON User.[Full-Name] = [P2P-
Request].[Created-By]
WHERE ((([P2P-Request].[Case-ID]) Is Not Null) AND ((User.[Primary-
Group]) Like "%Help%"));

This inserts records successfully into the table specified
(tblResolved1stTime) the first time I run it. However when I run it
when new records have been created (in P2P-Request) I only want it to
post records in where the Case-ID within P2P-Request doesn't already
exist in tblResolved1stTime.

Case-ID is a unique field in each table.

Any suggestions on how I can do this part? Thanks in advance, Al.

 

[mralmackay]

Hi Allen,

Thanks for these links/advice. Going to try these today.

Appreciate comment about table name - these are external tables from
our source system. Normally I use the prefix tbl etc..

Thanks again for your help, and your website as well - Had a link ages
ago which I lost to this but this is really helpful resource to people
like myself (will make sure I'm better with managing my favourite
links going forward!)

Al.

Use the unmatched query wizard to identify the records that are not present.

If you can't do that, an alternative would be to use a subquery in the WHERE
clause to skip the records that already exist. If you are not used to
subqueries, here's an introduction:
   http://allenbrowne.com/subquery-01.html

I think you understand why the key violations are occurring, so you probably
don't need this explanation of what to look for:
   http://allenbrowne.com/casu-19.html

A less desirable way to do it might be to use Execute without dbFailOnError.
This just ignores (doesn't report) the errors, and still gives you a count
of the actual number inserted. If Execute is new, see:
   http://allenbrowne.com/ser-60.html

As a side comment, you have a table named User, which is a reserved word.
JET may have trouble interpreting this query, particularly as the table
appears in the WHERE clause without square brackets around the name. You may
find life easier if you avoid using reserved words as the names of tablesor
fields. There's a few thousand names to avoid, so it may help to refer toa
list like this when designing tables:
   http://allenbrowne.com/AppIssueBadWord.html#U

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


Extra note on this.  The error that I get is related to Key Violations
for x number of records (basically all the records that are already in
the table).

Thanks, Al.

I've been trying to work on a query to do resolved 1st time based on
some recommendations through the groups.

However... this isn't still quite working as I need it to. So far
I've done the following:
INSERT INTO tblResolved1stTime ( [Case-ID], [Created-By], [Closed-By],
[Create-Date], [Closed-Date], Resolved1stTime, [Primary-Group] )
SELECT DISTINCT [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","No") AS [Resolved 1st Time], User.
[Primary-Group]
FROM [User] LEFT JOIN [P2P-Request] ON User.[Full-Name] = [P2P-
Request].[Created-By]
WHERE ((([P2P-Request].[Case-ID]) Is Not Null) AND ((User.[Primary-
Group]) Like "%Help%"));

This inserts records successfully into the table specified
(tblResolved1stTime) the first time I run it. However when I run it
when new records have been created (in P2P-Request) I only want it to
post records in where the Case-ID within P2P-Request doesn't already
exist in tblResolved1stTime.

Case-ID is a unique field in each table.

Any suggestions on how I can do this part? Thanks in advance, Al.- Hidequoted text -

- Show quoted text -