Please help

G

Guest

I have a query that has a service field with A, S, and W. I have excluded W
by <>"W" on the design view of the query. When I run the query, it displays
all records that do not have a W in service. I then have a report that uses
this information, when I open the report it creates a page for both A and S,
I want all records to be on the same report and not seperated by A or S.
However, if the report groups them together than I get duplicates. Is there
a way to have all records be on one report and not seperated by the service
field without creating a duplicate??????

I've tried the hiding duplicate function on the report but since all records
are not exact duplicates, this does not work.The only difference between
records in the query, is the field service.

Any information that can be provided, will be greatly appreciated. I've
tried everything that I could think of....

Thanks!
 
K

Ken Snell [MVP]

While in design view of the report, go to View | Sorting and Grouping.

Likely you'll find the service field listed there as the way the report is
to group the data. Delete the field from that list and close the window.
 
G

Guest

I've checked the Sorting and Grouping window. Service isnot listed. I,
also, checked the subreport to make sure it wasn't sorting or grouping on the
service field.

Any other ideas????

Thanks for the suggestion!!
 
K

Ken Snell [MVP]

Hmmm....

Well, let's start with some basic info. Please post
- the SQL statement of the query that you're using;
- the name of the query that you're using;
- the recordsource of the report and all subreports.
 
G

Guest

Name of Query Using: Bart S1

SELECT DISTINCTROW *
FROM [System Information]
WHERE ((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=0)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=1)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=7));

The query, Bart S1, is used as a filter for the report/subreport. A button
on a form is used to open the report and apply the filter.

Record source for the report and subreport is as follows:

Service Report is based on the Report Query:

SELECT DISTINCT [Customer Information].Customer_ID, [Customer
Information].Customer, [Service Address].Service_Address, [Service
Address].Manager, ServiceRequirements2.Type_of_System, [System
Information].Employee, ServiceRequirements2.Raw_Water,
ServiceRequirements2.Treated_Water, ServiceRequirements2.Cycles,
ServiceRequirements2.Inhibitor_Level, ServiceRequirements2.Range_1,
ServiceRequirements2.Range_2, ServiceRequirements2.Range_3,
ServiceRequirements2.Range_4, ServiceRequirements2.Range_5, [System
Information].Service, [System Information].Week
FROM ServiceRequirements2 INNER JOIN ([Customer Information] INNER JOIN
([Service Address] INNER JOIN [System Information] ON [Service
Address].Service_Address = [System Information].Service_Address) ON [Customer
Information].Customer_ID = [System Information].Customer_ID) ON
ServiceRequirements2.Type_of_System = [System Information].Type_of_System
GROUP BY [Customer Information].Customer_ID, [Customer
Information].Customer, [Service Address].Service_Address, [Service
Address].Manager, ServiceRequirements2.Type_of_System, [System
Information].Employee, ServiceRequirements2.Raw_Water,
ServiceRequirements2.Treated_Water, ServiceRequirements2.Cycles,
ServiceRequirements2.Inhibitor_Level, ServiceRequirements2.Range_1,
ServiceRequirements2.Range_2, ServiceRequirements2.Range_3,
ServiceRequirements2.Range_4, ServiceRequirements2.Range_5, [System
Information].Service, [System Information].Week
ORDER BY [Customer Information].Customer, [Service Address].Service_Address,
ServiceRequirements2.Type_of_System;


Service Subreport is based on the Report Query 2:

SELECT DISTINCT [Customer Information].Customer_ID, [System
Information].System, [System Information].Customer_Identification, [System
Information].Service_Address, [System Information].Type_of_System, [System
Information].Week
FROM [Customer Information] INNER JOIN ([System Information] INNER JOIN
[Service Address] ON [System Information].Service_Address = [Service
Address].Service_Address) ON [Customer Information].Customer_ID = [System
Information].Customer_ID
GROUP BY [Customer Information].Customer_ID, [System Information].System,
[System Information].Customer_Identification, [System
Information].Service_Address, [System Information].Type_of_System, [System
Information].Week
ORDER BY [Customer Information].Customer_ID, [System Information].System,
[System Information].Customer_Identification;

Thank you so much for your time and suggestions!!!
 
G

Guest

Name of Query Using: Bart S1

SELECT DISTINCTROW *
FROM [System Information]
WHERE ((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=0)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=1)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=7));

The Query, Bart S1, is used as a filter for the reports. The report is
opened by a button on a form. This is when the filter is applied to the
report.

Record source for Report and subreport is the following:

Service Report is based on the Report Query:

