Leaving a record out on a condition

C

ChrisP

I have a report that basically a service bill. It
displays a client's case and all the issues related to
that case. Each issue can contain parts.

The report is arranged like this; I have an Issue Header
which will repeat all the Issue info for that particular
case of this client. I have a Detail header which repeats
all the Parts that are used in that issue.

I want to set a condition in my report that will NOT
display the part if the serial# = 0. The serial# is text
data type.

I did this because the report is running off my query
which will not display the right records unless there are
parts for that issue. So i set a DUMBY part with serial#
0. Now everything is dandy, but my report is showing the
DUMBY part.

I have no idea how to set a condition in a report to now
display the Detail section, which contains part info, if
the part's serial# = 0.

Thanks for your help ahead of time,
Chris
 
F

Fons Ponsioen

Chris, I attempted to answer this question below. I gues
it did not do what you need.
do you want the textbox on your report ro be blank when
the serial# = 0? or do you want to hide the record and
omit printing the entire record if the Serial# =0.
Maybe I am misunderstanding your question.
Fons
 
F

Fons Ponsioen

Chris, post back with the tabel structure and a sample as
to what you want, or if you prefer send me a copy of your
mdb file with some sample dat and I'll look at it. Just
delete the nospam. from the email above.
Fons
 
D

Duane Hookom

You shouldn't be placing dummy records in tables to get around issues that
can be resolved with other methods such as reports with subreports or LEFT
or RIGHT JOINS.
 
J

John Spencer (MVP)

Access does support LEFT and RIGHT JOINS. So there must be something else going
on. As requested in the QUERIES group, please post your SQL statement. Also,
try posting in only one group if possible.
 
C

ChrisP

Here is the SQL which was constructed by Access using the
Query Design. This query shows all the records I need,
but if there isn't a part for an issue it will not
display it. That is why i divised a plan to add a DUMBY
part if no parts exist.


From what I been reading I know the right/left joins will
work, but I have no idea how to construct it with all the
stuff I need. I dont know enough SQL to do it.

here is the SQL:

