table in query has no data

[tobesurveyor via AccessMonster.com]

Good afternoon-

I am trying to create a report that is based upon several tables. In the
main table, tblMain, I have the following fields:
MainID
ProjectName
ClientID ---------------------------------> Linked to tblClient
ProposalID --------------------------> Linked to tblProposal

In a form, a user enters in the new record for tblMain, which requires
ProjectName and ClientID to be filled. ProposalID is an optional field.

When I create the query, because some records will have a 0 for the cell
value of ProposalID, there is no value in the tblProposal and will skip that
record and only show records where there is a value coorisponding in
tblProposal.

Is there a work around to this in the query design? Any thoughts would be
greatly appreciated.

Thanks,
surveyorinva

 

[Jeff Boyce]

If you are saying that you could have a Client record that has no
corresponding Proposal record, you can do this in your query.

Open a query in design view. Add both tables. Drag ProposalID from
tblClient to the corresponding field (?ProposalID) in tblProposal (out of
curiosity, what is the relationship between these? That is, can one Client
have one-only-one Proposal? ... and why not put ClientID as a foreign key
in your Proposal table?)

Now right-click on the line joining the two tables. Select properties.
Change the type of join to get ALL Clients and ANY matching Proposals.

This way, you still see the Client data even if there are no matching
Proposals.

Regards

Jeff Boyce
Microsoft Office/Access MVP