SELECT DISTINCT [Customer Information].Customer_ID, [Customer
Information].Customer, [Service Address].Service_Address, [Service
Address].Manager, ServiceRequirements2.Type_of_System, [System
Information].Employee, ServiceRequirements2.Raw_Water,
ServiceRequirements2.Treated_Water, ServiceRequirements2.Cycles,
ServiceRequirements2.Inhibitor_Level, ServiceRequirements2.Range_1,
ServiceRequirements2.Range_2, ServiceRequirements2.Range_3,
ServiceRequirements2.Range_4, ServiceRequirements2.Range_5, [System
Information].Service, [System Information].Week
FROM ServiceRequirements2 INNER JOIN ([Customer Information] INNER JOIN
([Service Address] INNER JOIN [System Information] ON [Service
Address].Service_Address = [System Information].Service_Address) ON [Customer
Information].Customer_ID = [System Information].Customer_ID) ON
ServiceRequirements2.Type_of_System = [System Information].Type_of_System
GROUP BY [Customer Information].Customer_ID, [Customer
Information].Customer, [Service Address].Service_Address, [Service
Address].Manager, ServiceRequirements2.Type_of_System, [System
Information].Employee, ServiceRequirements2.Raw_Water,
ServiceRequirements2.Treated_Water, ServiceRequirements2.Cycles,
ServiceRequirements2.Inhibitor_Level, ServiceRequirements2.Range_1,
ServiceRequirements2.Range_2, ServiceRequirements2.Range_3,
ServiceRequirements2.Range_4, ServiceRequirements2.Range_5, [System
Information].Service, [System Information].Week
ORDER BY [Customer Information].Customer, [Service Address].Service_Address,
ServiceRequirements2.Type_of_System;


Service Subreport is based on the Report Query 2:

SELECT DISTINCT [Customer Information].Customer_ID, [System
Information].System, [System Information].Customer_Identification, [System
Information].Service_Address, [System Information].Type_of_System, [System
Information].Week
FROM [Customer Information] INNER JOIN ([System Information] INNER JOIN
[Service Address] ON [System Information].Service_Address = [Service
Address].Service_Address) ON [Customer Information].Customer_ID = [System
Information].Customer_ID
GROUP BY [Customer Information].Customer_ID, [System Information].System,
[System Information].Customer_Identification, [System
Information].Service_Address, [System Information].Type_of_System, [System
Information].Week
ORDER BY [Customer Information].Customer_ID, [System Information].System,
[System Information].Customer_Identification;

Thanks so much for your time and suggestions!!!
 
K

Ken Snell [MVP]

The first query you posted is being used as the filtering query for the
report's recordsource query, right? But it doesn't include all the fields
from the report's recordsource query, so I'm not sure how it should be able
to work correctly.

I admit to you that I do not use filtering queries for my reports,
preferring instead to use the WHERE argument in the DoCmd.OpenReport method
(or use the report's Filter property to have a "WHERE" filtering string in
it). I find this much easier and more understandable.

Let's try something with your report setup and see what happens.

Set the Filter property of both the main report and the subreport to this
WHERE expression (do this in design view of the report and subreport) (all
one long line, even though the newsreader likely is wrapping it):

((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=0)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=1)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=7))


Or, if you're opening the report via DoCmd.OpenReport method (and using the
Bart S1 query as the third argument (Filter) for that action), change that
to this (all one long line, even though the newsreader likely is wrapping
it):

DoCmd.OpenReport "ReportName", , , "((([System
Information].Employee)='Bart') AND (([System Information].Service)<>'W') AND
(([System Information].Week)=0)) OR ((([System
Information].Employee)='Bart') AND (([System Information].Service)<>'W') AND
(([System Information].Week)=1)) OR ((([System
Information].Employee)='Bart') AND (([System Information].Service)<>'W') AND
(([System Information].Week)=7))"


Does the report now work as you expect?

--

Ken Snell
<MS ACCESS MVP>


Tia said:
Name of Query Using: Bart S1

SELECT DISTINCTROW *
FROM [System Information]
WHERE ((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=0)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=1)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=7));

The query, Bart S1, is used as a filter for the report/subreport. A button
on a form is used to open the report and apply the filter.

Record source for the report and subreport is as follows:

Service Report is based on the Report Query:

SELECT DISTINCT [Customer Information].Customer_ID, [Customer
Information].Customer, [Service Address].Service_Address, [Service
Address].Manager, ServiceRequirements2.Type_of_System, [System
Information].Employee, ServiceRequirements2.Raw_Water,
ServiceRequirements2.Treated_Water, ServiceRequirements2.Cycles,
ServiceRequirements2.Inhibitor_Level, ServiceRequirements2.Range_1,
ServiceRequirements2.Range_2, ServiceRequirements2.Range_3,
ServiceRequirements2.Range_4, ServiceRequirements2.Range_5, [System
Information].Service, [System Information].Week
FROM ServiceRequirements2 INNER JOIN ([Customer Information] INNER JOIN
([Service Address] INNER JOIN [System Information] ON [Service
Address].Service_Address = [System Information].Service_Address) ON [Customer
Information].Customer_ID = [System Information].Customer_ID) ON
ServiceRequirements2.Type_of_System = [System Information].Type_of_System
GROUP BY [Customer Information].Customer_ID, [Customer
Information].Customer, [Service Address].Service_Address, [Service
Address].Manager, ServiceRequirements2.Type_of_System, [System
Information].Employee, ServiceRequirements2.Raw_Water,
ServiceRequirements2.Treated_Water, ServiceRequirements2.Cycles,
ServiceRequirements2.Inhibitor_Level, ServiceRequirements2.Range_1,
ServiceRequirements2.Range_2, ServiceRequirements2.Range_3,
ServiceRequirements2.Range_4, ServiceRequirements2.Range_5, [System
Information].Service, [System Information].Week
ORDER BY [Customer Information].Customer, [Service Address].Service_Address,
ServiceRequirements2.Type_of_System;


Service Subreport is based on the Report Query 2:

SELECT DISTINCT [Customer Information].Customer_ID, [System
Information].System, [System Information].Customer_Identification, [System
Information].Service_Address, [System Information].Type_of_System, [System
Information].Week
FROM [Customer Information] INNER JOIN ([System Information] INNER JOIN
[Service Address] ON [System Information].Service_Address = [Service
Address].Service_Address) ON [Customer Information].Customer_ID = [System
Information].Customer_ID
GROUP BY [Customer Information].Customer_ID, [System Information].System,
[System Information].Customer_Identification, [System
Information].Service_Address, [System Information].Type_of_System, [System
Information].Week
ORDER BY [Customer Information].Customer_ID, [System Information].System,
[System Information].Customer_Identification;

Thank you so much for your time and suggestions!!!

Ken Snell said:
Hmmm....

Well, let's start with some basic info. Please post
- the SQL statement of the query that you're using;
- the name of the query that you're using;
- the recordsource of the report and all subreports.


--

Ken Snell
<MS ACCESS MVP>

on
the report
is both A
and or
S.
Click to expand...
Click to expand...
 
G

Guest

Thanks again for your help....I tried putting the filter as a "where"
filtering string on the DoCmd.OpenReport method. Instead of filtering my
reports when I clicked on the button, it opened all reports. Here's the code
I used:

Bart_Week_S1_Click
'
'------------------------------------------------------------
Private Sub Bart_Week_S1_Click()
On Error GoTo Bart_Week_S1_Click_Err

DoCmd.OpenReport "ServiceReport", acViewPreview, "((([System
Information].Employee)='Bart') AND (([System Information].Service)<>'W') AND
(([System Information].Week)=0)) OR ((([System Information].Employee)='Bart')
AND (([System Information].Service)<>'W') AND (([System
Information].Week)=1)) OR ((([System Information].Employee)='Bart') AND
(([System Information].Service)<>'W') AND (([System Information].Week)=7))",
"", acNormal
DoCmd.OutputTo acReport, "ServiceReport", "RichTextFormat(*.rtf)",
"Service Report.rtf", False, "", 0


Bart_Week_S1_Click_Exit:
Exit Sub

Bart_Week_S1_Click_Err:
MsgBox Error$
Resume Bart_Week_S1_Click_Exit

End Sub '------------------------------------------------------------

Am I missing something or doing something wrong????

I, also, tried chagning the filter property of the report and main report
and it didn't seem to change anything.

Thanks again for your help!!

Ken Snell said:
The first query you posted is being used as the filtering query for the
report's recordsource query, right? But it doesn't include all the fields
from the report's recordsource query, so I'm not sure how it should be able
to work correctly.

I admit to you that I do not use filtering queries for my reports,
preferring instead to use the WHERE argument in the DoCmd.OpenReport method
(or use the report's Filter property to have a "WHERE" filtering string in
it). I find this much easier and more understandable.

Let's try something with your report setup and see what happens.

Set the Filter property of both the main report and the subreport to this
WHERE expression (do this in design view of the report and subreport) (all
one long line, even though the newsreader likely is wrapping it):

((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=0)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=1)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=7))


Or, if you're opening the report via DoCmd.OpenReport method (and using the
Bart S1 query as the third argument (Filter) for that action), change that
to this (all one long line, even though the newsreader likely is wrapping
it):

DoCmd.OpenReport "ReportName", , , "((([System
Information].Employee)='Bart') AND (([System Information].Service)<>'W') AND
(([System Information].Week)=0)) OR ((([System
Information].Employee)='Bart') AND (([System Information].Service)<>'W') AND
(([System Information].Week)=1)) OR ((([System
Information].Employee)='Bart') AND (([System Information].Service)<>'W') AND
(([System Information].Week)=7))"


