need append query SQL help

  • Thread starter Thread starter Slez via AccessMonster.com
  • Start date Start date
S

Slez via AccessMonster.com

I'm trying to write the SQL to import a recordset from linked tables. My
current code is as follows:

INSERT INTO tblProject ( JobNumber, ProjectName, SiteStreetAddress, SiteCity,
SiteState, SiteZip, GCName, GCContact, GCStreetAddress, GCCity, GCState,
GCZip )
SELECT Project.JobNumber, Project.ProjectName, Project.SiteStreetAddress,
Project.SiteCity, Project.SiteState, Project.SiteZip, Project.GCName, Project.
GCContact, Customer.GCStreetAddress, Customer.GCCity, Customer.GCState,
Customer.GCZip
FROM Project, Customer
WHERE (((Project.JobNumber)="065812"));

As you can see, I have records from 2 tables: Project & Customer, that I'm
trying to add to one table: tblProject. What I currently get is all records
from the Customer table, rather than just the one that relates to JobNumber
"065812". I have spent alot of time searching tutorials and examples but
cannot grasp what I need to do to distinguish when the records are from more
than one table. To build on this, I will also need to expand this SQL to
include inserting records into tblItem & tblItemDetail from linked tables
Item and ItemDetail. I want to get the above SQL working before I add to it.

I appreciate any help!
 
Slez,

This query is currently evaluating as a Cartesian product. There is no
Join between the Customer and Project tables. I assume these tables
have a "field in common" which is the basis of the relationship between
them? Most likely the Project table includes a CustomerID field, or
some such, to identify which Customer the Project relates to? If so,
the FROM clause of the query should look something like this...
FROM Project INNER JOIN Customer ON Project.CustomerID =
Customer.CustomerID
 
Project and Customer need to be joined together somehow. In other
words they need to have a field in common so that you can grab the
information that you want. By your description it sounds like that
field is JobNumber. Your From statement needs to change to
From Project Inner Join Customer ON Project.JobNumber =
Click to expand...
Customer.JobNumber

Hope that helps!
 
Back
Top