November 2, 2004 at 7:42 pm
Let me start off by saying I am NOT a SQL programmer and very much an Access beginner, but can someone help me figure out how to redo this code so that when I create a query and append to a table, only the new data is added? Thanks!
INSERT INTO Table1 ( [Application Field], CountOfApplication )
SELECT First(Software.Application) AS [Application Field], Count(Software.Application) AS CountOfApplication
FROM Software
WHERE NOT EXISTS (SELECT * from Table1
WHERE Table1.Application = Software.Application)
GROUP BY Software.Application
HAVING (((Count(Software.Application))>0));
November 4, 2004 at 7:20 am
This should do the trick (just removed First() and the having that didn't filter anything):
INSERT INTO Table1 ( [Application Field], CountOfApplication )
SELECT Software.Application AS [Application Field], Count(Software.Application) AS CountOfApplication
FROM Software
WHERE NOT EXISTS (SELECT * from Table1
WHERE Table1.Application = Software.Application)
GROUP BY Software.Application
if that doesn't work I'll need the table info and some sample data to do some testing on the query myself.
HTH.
November 4, 2004 at 7:31 am
Katrina,
I created two tables in a SQL Server database and linked to them from Access through an ODBC connection. The tables are Table1 and Software.
I inserted the following data into the Application field of the Software table:
When I ran your query in Access I received the error Table1.[Application Field] does not exist. I assumed that your reference to Table1.Application was intended to reference the same field as Table1.[Application Field] so I renamed the field in Table1 from Application to [Application Field] and changed the reference Table1.Application to Table1.[Application Field].
With this done, I again ran your query and Table1 had 3 records inserted as follows:
[Application Field] CountOfApplication
Is this not the desired outcome?
November 4, 2004 at 7:53 am
Nice call cjensen... I missed that one.
November 5, 2004 at 1:35 pm
INSERT INTO Table1 ( [Application Field], CountOfApplication )
SELECT Application, COUNT(*)
FROM Software
WHERE Application NOT IN (SELECT [Application Field] FROM Table1)
GROUP BY Application
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply