New Posts  All Forums:Forum Nav:

Access SQL Help

post #1 of 2
Thread Starter 
Hey guys,

I am just doing some basic SQL within an access database. I am trying to enter in a ''Where'' clause to stop duplicate date appearing when I use an append query. I am using the below code to avoid duplicates

WHERE NOT EXISTS (SELECT *
FROM Invoice5
WHERE (Invoice5.ID=JobCard.ID))

Please see the entire code I am using in my query below.


INSERT INTO Invoice5 ( ID, [Job No], Item, [Deposit Invoice], [Delivery Invoice 50%], [Invoice 100%], [Invoice Date], [Order Acknowledgement], [Delivery Cost], [Customer Notes], [Client Reference], [Actual Delivery Date], [Expected Delivery], Price, Quantity, [Value], [Drg Catalogue ID], [Customer ID], [Delivery ID], Finish )
SELECT DISTINCTROW JobCard.ID, JobCard.[Job No], JobCard.Item, JobCard.[Deposit Invoice], JobCard.[Delivery Invoice 50%], JobCard.[Invoice 100%], JobCard.[Invoice Date], JobCard.[Order Acknowledgement], JobCard.[Delivery Cost], JobCard.[Customer Notes], JobCard.[Client Reference], JobCard.[Actual Delivery Date], JobCard.[Expected Delivery], JobCard.Price, JobCard.Quantity, [Price]*[Quantity] AS [Value], JobCard.[Drg Catalogue ID], JobCard.[Customer ID], JobCard.[Delivery ID], JobCard.Finish
FROM JobCard
WHERE (((JobCard.[Deposit Invoice])=Yes) AND ((JobCard.[Invoice Date])=Date())) OR (((JobCard.[Delivery Invoice 50%])=Yes) AND ((JobCard.[Invoice Date])=Date())) OR (((JobCard.[Invoice 100%])=Yes) AND ((JobCard.[Invoice Date])=Date()))
WHERE NOT EXISTS (SELECT *
FROM Invoice5
WHERE (Invoice5.ID=JobCard.ID))
WITH OWNERACCESS OPTION;

....................................................................................................................................

When I try to run the query I am presented with. (Click to view)

http://www.rigshowcase.com/pic.php?u=2620NRHP&i=15274


Any idea what I am doing wrong ?

Thanks guys.
post #2 of 2
I don't work with access, but I do work with sql. In sql server you can't have two where clauses in the same query. You can nest a subquery in your where clause, which what you did in the NOT EXISTS function. You can't have two consecutive where clause in the same query, you have to join the conditions with an AND statement. First step, give something like this a try.

Also note, you can use some basic styling to make your code more readable. See what I did below.
Code:
INSERT INTO Invoice5 ( ID, [Job No], Item, [Deposit Invoice], [Delivery Invoice 50%], [Invoice 100%], [Invoice Date], [Order Acknowledgement], [Delivery Cost], [Customer Notes], [Client Reference], [Actual Delivery Date], [Expected Delivery], Price, Quantity, [Value], [Drg Catalogue ID], [Customer ID], [Delivery ID], Finish )
SELECT DISTINCTROW 
JobCard.ID
,JobCard.[Job No]
,JobCard.Item
,JobCard.[Deposit Invoice]
,JobCard.[Delivery Invoice 50%]
,JobCard.[Invoice 100%]
,JobCard.[Invoice Date]
,JobCard.[Order Acknowledgement]
,JobCard.[Delivery Cost]
,JobCard.[Customer Notes]
,JobCard.[Client Reference]
,JobCard.[Actual Delivery Date]
,JobCard.[Expected Delivery]
,JobCard.Price
,JobCard.Quantity
,[Price]*[Quantity] AS [Value]
,JobCard.[Drg Catalogue ID]
,JobCard.[Customer ID]
,JobCard.[Delivery ID]
,JobCard.Finish
FROM JobCard
WHERE ((((JobCard.[Deposit Invoice])=Yes) AND ((JobCard.[Invoice Date])=Date())) OR (((JobCard.[Delivery Invoice 50%])=Yes) AND ((JobCard.[Invoice Date])=Date())) OR (((JobCard.[Invoice 100%])=Yes) AND ((JobCard.[Invoice Date])=Date()))) 
AND NOT EXISTS 
(
SELECT *
FROM Invoice5
WHERE (Invoice5.ID=JobCard.ID)
)
WITH OWNERACCESS OPTION;

You might have to clean that up a little bit, because the syntax for access and sql server is a tad different.

If this were my project, I would use a transaction statement and check for duplicates after the insertion, and then roll back the changes. You could also write a function that checks for a matching invoice based on the ID, and returns true or false (at least you can in sql server).
Edited by mothergoose729 - 9/27/15 at 12:09pm
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming