How do I keep data together on report with group?

[Guest]

I have a database for sponsors of trophys. I have a sponsor table and a
trophy table. The trophy table lists the sponsor. Some sponsors may have
multiple trophies. I created a form to be sent to sponsors with a list of
their trophies. I can't get all of the trophies from one sponsor to print on
one form. I get separate pages for each trophy. Please help if you can. I
am very Access challenged. Thank you.

 

[Al Campagna]

Sonia,
You should have a Group for Sponsors, with Trophies in the detail section.
For now... remove any PageBreaks you may have placed on the report.
Also, check the Sponsor Group Header and Footer and the Detail Section and make sure
that they are all set to Force New Page = None.
Does that make any difference?

It sounds like, from your description, that there may be a ForceNewPage = AfterSection
in your Detail Section.

 

[Guest]

Al, thank you so much for responding to my question. I do have a Sponsor
Group Header and Awards is in the Detail Section. I checked them and set
them to NONE. However, this didn't seem to fix the problem. Do you have any
other suggestions?

 

[Al Campagna]

Sonia,
Well, we could keep on guessing...
If the DB isn't too large, you could send me the "zipped" .mdb file, (my website
"Contact") and I'll take a look at it.
Please indicate what report is the problem in your note, and what version of Access...
No charge of course, and all confidential... I just think that might be faster.

 

[Al Campagna]

Also, post back here when if you send it. My spam control is pretty aggresive...

 

[Resurrection]

If you manage to figure it out, please let me know here as well. I
have a similar problem.
I have a Roster report which pulls all its information from one table.
This table has WorkstationID, Employee ID, and Date fields so I can see
which employee is on which workstation on a certain date. My report is
specifically designed with a combo box for each Workstation (about 25
of them) and each combo box selects its value using a SQL statement
based on WorkstationID. The SQL statement I am using is basically:
SELECT RosterData.EmployeeID, Personnel.Name, RosterData.WorkstationID
FROM Personnel LEFT JOIN RosterData ON
Personnel.EmployeeID=RosterData.EmployeeID WHERE
(((RosterData.WorkstationID)="Name of the Workstation"));

The "Name of the Workstation" criteria is the only part that changes
for each box. The report works great except that it gives me a page
with all the boxes on the report but only one record per page. So all
my boxes are blank except for one of them on every page. So I get 25
pages instead of one page with all the boxes filled in. I don't have
any page breaks or force page turned on anywhere either. My report
must be set up this way to conform to my end user requirements and my
Roster table has to be set up to have only one record per
Workstation/Employee/Date combination.

I guess I need to run all those SQL statements on one of the Events for
the Report maybe? Instead of running one query per page?

 

[Al Campagna]

Res,
When you run the query behind the report...
Could you give me a few lines of the data returned (just the key fields).

 

[Resurrection]

Res,
When you run the query behind the report...
Could you give me a few lines of the data returned (just the key fields).
--

Well, my report is based directly on a table, not a query. I just use
the SELECT statement described above to query out the specific record
that I want to show up in each box on my report. The SELECT query does
give the right data. I don't have any grouping assigned on the report,
maybe a grouping issue?

 

[Al Campagna]

Resurrection,
No big deal, but... please don't erase any previous posts from the "thread" of the
problem. That way, in the latest post, anyone can see the flow of the problem, and all
the suggestions offered so far.

Are you saying that you have 25 "unbound" text controls, each with their own query?
And you also have a table behind the report?
I can only say that that design seems amiss.

If you have designed your tables properly, the individual workstation data should flow
out of the query behind the report. It is very rare to have a table behind a report,
since criteria can not be used against a table. I think you're attempting to create query
criteria "on the report", rather than in the query behind it.
Create query that list all the workstations and their associated employees, add any
criteria against those records as needed to get just the records you wnat returned, and
place those fields in the Detail section of your report.
If that cannot be done, then I suspect faulty table design that won't allow the
workstations and the employees to associate properly.

 

[Resurrection]

Sorry about deleting it, I thought I was helping save space since I
tend to be long-winded. Still learning the local netiquette of this
newsgroup. Thanks for the heads up.

I understand what you are saying. I do have as the source of the
report, but the combo fields on my report are not unbound, they are
bound to the table. You are right, I am creating query criteria on the
report. However considering my table structure:

RosterTable (table with the following fields):
Date
WorkStationID
NameID

So I have a record that accounts for a date / workstation / name
combination for every day.
By trying to implement your suggestion of creating a query on the table
and then basing the report on that query, how do I get it to query out
the info I need?

If I want WorkStation1's associate Name in the first box on my report,
then Workstation2's associated name in the second, etc. this doesnt'
work. Based on your suggestion, a query will produce a list of
WorkstationIDs and associated NameIDs. If I only had one post to
separate out, this would work. But my WorkStationID field can contain
upwards of 25 different workstations, and I need to separate them out
one by one so that I can display them in the correct order on my
report. The end result being that each page of the report tells who is
on every workstation on a given day. So how can a query pull out a
record based on criteria, spit it out to a box on a report, run itself
again, spit out another record to another box based off different
criteria, run itself again, spit out another record, etc etc etc?
Meanwhile keeping every piece of data in its appropriate box on the
same page.

 

[William Hindman]

Resurrection

....sometimes its a matter of deciphering what the other guy is really
saying.
....from your post I understand that you have a table with three fields:
Date/WorkstationID/NameID
....side note: "Date" is a reserved word in Access and using it as a Field
name can lead to problems
....I also understand that you want a report based upon that table configured
as follows:

Date WorkstationID NameID
12Jun06 AlphaWS John Doe

....with a page break between each new date.

....if that is correct, I would base the report upon a query of the table
with Date and WorkStationID set to sort Ascending.

....set the report to group on Date and the GroupHeader to force a new page.

....get rid of the combos.

....then its a simple matter of inserting the three fields in their correct
sections and setting their source

....should give you what you want.

Date WorkstationID NameID
12Jun06
AlphaWS John Doe
BetaWS Mary Doe
------------------------------------------------------page break
Date WorkstationID NameID
13Jun06
AlphaWS Mary Doe
BetaWS John Doe

....hth

William Hindman

 

[Al Campagna]

That's good information. Now we have a better understanding of your setup. (what you have
vs. what you want to see)
Showing some samples of the raw data, and then how you'd like to see it come out on the
report helps a lot.

I would agree with William Hindman's response. Give that a go...

 

[Resurrection]

Thank you both for your assistance, however, your not understanding
what I want. I don't think Access is capable of doing what I need. My
report needs to be in a specific format. That is why I was trying to
fill boxes with data. Alphabetized or numeric listings won't help me.
Certain workstations are grouped together and they need to stay that
way on the report. And they are not grouped alphabetically. I guess
Access can't give me the functionality I want. The combo boxes with a
select statement to query out the pieces I need was the only way I knew
to get specific data to fill a specifc space on a report. This is what
I must do. It is non-negotiable according to my end user requirements.
Can access do what I need or not?

 

[John Vinson]

Thank you both for your assistance, however, your not understanding
what I want. I don't think Access is capable of doing what I need. My
report needs to be in a specific format. That is why I was trying to
fill boxes with data. Alphabetized or numeric listings won't help me.
Certain workstations are grouped together and they need to stay that
way on the report. And they are not grouped alphabetically. I guess
Access can't give me the functionality I want. The combo boxes with a
select statement to query out the pieces I need was the only way I knew
to get specific data to fill a specifc space on a report. This is what
I must do. It is non-negotiable according to my end user requirements.
Can access do what I need or not?

Yes.

You need to *add a field to your table* to specify the grouping order.
This could be alphabetical or numerical, and - since your grouping
order is apparently based on external considerations, rather than on
any value currently in your table - you'll need to add this field and
manually specify the values.

The new field need not be *shown* on your report (probably wouldn't
be) but can still be used as the basis for the grouping.

John W. Vinson[MVP]

 

[Resurrection]

Ahhhh, now I understand. Yes I believe that will work. I will get on
it tonight. That should also simplify my report quite a bit (barring I
don't continue to botch up the implementation). Thank you John, the
way you continue to babysit me and my problems is greatly appreciated.
Your continued patience with me speaks volumes about you.

 

[William Hindman]

Resurrection

....it helps immensely to state the problem accurately and completely in your
initial post ...in this thread it took three iterations before you presented
all of the information necessary to answer what you were really after.

William Hindman

 

[Resurrection]

Well the age old story, what is clear in my mind is not necessarily
clear in the minds of others. I actually was trying to avoid hijacking
this thread too badly considering I was not the OP. So much for that
idea. But your point is correct and I will take it on board. Your
patience is appreciated as well.

 

[Al Campagna]

Resurrection,
Please... consider showing us some sample raw data, and how you want to see that data
displayed, the next time you run into a report layout problem like this.
One "picture" is worth a thousand words...

If I might add a minor suggestion to John's response... I once bulit a "plowing" app
for a friend to plow driveways, so... like you, I needed to sort the houses in the order
they are plowed. I added a Single Type counter field to the table. That would allow a
new customer to be stuck in between two previous customers, as far as plowing order. Like
a Dewey decimal sysytem...
PlowOrder Name
3.0 Smith
4.0 Jones

Later on...
PlowOrder Name
3.0 Smith
3.5 Brown
3.75 Allan
4.0 Jones
Now you won't have to reorder everyone if a new item is added to your table