One last problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Okay, I have one last problem and then my database will be perfect (for our
needs anyway) :) I am using a database for multiple projects, we deal with
many different companies and naturally there will times when some companies
will be working on multiple projects. I have my query set to provide
information by our assisgned project number however in the situations where I
have one company working on two projects my query will duplicate information.
Example Medco is working on two projects, the value on project 1 is 2
million. When I run the query Medco shows up twice and the proper information
for project 1 is duplicated. This duplication only happens when a company is
on more than one project. Any Idea?
 
Lori said:
Okay, I have one last problem and then my database will be perfect
(for our needs anyway) :) I am using a database for multiple
projects, we deal with many different companies and naturally there
will times when some companies will be working on multiple projects.
I have my query set to provide information by our assisgned project
number however in the situations where I have one company working on
two projects my query will duplicate information. Example Medco is
working on two projects, the value on project 1 is 2 million. When I
run the query Medco shows up twice and the proper information for
project 1 is duplicated. This duplication only happens when a
company is on more than one project. Any Idea?
Click to expand...

It sounds like your query is joining (on the company ID field) to a
table or query that contains two records for the company. Post the SQL
for the query, and maybe we can figure out where the problem lies.
 
Okay I hope this helps. I've tried other options, there are multiple
similarities in the two tables but this is the only one that will give me any
reasonable results at all.

