table in query has no data

  • Thread starter tobesurveyor via AccessMonster.com
  • Start date
T

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
 
J

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
 

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

Top