Query Problem - Leaving out Records

  • Thread starter Thread starter ChrisP
  • Start date Start date
C

ChrisP

I have a Query that is like a Service Bill and I use it
for a Report that is a service bill that I want to print
out.

My query is working fine, but a minor flaw.
I have a Case which contains Issues. These Issues can
contain parts or not. My query displays all the Issues
for a particular case, but the way I set it up when
adding the Issue_Parts table it will only display issues
that contain parts.

(1)I want to be able to display All the Issues with or
without their parts for a specific Case.

This is basically between 3 tables; Case_t, Case_Issue_t,
and Case_Issue_Parts_t.

I have gone so far as to create a DUMBY part record in
the Case_Issue_Parts_t if an Issue doesn't have a part.
One idea I have is to display what my query is displayin
now as long as the Serial# not = 0, which signifies the
DUMBY part.
(2)How can I do this?

Also, if the DUMBY part idea isn't a good solution. How
can I set it up to display what I asked in (1)? Lastly,
any ideas even if the DUMBY part is a good idea?

Thanks ahead of time
 
Access does support left and right joins. Have you tried double-clicking the
join between Issue and Parts and selecting the option to include all records
from Issue?

Another method is to remove the parts table from the report. Create a
subreport of the parts table and insert it into the detail section of the
main report. Set the Link Master/Child properties to the case field.
 
I like your first idea about double clikcing the join. I
forgot all about that.

I changed it to the 2nd and 3rd option, but I get an
error it says, "SQL couldn't be executed because there is
an ambigious OUTER JOIN"

Here is my setup of tables, to give you more info;

Case_t, Issue_t, Case_Issue_t, Case_Issue_Parts_t

Case_t: Here is the info on whent he Case was made and
what ClientID, etch

Issue_t: The issue info, like; ProblemID, Date, Time

Case_Issue_t: The details, solution, cost, etc..

Case_issue_Parts_t: All the parts that a Case-Issue has.
Part,cost,vendor, etc..

I appreciate your help and time and I hope you can help
me further.
Chris
 
You didn't suggest which joins you updated and which options you selected. I
always create joins by dragging from the unique field to the foreign key
field. Then modify your joins.
 
Back
Top