Missing Data

  • Thread starter Thread starter bill_macdermott
  • Start date Start date
B

bill_macdermott

I am trying to run a simple query that pulls from a main
table that has 65,336 records. There are no criteria for
the query, however, it only seems to be pulling in 25,000
or so records. Any idea what I am doing wrong?

Thanks
Bill
 
Bill

Without a look at the SQL statement your query is using, it could be tough
to guess what might be happening. You know, a little like telling the auto
mechanic on the phone, "my car is making this funny sound ... what's wrong
with it?"
 
I suppose you are right. The SQL code is below. the
tabel "data" is the main table.

SELECT data.JobNo, dblist.[job title], data.FPI,
data.Overall, data.Compare, data.RateVExp, data.Sat,
data.PVRate, Methodology.[Methodology Name],
Marketing_objective.[Marketing Objective], [Product ID].
[Product Name], dblist.[n=], dblist.[job#1-4]
FROM [Product ID] INNER JOIN (((data INNER JOIN dblist ON
data.JobNo = dblist.jobno) INNER JOIN Methodology ON data.
[Methodology Number] = Methodology.[Methodology Number])
INNER JOIN Marketing_objective ON (Methodology.ID =
Marketing_objective.ID) AND (data.[Marketing Objective
ID] = Marketing_objective.[Marketing Objective ID])) ON
[Product ID].[Product ID] = data.[Product ID]
ORDER BY dblist.[job#1-4];
 
Bill,

<sql altered for text alignment only>

SELECT data.JobNo
,dblist.[job title]
,data.FPI
,data.Overall
,data.Compare
,data.RateVExp
,data.Sat
,data.PVRate
,Methodology.[Methodology Name]
,Marketing_objective.[Marketing Objective]
,[Product ID].[Product Name]
,dblist.[n=]
,dblist.[job#1-4]
FROM [Product ID]
INNER JOIN
(((data
INNER JOIN
dblist
ON data.JobNo = dblist.jobno)
INNER JOIN
Methodology
ON data.[Methodology Number] = Methodology.[Methodology Number])
INNER JOIN
Marketing_objective
ON (Methodology.ID = Marketing_objective.ID)
AND (data.[Marketing Objective ID] = Marketing_objective.[Marketing
Objective ID]))
ON [Product ID].[Product ID] = data.[Product ID]
ORDER BY dblist.[job#1-4];


This is not a simple query pulling from a main table. It is a five-way
join, with each INNER JOIN clause restricting more and more rows.

So that's your answer. 4 INNER JOIN clauses are restricting the return of
rows.


Sincerely,

Chris O.



bill said:
I suppose you are right. The SQL code is below. the
tabel "data" is the main table.

SELECT data.JobNo, dblist.[job title], data.FPI,
data.Overall, data.Compare, data.RateVExp, data.Sat,
data.PVRate, Methodology.[Methodology Name],
Marketing_objective.[Marketing Objective], [Product ID].
[Product Name], dblist.[n=], dblist.[job#1-4]
FROM [Product ID] INNER JOIN (((data INNER JOIN dblist ON
data.JobNo = dblist.jobno) INNER JOIN Methodology ON data.
[Methodology Number] = Methodology.[Methodology Number])
INNER JOIN Marketing_objective ON (Methodology.ID =
Marketing_objective.ID) AND (data.[Marketing Objective
ID] = Marketing_objective.[Marketing Objective ID])) ON
[Product ID].[Product ID] = data.[Product ID]
ORDER BY dblist.[job#1-4];
-----Original Message-----
Bill

Without a look at the SQL statement your query is using, it could be tough
to guess what might be happening. You know, a little like telling the auto
mechanic on the phone, "my car is making this funny sound ... what's wrong
with it?"

--
Good luck

Jeff Boyce
<Access MVP>

.
Click to expand...
Click to expand...
 
SInce you are using INNER JOINS there must be at least one matching record in
every table to generate a row. If there is NO marketing objective for the row
then there will be no row returned even if the Data table has a record.

You need to change the join types from INNER JOINs to LEFT or RIGHT JOINS. Try
clicking on the join line between the data table and the other tables and
picking the option that gives you all records and data and matching records in
the other table(s). This may still fail depending on how your tables are set
up. If it does try breaking down your query into a query returning just the
Data and Marketing objective, etc. Then combine these queries into one query.

SELECT data.JobNo, dblist.[job title], data.FPI,
data.Overall, data.Compare, data.RateVExp, data.Sat,
data.PVRate, Methodology.[Methodology Name],
Marketing_objective.[Marketing Objective], [Product ID].
[Product Name], dblist.[n=], dblist.[job#1-4]
FROM [Product ID] INNER JOIN (((data INNER JOIN dblist ON
data.JobNo = dblist.jobno) INNER JOIN Methodology ON data.
[Methodology Number] = Methodology.[Methodology Number])
INNER JOIN Marketing_objective ON (Methodology.ID =
Marketing_objective.ID) AND (data.[Marketing Objective
ID] = Marketing_objective.[Marketing Objective ID])) ON
[Product ID].[Product ID] = data.[Product ID]
ORDER BY dblist.[job#1-4];
I suppose you are right. The SQL code is below. the
tabel "data" is the main table.

SELECT data.JobNo, dblist.[job title], data.FPI,
data.Overall, data.Compare, data.RateVExp, data.Sat,
data.PVRate, Methodology.[Methodology Name],
Marketing_objective.[Marketing Objective], [Product ID].
[Product Name], dblist.[n=], dblist.[job#1-4]
FROM [Product ID] INNER JOIN (((data INNER JOIN dblist ON
data.JobNo = dblist.jobno) INNER JOIN Methodology ON data.
[Methodology Number] = Methodology.[Methodology Number])
INNER JOIN Marketing_objective ON (Methodology.ID =
Marketing_objective.ID) AND (data.[Marketing Objective
ID] = Marketing_objective.[Marketing Objective ID])) ON
[Product ID].[Product ID] = data.[Product ID]
ORDER BY dblist.[job#1-4];
-----Original Message-----
Bill

Without a look at the SQL statement your query is using, it could be tough
to guess what might be happening. You know, a little like telling the auto
mechanic on the phone, "my car is making this funny sound ... what's wrong
with it?"

--
Good luck

Jeff Boyce
<Access MVP>

.
Click to expand...
Click to expand...
 
Back
Top