SELECT Case_t.[Case#], Case_t.ClientID, [firstname] & " "
& [lastname] AS ClientName, Case_t.Case_Date,
Case_t.DropOff_Date, Case_t.PickUp_Date,
Case_t.Total_Amount, Case_Issue_t.Issue_Total,
Case_Issue_t.[Issue#], Case_Issue_t.Issue_Details,
Issue_t.[Problem#], Problem_Category_t.Problem_Desc,
Case_Issue_t.Parts_Used, Case_Issue_Parts_t.Part_Name,
Case_Issue_Parts_t.Cost, Case_Issue_Parts_t.Serial_Number
FROM Problem_Category_t INNER JOIN (Issue_t INNER JOIN
(Client_t INNER JOIN ((Case_t INNER JOIN
Case_Issue_Parts_t ON Case_t.[Case#]=Case_Issue_Parts_t.
[Case#]) INNER JOIN Case_Issue_t ON Case_t.[Case#]
=Case_Issue_t.[Case#]) ON
Client_t.ClientID=Case_t.ClientID) ON (Issue_t.[Issue#]
=Case_Issue_Parts_t.[Issue#]) AND (Issue_t.[Issue#]
=Case_Issue_t.[Issue#])) ON Problem_Category_t.[Problem#]
=Issue_t.[Problem#];

Thanks ahead of time,
 
C

ChrisP

I was only posting in another area because I want to get
either one fixed. My report to fix the flaw in my Query
or my Query's flaw. I need either one solved adn I can go
on my merry way.
 
J

John Spencer (MVP)

PERHAPS the following.


SELECT Case_t.[Case#], Case_t.ClientID, [firstname] & " "
& [lastname] AS ClientName, Case_t.Case_Date,
Case_t.DropOff_Date, Case_t.PickUp_Date,
Case_t.Total_Amount, Case_Issue_t.Issue_Total,
Case_Issue_t.[Issue#], Case_Issue_t.Issue_Details,
Issue_t.[Problem#], Problem_Category_t.Problem_Desc,
Case_Issue_t.Parts_Used, Case_Issue_Parts_t.Part_Name,
Case_Issue_Parts_t.Cost, Case_Issue_Parts_t.Serial_Number
FROM Problem_Category_t LEFT JOIN
(Issue_t LEFT JOIN
(Client_t LEFT JOIN
((Case_t LEFT JOIN
Case_Issue_Parts_t ON Case_t.[Case#]=Case_Issue_Parts_t.
[Case#]) LEFT JOIN
Case_Issue_t ON Case_t.[Case#]
=Case_Issue_t.[Case#]) ON
Client_t.ClientID=Case_t.ClientID) ON (Issue_t.[Issue#]
=Case_Issue_Parts_t.[Issue#]) AND (Issue_t.[Issue#]
=Case_Issue_t.[Issue#])) ON Problem_Category_t.[Problem#]
=Issue_t.[Problem#];

In the query grid, double-click on the join line(s) and select the option 2 or 3
(Show All of A and Only B).
Here is the SQL which was constructed by Access using the
Query Design. This query shows all the records I need,
but if there isn't a part for an issue it will not
display it. That is why i divised a plan to add a DUMBY
part if no parts exist.

From what I been reading I know the right/left joins will
work, but I have no idea how to construct it with all the
stuff I need. I dont know enough SQL to do it.

here is the SQL:

SELECT Case_t.[Case#], Case_t.ClientID, [firstname] & " "
& [lastname] AS ClientName, Case_t.Case_Date,
Case_t.DropOff_Date, Case_t.PickUp_Date,
Case_t.Total_Amount, Case_Issue_t.Issue_Total,
Case_Issue_t.[Issue#], Case_Issue_t.Issue_Details,
Issue_t.[Problem#], Problem_Category_t.Problem_Desc,
Case_Issue_t.Parts_Used, Case_Issue_Parts_t.Part_Name,
Case_Issue_Parts_t.Cost, Case_Issue_Parts_t.Serial_Number
FROM Problem_Category_t INNER JOIN (Issue_t INNER JOIN
(Client_t INNER JOIN ((Case_t INNER JOIN
Case_Issue_Parts_t ON Case_t.[Case#]=Case_Issue_Parts_t.
[Case#]) INNER JOIN Case_Issue_t ON Case_t.[Case#]
=Case_Issue_t.[Case#]) ON
Client_t.ClientID=Case_t.ClientID) ON (Issue_t.[Issue#]
=Case_Issue_Parts_t.[Issue#]) AND (Issue_t.[Issue#]
=Case_Issue_t.[Issue#])) ON Problem_Category_t.[Problem#]
=Issue_t.[Problem#];

Thanks ahead of time,
-----Original Message-----
Access does support LEFT and RIGHT JOINS. So there must be something else going
on. As requested in the QUERIES group, please post your SQL statement. Also,
try posting in only one group if possible.

.
Click to expand...
Click to expand...
 
C

ChrisP

It pops up a messagebox that says "JOIN expression not
supported"

I have Access XP from Office XP professional

Thats why i said the LEFT/RIGHT joins aren't supported
What do you think now?

-----Original Message-----
PERHAPS the following.


SELECT Case_t.[Case#], Case_t.ClientID, [firstname] & " "
& [lastname] AS ClientName, Case_t.Case_Date,
Case_t.DropOff_Date, Case_t.PickUp_Date,
Case_t.Total_Amount, Case_Issue_t.Issue_Total,
Case_Issue_t.[Issue#], Case_Issue_t.Issue_Details,
Issue_t.[Problem#], Problem_Category_t.Problem_Desc,
Case_Issue_t.Parts_Used, Case_Issue_Parts_t.Part_Name,
Case_Issue_Parts_t.Cost, Case_Issue_Parts_t.Serial_Number
FROM Problem_Category_t LEFT JOIN
(Issue_t LEFT JOIN
(Client_t LEFT JOIN
((Case_t LEFT JOIN
Case_Issue_Parts_t ON Case_t.[Case#]=Case_Issue_Parts_t.
[Case#]) LEFT JOIN
Case_Issue_t ON Case_t.[Case#]
=Case_Issue_t.[Case#]) ON
Client_t.ClientID=Case_t.ClientID) ON (Issue_t.[Issue#]
=Case_Issue_Parts_t.[Issue#]) AND (Issue_t.[Issue#]
=Case_Issue_t.[Issue#])) ON Problem_Category_t.[Problem#]
=Issue_t.[Problem#];

In the query grid, double-click on the join line(s) and select the option 2 or 3
(Show All of A and Only B).
Here is the SQL which was constructed by Access using the
Query Design. This query shows all the records I need,
but if there isn't a part for an issue it will not
display it. That is why i divised a plan to add a DUMBY
part if no parts exist.

From what I been reading I know the right/left joins will
work, but I have no idea how to construct it with all the
stuff I need. I dont know enough SQL to do it.

here is the SQL:

SELECT Case_t.[Case#], Case_t.ClientID, [firstname] & " "
& [lastname] AS ClientName, Case_t.Case_Date,
Case_t.DropOff_Date, Case_t.PickUp_Date,
Case_t.Total_Amount, Case_Issue_t.Issue_Total,
Case_Issue_t.[Issue#], Case_Issue_t.Issue_Details,
Issue_t.[Problem#], Problem_Category_t.Problem_Desc,
Case_Issue_t.Parts_Used, Case_Issue_Parts_t.Part_Name,
Case_Issue_Parts_t.Cost, Case_Issue_Parts_t.Serial_Number
FROM Problem_Category_t INNER JOIN (Issue_t INNER JOIN
(Client_t INNER JOIN ((Case_t INNER JOIN
Case_Issue_Parts_t ON Case_t.[Case#] =Case_Issue_Parts_t.
[Case#]) INNER JOIN Case_Issue_t ON Case_t.[Case#]
=Case_Issue_t.[Case#]) ON
Client_t.ClientID=Case_t.ClientID) ON (Issue_t.[Issue#]
=Case_Issue_Parts_t.[Issue#]) AND (Issue_t.[Issue#]
=Case_Issue_t.[Issue#])) ON Problem_Category_t. [Problem#]
=Issue_t.[Problem#];

Thanks ahead of time,
-----Original Message-----
Access does support LEFT and RIGHT JOINS. So there
Click to expand...
must
be something else going
on. As requested in the QUERIES group, please post
Click to expand...
your
SQL statement. Also,
try posting in only one group if possible.

ChrisP wrote:

Access doesnt support Left/Right Joins.
Can you help me solve this problem?

-----Original Message-----
You shouldn't be placing dummy records in tables to get
around issues that
can be resolved with other methods such as reports with
subreports or LEFT
or RIGHT JOINS.
Click to expand...
wrote
in
message
I have a report that basically a service bill. It
displays a client's case and all the issues
Click to expand...
related
to
that case. Each issue can contain parts.

The report is arranged like this; I have an Issue
Header
which will repeat all the Issue info for that
particular
case of this client. I have a Detail header which
repeats
all the Parts that are used in that issue.

I want to set a condition in my report that will NOT
display the part if the serial# = 0. The serial# is
text
data type.

I did this because the report is running off my query
which will not display the right records unless there
are
parts for that issue. So i set a DUMBY part with
serial#
0. Now everything is dandy, but my report is showing
the
DUMBY part.

I have no idea how to set a condition in a report to
now
display the Detail section, which contains part info,
if
the part's serial# = 0.

Thanks for your help ahead of time,
Chris



.

.
Click to expand...
Click to expand...
.
Click to expand...
 
J

John Spencer (MVP)

That somewhere in that query I caused a typo.

Can you try building the query one table at a time until you hit the error?
It pops up a messagebox that says "JOIN expression not
supported"

I have Access XP from Office XP professional

Thats why i said the LEFT/RIGHT joins aren't supported
What do you think now?
-----Original Message-----
PERHAPS the following.


SELECT Case_t.[Case#], Case_t.ClientID, [firstname] & " "
& [lastname] AS ClientName, Case_t.Case_Date,
Case_t.DropOff_Date, Case_t.PickUp_Date,
Case_t.Total_Amount, Case_Issue_t.Issue_Total,
Case_Issue_t.[Issue#], Case_Issue_t.Issue_Details,
Issue_t.[Problem#], Problem_Category_t.Problem_Desc,
Case_Issue_t.Parts_Used, Case_Issue_Parts_t.Part_Name,
Case_Issue_Parts_t.Cost, Case_Issue_Parts_t.Serial_Number
FROM Problem_Category_t LEFT JOIN
(Issue_t LEFT JOIN
(Client_t LEFT JOIN
((Case_t LEFT JOIN
Case_Issue_Parts_t ON Case_t.[Case#]=Case_Issue_Parts_t.
[Case#]) LEFT JOIN
Case_Issue_t ON Case_t.[Case#]
=Case_Issue_t.[Case#]) ON
Client_t.ClientID=Case_t.ClientID) ON (Issue_t.[Issue#]
=Case_Issue_Parts_t.[Issue#]) AND (Issue_t.[Issue#]
=Case_Issue_t.[Issue#])) ON Problem_Category_t.[Problem#]
=Issue_t.[Problem#];

In the query grid, double-click on the join line(s) and select the option 2 or 3
(Show All of A and Only B).
Here is the SQL which was constructed by Access using the
Query Design. This query shows all the records I need,
but if there isn't a part for an issue it will not
display it. That is why i divised a plan to add a DUMBY
part if no parts exist.

From what I been reading I know the right/left joins will
work, but I have no idea how to construct it with all the
stuff I need. I dont know enough SQL to do it.

here is the SQL:

SELECT Case_t.[Case#], Case_t.ClientID, [firstname] & " "
& [lastname] AS ClientName, Case_t.Case_Date,
Case_t.DropOff_Date, Case_t.PickUp_Date,
Case_t.Total_Amount, Case_Issue_t.Issue_Total,
Case_Issue_t.[Issue#], Case_Issue_t.Issue_Details,
Issue_t.[Problem#], Problem_Category_t.Problem_Desc,
Case_Issue_t.Parts_Used, Case_Issue_Parts_t.Part_Name,
Case_Issue_Parts_t.Cost, Case_Issue_Parts_t.Serial_Number
FROM Problem_Category_t INNER JOIN (Issue_t INNER JOIN
(Client_t INNER JOIN ((Case_t INNER JOIN
Case_Issue_Parts_t ON Case_t.[Case#] =Case_Issue_Parts_t.
[Case#]) INNER JOIN Case_Issue_t ON Case_t.[Case#]
=Case_Issue_t.[Case#]) ON
Client_t.ClientID=Case_t.ClientID) ON (Issue_t.[Issue#]
=Case_Issue_Parts_t.[Issue#]) AND (Issue_t.[Issue#]
=Case_Issue_t.[Issue#])) ON Problem_Category_t. [Problem#]
=Issue_t.[Problem#];

Thanks ahead of time,

-----Original Message-----
Access does support LEFT and RIGHT JOINS. So there must
be something else going
on. As requested in the QUERIES group, please post your
SQL statement. Also,
try posting in only one group if possible.

ChrisP wrote:

Access doesnt support Left/Right Joins.
Can you help me solve this problem?

-----Original Message-----
You shouldn't be placing dummy records in tables to
get
around issues that
can be resolved with other methods such as reports
with
subreports or LEFT
or RIGHT JOINS.

--
Duane Hookom
MS Access MVP
--

in
message
I have a report that basically a service bill. It
displays a client's case and all the issues related
to
that case. Each issue can contain parts.

The report is arranged like this; I have an Issue
Header
which will repeat all the Issue info for that
particular
case of this client. I have a Detail header which
repeats
all the Parts that are used in that issue.

I want to set a condition in my report that will NOT
display the part if the serial# = 0. The serial# is
text
data type.

I did this because the report is running off my
query
which will not display the right records unless
there
are
parts for that issue. So i set a DUMBY part with
serial#
0. Now everything is dandy, but my report is showing
the
DUMBY part.

I have no idea how to set a condition in a report to
now
display the Detail section, which contains part
info,
if
the part's serial# = 0.

Thanks for your help ahead of time,
Chris



.

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