SELECT [Subcontractor Status Log].[Project#], [Subcontractor Status
Log].[#], [Subcontractor Status Log].Subcontractor, [Subcontractor Status
Log].[Scope of Work], [Subcontractor Status Log].[Spec Sec], [Subcontractor
Status Log].Value, [Subcontractor Status Log].[AL No], [Subcontractor Status
Log].[AL Date], [Subcontractor Status Log].[AL Executed], [Subcontractor
Status Log].Type, [Subcontractor Status Log].LOI, [Subcontractor Status
Log].Issued, [Subcontractor Status Log].Returned, [Subcontractor Status
Log].Executed, [Subcontractor Status Log].Status, [Subcontractor Insurance
Status].[Status:], [Subcontractor Status Log].[Date Rec]
FROM [Subcontractor Status Log] INNER JOIN [Subcontractor Insurance Status]
ON [Subcontractor Status Log].Subcontractor = [Subcontractor Insurance
Status].Subcontractor
WHERE ((([Subcontractor Status Log].[Project#])=[Which Project #?]) AND
(([Subcontractor Status Log].Type)="short form" Or ([Subcontractor Status
Log].Type)="apm" Or ([Subcontractor Status Log].Type)="subcontract" Or
([Subcontractor Status Log].Type)="change order") AND (([Subcontractor Status
Log].Status)="Ongoing"))
ORDER BY [Subcontractor Status Log].[#];
 
Lori said:
Okay I hope this helps. I've tried other options, there are multiple
similarities in the two tables but this is the only one that will
give me any reasonable results at all.

SELECT [Subcontractor Status Log].[Project#], [Subcontractor Status
Log].[#], [Subcontractor Status Log].Subcontractor, [Subcontractor
Status Log].[Scope of Work], [Subcontractor Status Log].[Spec Sec],
[Subcontractor Status Log].Value, [Subcontractor Status Log].[AL No],
[Subcontractor Status Log].[AL Date], [Subcontractor Status Log].[AL
Executed], [Subcontractor Status Log].Type, [Subcontractor Status
Log].LOI, [Subcontractor Status Log].Issued, [Subcontractor Status
Log].Returned, [Subcontractor Status Log].Executed, [Subcontractor
Status Log].Status, [Subcontractor Insurance Status].[Status:],
[Subcontractor Status Log].[Date Rec]
FROM [Subcontractor Status Log] INNER JOIN [Subcontractor Insurance
Status] ON [Subcontractor Status Log].Subcontractor = [Subcontractor
Insurance Status].Subcontractor
WHERE ((([Subcontractor Status Log].[Project#])=[Which Project #?])
AND (([Subcontractor Status Log].Type)="short form" Or
([Subcontractor Status Log].Type)="apm" Or ([Subcontractor Status
Log].Type)="subcontract" Or ([Subcontractor Status Log].Type)="change
order") AND (([Subcontractor Status Log].Status)="Ongoing"))
ORDER BY [Subcontractor Status Log].[#];
Click to expand...

Is the table [Subcontractor Insurance Status] actually related directly
to [Subcontractor Status Log], or is really related to a table of
Subcontractors (not included in the query)? I'm guessing that the
latter is the case, and if it is, you should not be joining this:

[Subcontractor Status Log] <--> [Subcontractor Insurance Status]

but rather this:

[Subcontractor Status Log] <--> [Subcontractors] <--->
[Subcontractor Insurance Status]

Note that the Subcontractors table might actually be a subset of a more
general Companies table, returned by a query, but *logically* it's a
table.

If there is no such Subcontractors table (or query), there ought to be.
Then your query would look something like the following rough "air SQL":

SELECT <various fields>
FROM
[Subcontractor Status Log]
INNER JOIN
[Subcontractors]
ON [Subcontractor Status Log].Subcontractor =
[Subcontractors].Subcontractor
INNER JOIN
[Subcontractor Insurance Status]
ON [Subcontractor Insurance Status].Subcontractor =

[Subcontractors].Subcontractor
WHERE
<various selection criteria, including
[Subcontractor Status Log].[Project#]>
ORDER BY [Subcontractor Status Log].[#];
 
Okay, there are three tables - Subcontractor Status Log which provides
specific contract info. Subcontractors - which provides the name address, etc
for each company and Subcontractor Insurance Status which obviously provides
details on their insurance for the subcontractors each project.

All of the information in the query and report comes from the Status Log
with the exception of one item from the Insurance status (whether the
insurance is approved or not). With the exception of the duplications caused
when a subcontractor is on more than project it works perfectly and I'm not
sure how this one item might be causing this problem. The Subcontractor table
is linked to the Subcontractor Status Table to access the Subcontractor
Names. I've attempted to put the Subcontractor Table into the Query pulling
the Subcontractor Name from that table and it doesn't help. I'm not sure if
that was your intent. HELP
--
Lori A. Pong


Dirk Goldgar said:
Lori said:
Okay I hope this helps. I've tried other options, there are multiple
similarities in the two tables but this is the only one that will
give me any reasonable results at all.

SELECT [Subcontractor Status Log].[Project#], [Subcontractor Status
Log].[#], [Subcontractor Status Log].Subcontractor, [Subcontractor
Status Log].[Scope of Work], [Subcontractor Status Log].[Spec Sec],
[Subcontractor Status Log].Value, [Subcontractor Status Log].[AL No],
[Subcontractor Status Log].[AL Date], [Subcontractor Status Log].[AL
Executed], [Subcontractor Status Log].Type, [Subcontractor Status
Log].LOI, [Subcontractor Status Log].Issued, [Subcontractor Status
Log].Returned, [Subcontractor Status Log].Executed, [Subcontractor
Status Log].Status, [Subcontractor Insurance Status].[Status:],
[Subcontractor Status Log].[Date Rec]
FROM [Subcontractor Status Log] INNER JOIN [Subcontractor Insurance
Status] ON [Subcontractor Status Log].Subcontractor = [Subcontractor
Insurance Status].Subcontractor
WHERE ((([Subcontractor Status Log].[Project#])=[Which Project #?])
AND (([Subcontractor Status Log].Type)="short form" Or
([Subcontractor Status Log].Type)="apm" Or ([Subcontractor Status
Log].Type)="subcontract" Or ([Subcontractor Status Log].Type)="change
order") AND (([Subcontractor Status Log].Status)="Ongoing"))
ORDER BY [Subcontractor Status Log].[#];
Click to expand...

Is the table [Subcontractor Insurance Status] actually related directly
to [Subcontractor Status Log], or is really related to a table of
Subcontractors (not included in the query)? I'm guessing that the
latter is the case, and if it is, you should not be joining this:

[Subcontractor Status Log] <--> [Subcontractor Insurance Status]

but rather this:

[Subcontractor Status Log] <--> [Subcontractors] <--->
[Subcontractor Insurance Status]

Note that the Subcontractors table might actually be a subset of a more
general Companies table, returned by a query, but *logically* it's a
table.

If there is no such Subcontractors table (or query), there ought to be.
Then your query would look something like the following rough "air SQL":

SELECT <various fields>
FROM
[Subcontractor Status Log]
INNER JOIN
[Subcontractors]
ON [Subcontractor Status Log].Subcontractor =
[Subcontractors].Subcontractor
INNER JOIN
[Subcontractor Insurance Status]
ON [Subcontractor Insurance Status].Subcontractor =

[Subcontractors].Subcontractor
WHERE
<various selection criteria, including
[Subcontractor Status Log].[Project#]>
ORDER BY [Subcontractor Status Log].[#];


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Lori said:
Okay, there are three tables - Subcontractor Status Log which provides
specific contract info. Subcontractors - which provides the name
address, etc for each company and Subcontractor Insurance Status
which obviously provides details on their insurance for the
subcontractors each project.
Click to expand...

Are you saying that [Subcontractor Insurance Status] is dependent on
both subcontractor and project? Then there's a [Project#] field in that
table, too? If that is the case, then you can use something very
similar to your original query, except that you need to join the two
tables on *both* [Subcontactor] and [Project#]. Like this:

--------- start of SQL ---------
SELECT
S.[Project#], S.[#], S.Subcontractor, S.[Scope of Work],
S.[Spec Sec], S.Value, S.[AL No], S.[AL Date], S.[AL Executed],
S.Type, S.LOI, S.Issued, S.Returned, S.Executed, S.Status,
I.[Status:], S.[Date Rec]
FROM
[Subcontractor Status Log] S
INNER JOIN [Subcontractor Insurance Status] I
ON
S.Subcontractor = I.Subcontractor
AND S.[Project#] = I.[Project#]
WHERE ((S.[Project#]=[Which Project #?])
AND (S.Type="short form" Or S.Type="apm"
Or S.Type="subcontract" Or S.Type="change order")
AND ((S.Status)="Ongoing"))
ORDER BY S.[#];

--------- end of SQL ---------

I simplified it a bit by aliasing the tables to S and I.
All of the information in the query and report comes from the Status
Log with the exception of one item from the Insurance status (whether
the insurance is approved or not). With the exception of the
duplications caused when a subcontractor is on more than project it
works perfectly and I'm not sure how this one item might be causing
this problem.
Click to expand...

The problem is coming from the way a SQL join resolves unrelated tables.
The above SQL should fix it, if my new understanding of the table
relationships is correct.

Note, though, that the use of an inner join in the query presumes that
there will be a matching record in [Subcontractor Insurance Status] for
every subcontractor and project in [Subcontractor Status Log]. If
that's not the case, you need to use a left join.
 
Thank You Thank You. Works perfectly.
--
Lori A. Pong


Dirk Goldgar said:
Lori said:
Okay, there are three tables - Subcontractor Status Log which provides
specific contract info. Subcontractors - which provides the name
address, etc for each company and Subcontractor Insurance Status
which obviously provides details on their insurance for the
subcontractors each project.
Click to expand...

Are you saying that [Subcontractor Insurance Status] is dependent on
both subcontractor and project? Then there's a [Project#] field in that
table, too? If that is the case, then you can use something very
similar to your original query, except that you need to join the two
tables on *both* [Subcontactor] and [Project#]. Like this:

--------- start of SQL ---------
SELECT
S.[Project#], S.[#], S.Subcontractor, S.[Scope of Work],
S.[Spec Sec], S.Value, S.[AL No], S.[AL Date], S.[AL Executed],
S.Type, S.LOI, S.Issued, S.Returned, S.Executed, S.Status,
I.[Status:], S.[Date Rec]
FROM
[Subcontractor Status Log] S
INNER JOIN [Subcontractor Insurance Status] I
ON
S.Subcontractor = I.Subcontractor
AND S.[Project#] = I.[Project#]
WHERE ((S.[Project#]=[Which Project #?])
AND (S.Type="short form" Or S.Type="apm"
Or S.Type="subcontract" Or S.Type="change order")
AND ((S.Status)="Ongoing"))
ORDER BY S.[#];

--------- end of SQL ---------

I simplified it a bit by aliasing the tables to S and I.
All of the information in the query and report comes from the Status
Log with the exception of one item from the Insurance status (whether
the insurance is approved or not). With the exception of the
duplications caused when a subcontractor is on more than project it
works perfectly and I'm not sure how this one item might be causing
this problem.
Click to expand...

The problem is coming from the way a SQL join resolves unrelated tables.
The above SQL should fix it, if my new understanding of the table
relationships is correct.

Note, though, that the use of an inner join in the query presumes that
there will be a matching record in [Subcontractor Insurance Status] for
every subcontractor and project in [Subcontractor Status Log]. If
that's not the case, you need to use a left join.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top