Does the report now work as you expect?

--

Ken Snell
<MS ACCESS MVP>


Tia said:
Name of Query Using: Bart S1

SELECT DISTINCTROW *
FROM [System Information]
WHERE ((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=0)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=1)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)<>"W") AND (([System Information].Week)=7));

The query, Bart S1, is used as a filter for the report/subreport. A button
on a form is used to open the report and apply the filter.

Record source for the report and subreport is as follows:

Service Report is based on the Report Query:

SELECT DISTINCT [Customer Information].Customer_ID, [Customer
Information].Customer, [Service Address].Service_Address, [Service
Address].Manager, ServiceRequirements2.Type_of_System, [System
Information].Employee, ServiceRequirements2.Raw_Water,
ServiceRequirements2.Treated_Water, ServiceRequirements2.Cycles,
ServiceRequirements2.Inhibitor_Level, ServiceRequirements2.Range_1,
ServiceRequirements2.Range_2, ServiceRequirements2.Range_3,
ServiceRequirements2.Range_4, ServiceRequirements2.Range_5, [System
Information].Service, [System Information].Week
FROM ServiceRequirements2 INNER JOIN ([Customer Information] INNER JOIN
([Service Address] INNER JOIN [System Information] ON [Service
Address].Service_Address = [System Information].Service_Address) ON [Customer
Information].Customer_ID = [System Information].Customer_ID) ON
ServiceRequirements2.Type_of_System = [System Information].Type_of_System
GROUP BY [Customer Information].Customer_ID, [Customer
Information].Customer, [Service Address].Service_Address, [Service
Address].Manager, ServiceRequirements2.Type_of_System, [System
Information].Employee, ServiceRequirements2.Raw_Water,
ServiceRequirements2.Treated_Water, ServiceRequirements2.Cycles,
ServiceRequirements2.Inhibitor_Level, ServiceRequirements2.Range_1,
ServiceRequirements2.Range_2, ServiceRequirements2.Range_3,
ServiceRequirements2.Range_4, ServiceRequirements2.Range_5, [System
Information].Service, [System Information].Week
ORDER BY [Customer Information].Customer, [Service Address].Service_Address,
ServiceRequirements2.Type_of_System;


Service Subreport is based on the Report Query 2:

SELECT DISTINCT [Customer Information].Customer_ID, [System
Information].System, [System Information].Customer_Identification, [System
Information].Service_Address, [System Information].Type_of_System, [System
Information].Week
FROM [Customer Information] INNER JOIN ([System Information] INNER JOIN
[Service Address] ON [System Information].Service_Address = [Service
Address].Service_Address) ON [Customer Information].Customer_ID = [System
Information].Customer_ID
GROUP BY [Customer Information].Customer_ID, [System Information].System,
[System Information].Customer_Identification, [System
Information].Service_Address, [System Information].Type_of_System, [System
Information].Week
ORDER BY [Customer Information].Customer_ID, [System Information].System,
[System Information].Customer_Identification;

Thank you so much for your time and suggestions!!!

Ken Snell said:
Hmmm....

Well, let's start with some basic info. Please post
- the SQL statement of the query that you're using;
- the name of the query that you're using;
- the recordsource of the report and all subreports.


--

Ken Snell
<MS ACCESS MVP>

I've checked the Sorting and Grouping window. Service isnot listed. I,
also, checked the subreport to make sure it wasn't sorting or grouping on
the
service field.

Any other ideas????

Thanks for the suggestion!!

:

While in design view of the report, go to View | Sorting and Grouping.

Likely you'll find the service field listed there as the way the report
is
to group the data. Delete the field from that list and close the window.

--

Ken Snell
<MS ACCESS MVP>

I have a query that has a service field with A, S, and W. I have
excluded
W
by <>"W" on the design view of the query. When I run the query, it
displays
all records that do not have a W in service. I then have a report
that
uses
this information, when I open the report it creates a page for both A
and
S,
I want all records to be on the same report and not seperated by A or
S.
However, if the report groups them together than I get duplicates. Is
there
a way to have all records be on one report and not seperated by the
service
field without creating a duplicate??????

I've tried the hiding duplicate function on the report but since all
records
are not exact duplicates, this does not work.The only difference
between
records in the query, is the field service.

Any information that can be provided, will be greatly appreciated.
I've
tried everything that I could think of....

Thanks!
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Repost: Please Help 6
challenging query 3
Split data in field on character return 0
Duplicate Records Retrieval 1
Deleting Anything Duplicate 3
remove duplicate records in Access 2007 7
How to Exclude Duplicates 7
Queries and Reports 1

Top