Grouping affecting results

A

aaearhart

Hello!

I have a report with these fields:
[Traveler] text
[Travel_To] date/time
[Travel_From] date/time
[Destination] text

The purpose of this report is to show _current and upcoming_ staff travel
([Travel_From] >Date()). I have it grouped on [Travel_From] by month and this
is the only form of sorting or grouping in the report.

Now, my data looks like this (essentially):
Jim 06JUN05 28JUN05 New York, NY
Jim 12JUN05 17JUN05 Venice, Italy
Mike 11JUN05 26JUN05 Venice, Italy

Jim 01JUL05 18JUL05 Venice, Italy
Kurt 02JUL05 10JUL05 Venice, Italy

Jim 06AUG05 28AUG05 New York, NY
Mike 08AUG05 12AUG05 Venice, Italy
Mike 19AUG05 26AUG05 Munich, Germany

....but my report looks like this:

JUNE 2005============================
Jim 06JUN05 28JUN05 New York, NY
Mike 11JUN05 26JUN05 Venice, Italy
JULY 2005============================
Jim 01JUL05 18JUL05 Venice, Italy
Kurt 02JUL05 10JUL05 Venice, Italy
AUGUST 2005==========================
Jim 06AUG05 28AUG05 New York, NY
Mike 08AUG05 12AUG05 Venice, Italy

....I'm missing trip info for Jim in June and Mike in August.
What's happening is this: if a traveler has more than one trip in a month,
only the first record is returned. Any ideas on how to solve this?

Thanks muchly!
/amelia
 
D

Duane Hookom

It looks like you have another grouping level by Traveler and have your
controls in the Travel Group Header section.
 
A

aaearhart

The superfluous grouping by Traveler was my first thought as well so I've
already eliminated that possibility by checking with the Grouping and Sorting
dialog box.

As far as controls go:

Report Header--------------------------------
Report Title
Page Header (Field labels)------------------
Traveler Travel Dates Destination
Travel_To Header----------------------------
[Travel_To] (groupin in month format)
Detail------------------------------------------
[Traveler] [Travel_To] [Travel_From] [Destination]
Travel_To Footer-----------------------------
(Formula for counting how many days traveled that month)
Page Footer-----------------------------------
(Date and Page # info)
Report Footer---------------------------------
(empty)
 
A

aaearhart

As far as controls go:

Report Header--------------------------------
Report Title
Page Header (Field labels)------------------
Traveler Travel Dates Destination
Travel_From Header------------------------- [EDIT: not "Travel_To"]
[Travel_From] (groupin in month format)

Detail------------------------------------------
[Traveler] [Travel_To] [Travel_From] [Destination]

Travel_To Footer-----------------------------
(Formula for counting how many days traveled that month)

Page Footer-----------------------------------
(Date and Page # info)

Report Footer---------------------------------
(empty)
 
D

Duane Hookom

I would go back to the datasheet view of the report's record source. Access
reports don't hide records unless you have something in the report design
that would hide a section or controls.
 
B

Bob Howard

Could your query have a "grouping" set on some field (like the date)??? As
if it were supposed to summarize something??


Duane Hookom said:
It looks like you have another grouping level by Traveler and have your
controls in the Travel Group Header section.

--
Duane Hookom
MS Access MVP
--

aaearhart said:
Hello!

I have a report with these fields:
[Traveler] text
[Travel_To] date/time
[Travel_From] date/time
[Destination] text

The purpose of this report is to show _current and upcoming_ staff travel
([Travel_From] >Date()). I have it grouped on [Travel_From] by month and
this
is the only form of sorting or grouping in the report.

Now, my data looks like this (essentially):
Jim 06JUN05 28JUN05 New York, NY
Jim 12JUN05 17JUN05 Venice, Italy
Mike 11JUN05 26JUN05 Venice, Italy

Jim 01JUL05 18JUL05 Venice, Italy
Kurt 02JUL05 10JUL05 Venice, Italy

Jim 06AUG05 28AUG05 New York, NY
Mike 08AUG05 12AUG05 Venice, Italy
Mike 19AUG05 26AUG05 Munich, Germany

...but my report looks like this:

JUNE 2005============================
Jim 06JUN05 28JUN05 New York, NY
Mike 11JUN05 26JUN05 Venice, Italy
JULY 2005============================
Jim 01JUL05 18JUL05 Venice, Italy
Kurt 02JUL05 10JUL05 Venice, Italy
AUGUST 2005==========================
Jim 06AUG05 28AUG05 New York, NY
Mike 08AUG05 12AUG05 Venice, Italy

...I'm missing trip info for Jim in June and Mike in August.
What's happening is this: if a traveler has more than one trip in a month,
only the first record is returned. Any ideas on how to solve this?

Thanks muchly!
/amelia
Click to expand...
Click to expand...
 
A

aaearhart

SELECT ...
WHERE ((([tbl_Travel-Schedule].Travel_From)>Date()));

Is this what you mean?
 
A

aaearhart

Ha ha! My own sentiments come to bite me back! I've often declared "Software
only does what its told."

Yeah, the issue's got to be in my grouping somehow, although Bob Howard may
be on to something in wondering about my query criteria:date)??? As
if it were supposed to summarize something??

here's my report's query criteria:
SELECT ... WHERE ((([tbl_Travel-Schedule].Travel_From)>Date()));

Otherwise, looking at my Sorting and Grouping, did that look correct?

also: (i hate when others do this, but here i go)
a screen grab--> http://www.n-e-d.com/testsite/images/report05.jpg
 
D

Duane Hookom

I'm not sure where you are at with this. Do you mind sharing the full SQL of
your report's record source as well as any where clause or filter that might
be used in your report?

--
Duane Hookom
MS Access MVP


aaearhart said:
Ha ha! My own sentiments come to bite me back! I've often declared
"Software
only does what its told."

Yeah, the issue's got to be in my grouping somehow, although Bob Howard
may
be on to something in wondering about my query criteria:date)??? As
if it were supposed to summarize something??

