ACCESS FEED: Re: [MS_AccessPros] Append Query Where Clause question

Jumat, 30 September 2011

Re: [MS_AccessPros] Append Query Where Clause question

Troy -

Alone with John's advice, i'd like to add this: You should always specify the fields in both the target and the source. That way you are sure to get everykthing where it belongs should the table structures change in any way in the future.

INSERT INTO dbo_Key_Scans(LastName, FirstName, MyDate)

SELECT LastName, FirstName, MyDate
...

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile/Bill.Mosca

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Troy-
>
> To add a WHERE clause, the table referenced must be in the FROM clause. Maybe
> you need to do a LEFT JOIN between TransMast and Key_Scans, then select only the
> rows where ScanDateTime1 Is Null - indicating the matching rows from TransMast
> don't exist yet in Key_Scans. I would take a stab at the SQL, but I don't know
> what should match in the two tables.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Troy Sherven
> Sent: Friday, September 30, 2011 4:48 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Append Query Where Clause question
>
> Hi,
>
>
>
> I'm new to the group and new to Access. I'm having some issues with an
> append query. Here is the SQL:
>
>
>
> INSERT INTO dbo_Key_Scans
>
> SELECT dbo_TransMast.*
>
> FROM dbo_TransMast
>
> WHERE (((dbo_Key_Scans.ScanDateTime1) is Null));
>
>
>
> When I run this query, Access is prompting me with a parameter for
> dbo_Key_Scans.ScanDateTime1. My first thought was, "maybe I have
> something spelled wrong?" I checked it numerous times. The query runs
> fine without the WHERE clause. What am I doing wrong?
>
>
>
> Thanks,
>
>
>
> Troy Sherven
>
> MCD, Inc.
>
> Madison, WI
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>