need append query SQL help

  • Thread starter Slez via AccessMonster.com
  • 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!
 
S

Steve Schapel

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
 
J

Jeff L

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 =
Customer.JobNumber

Hope that helps!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top