here's my report's query criteria:
SELECT ... WHERE ((([tbl_Travel-Schedule].Travel_From)>Date()));

Otherwise, looking at my Sorting and Grouping, did that look correct?

also: (i hate when others do this, but here i go)
a screen grab--> http://www.n-e-d.com/testsite/images/report05.jpg
Click to expand...
 
A

aaearhart

it's a mouthfull, but here it is...

SELECT tbl_Names.[Name First],
tbl_Names.[Name Last],
[tbl_Travel-Schedule].SchedID,
[tbl_Travel-Schedule].[Name ID] AS [tbl_Travel-Schedule_Name ID],
[tbl_Travel-Schedule].[Project Name],
[tbl_Travel-Schedule].Travel_To,
[tbl_Travel-Schedule].Start,
[tbl_Travel-Schedule].End,
[tbl_Travel-Schedule].Travel_From,
[tbl_Travel-Schedule].Task,
[tbl_Travel-Schedule].Notes,
[tbl_Travel-Schedule].[Commissioning?],
[tbl_Travel-Schedule].DepCity,
[tbl_Travel-Schedule].ArrCity,
[tbl_Travel-Schedule].RetCity,
[tbl_Travel-Schedule].ArrivalDate,
tbl_Names.[Name ID] AS [tbl_Names_Name ID],
tbl_Airports.City,
[tbl_Travel-Schedule]!Travel_From-[tbl_Travel-Schedule]!Travel_To+1 AS Days

FROM tbl_Names INNER JOIN ([tbl_Travel-Schedule]
INNER JOIN tbl_Airports ON
[tbl_Travel-Schedule].ArrCity=tbl_Airports.Airport) ON tbl_Names.[Name
ID]=[tbl_Travel-Schedule].[Name ID]
WHERE ((([tbl_Travel-Schedule].Travel_From)>Date()));

don't get me started on naming conventions; i'm working with an existing
database.
 
D

Duane Hookom

I don't see anything unusual about your record source. If your records
display in the datasheet view of the record source but not in the report, I
would start over with a new report.
Have you tried checking the number of records in the datasheet view and then
adding a text box in the Report Header section:
=Count(*)
The numbers should be the same.
 
A

aaearhart

somehow, the report fairy got in and resolved the problem. was having it for
days, now the report is fine.

thanks loads!

/amelia

Duane Hookom said:
I don't see anything unusual about your record source. If your records
display in the datasheet view of the record source but not in the report, I
would start over with a new report.
Have you tried checking the number of records in the datasheet view and then
adding a text box in the Report Header section:
=Count(*)
The numbers should be the same.
--
Duane Hookom
MS Access MVP


aaearhart said:
it's a mouthfull, but here it is...

SELECT tbl_Names.[Name First],
tbl_Names.[Name Last],
[tbl_Travel-Schedule].SchedID,
[tbl_Travel-Schedule].[Name ID] AS [tbl_Travel-Schedule_Name ID],
[tbl_Travel-Schedule].[Project Name],
[tbl_Travel-Schedule].Travel_To,
[tbl_Travel-Schedule].Start,
[tbl_Travel-Schedule].End,
[tbl_Travel-Schedule].Travel_From,
[tbl_Travel-Schedule].Task,
[tbl_Travel-Schedule].Notes,
[tbl_Travel-Schedule].[Commissioning?],
[tbl_Travel-Schedule].DepCity,
[tbl_Travel-Schedule].ArrCity,
[tbl_Travel-Schedule].RetCity,
[tbl_Travel-Schedule].ArrivalDate,
tbl_Names.[Name ID] AS [tbl_Names_Name ID],
tbl_Airports.City,
[tbl_Travel-Schedule]!Travel_From-[tbl_Travel-Schedule]!Travel_To+1 AS
Days

FROM tbl_Names INNER JOIN ([tbl_Travel-Schedule]
INNER JOIN tbl_Airports ON
[tbl_Travel-Schedule].ArrCity=tbl_Airports.Airport) ON tbl_Names.[Name
ID]=[tbl_Travel-Schedule].[Name ID]
WHERE ((([tbl_Travel-Schedule].Travel_From)>Date()));

don't get me started on naming conventions; i'm working with an existing
database.
Click to expand...
Click to expand...
 
Top