Re: need help designin a report

From: Larry Daugherty (Larry.NoSpam.Daugherty_at_verizon.net)
Date: 01/18/05 

Date: Tue, 18 Jan 2005 08:03:05 -0800

For the main form the query is just on the main table and sorts on the
field(s) you choose.

There will be a query on the child table that does the same. The child
table will have the primary key of the main table as a foreign key. give it
the same name in the child table.

Design your main form based on the main table alone but leave a large empty
space toward the bottom. By default it should display a single form.

Design the form that will become your subform to properly display the things
that could be there more than once. Do not create a header or footer.
Choose "Continuous forms" as the display mode.

With your main form in design mode and taking up only a part of the screen,
find and select your subform to be in the Database|Forms window. Drag the
subform to the proper place on your main form. There is a pretty good
description of the process in Access Help. You may need to iterate through
the process several times to get what you want.

If you have questions, post back.

HTH 

-- 
-Larry-
--
"B Earl" <BEarl@discussions.microsoft.com> wrote in message
news:507AC3DD-D757-4DCC-A3C5-5DF4453D5CAF@microsoft.com...
> so after i design the tables as you suggested, I did not understand your
last
> sentence. "with a proper design, there a form/subform data entry form that
> would require two simple queries" please explain and what are the two
simple
> queries? Thank you
>
> "Larry Daugherty" wrote:
>
> > To go forward properly, you would have to design your tables along the
lines
> > I suggested in the earlier thread in this newsgroup (the subject was
query
> > rather than report - my mistake).  I see no other response to that
thread in
> > this newsgroup and no other newsgroups referenced so you must be
> > multiposting  rather than cross-posting.  By doing that you may waste
the
> > effort of those who try to help you.  When you multipost, only the
thread in
> > the current newsgroup is seen.  Your other post(s) in other newsgroups
may
> > have already received satisfactory responses but only that newsgroup
would
> > know.  If you crosspost by putting two or three newsgroups in the
Newsgroups
> > line in your browser, a response in one newsgroup is reflected in all of
> > them.
> >
> > I don't know the whole story of what your application is intended to do
but
> > I think I know the gist of it.  The tables I suggested in my response in
the
> > earlier thread and the tables you and I referenced in this thread should
all
> > be in the same application with proper relationships.
> >
> > I have already suggested a path to a solution:  enhance your data
> > organization and create relationships as required.  Everything else
flows
> > from good data organization.  Given the lack of a relationship between
the
> > two tables (you really need more tables) you would not be able to
generate a
> > query to do what you want.  Your query would produce the Cartesian
product
> > of the two tables or something like it:  for each and every record
returned
> > from the first table you'd  get each and every record returned from the
> > second table.  With a proper design, there a form/subform data entry
form
> > that would require two simple queries.
> >
> > HTH
> > -- 
> > -Larry-
> > --
> >
> > "B Earl" <BEarl@discussions.microsoft.com> wrote in message
> > news:BBB7B09F-1349-459F-93A1-11B24BC88061@microsoft.com...
> > > Concerning the earlier post, there were two replies and I used the
other
> > one.
> > > It accomplished want I needed. I have about 12 reports done now and
they
> > all
> > > seem to be working fine. I think that this is my last report. What I
need
> > > here is a way to show only contestants that have entered more than one
> > event.
> > > I am not sure how to accomplish this in a query. I would appreciate
some
> > help
> > > from someone. I realize that I am not very knowledgeable about
databases.
> > I
> > > volunteered for this as the association does not have enough money to
hire
> > a
> > > professional. Hopefully I can get this done. Thank  you
> > >
> > > "Larry Daugherty" wrote:
> > >
> > > > You really need to do some motivated reading about Access and
relational
> > > > databases and data normalization.  The descriptions of your tables
and
> > their
> > > > fields suggests that you are still caught up in Excel/spread***
> > thinking
> > > > modes.  In cases where an attribute can have more than one value you
> > have
> > > > simply "added another column".  That's not the relational way.
> > > >
> > > > In your first table you should have a field "Gender" which will
handle
> > both
> > > > sexes.
> > > >
> > > > Your second table should actually be two tables: tblRodeo and
tblEvent.
> > > > tblEvent would contain the unabbreviated name of every possible
event.
> > > > There should be yet another table which you don't yet have called a
> > junction
> > > > table; tblRodeoEvent which would have every event for every member
for
> > > > rodeo.  Since you don't make reference to primary keys or foreign
keys I
> > > > don't believe you grasped the concept I tried to give you in another
of
> > your
> > > > threads.  Records in tblRodeoEvent would have as a foreign key the
> > primary
> > > > key of the member table.
> > > >
> > > > You will not have an easy time getting a usable report from your
> > suggested
> > > > table design.   The tables aren't related so you have no way to
> > associated
> > > > rodeo events with a particular member.  It would be a good idea for
you
> > to
> > > > lurk microsoft.public.access.tablesdesign - that newsgroup is
intended
> > to
> > > > focus on just the design of table.
> > > >
> > > > Your earlier post was also about designing a report.  Again, the
problem
> > was
> > > > in your proposed table design.  If you designed your tables as I
> > suggested
> > > > then creating a report would be a piece of cake.
> > > >
> > > > HTH
> > > > -- 
> > > > -Larry-
> > > > --
> > > >
> > > > "B Earl" <BEarl@discussions.microsoft.com> wrote in message
> > > > news:4895E173-F107-4ACC-A7E3-FEBE629EC789@microsoft.com...
> > > > > My query will have information from 2 tables
> > > > > membership table:
> > > > > number
> > > > > city
> > > > > male
> > > > > female
> > > > >
> > > > > rodeo table:
> > > > > name winnings
> > > > > points
> > > > > bb
> > > > > sb
> > > > > br
> > > > > cr
> > > > > bkr
> > > > > sw
> > > > > hd
> > > > > hl
> > > > > wbr (these last 9 are the events in the rodeo)
> > > > >
> > > > > the information in rodeo table is put in for all the rodeos each
> > > > contestant
> > > > > enters. So a contestants name is put in for each rodeo he enters
and
> > for
> > > > each
> > > > > event he enters. For example he can be at one rodeo in cr and hd &
> > then at
> > > > > another rodeo for cr. This would require entering his name 3
times.
> > > > >
> > > > > I need a report that shows contestants that have entered more than
one
> > > > > event. The report should show their name once with their total
> > winnings
> > > > and
> > > > > points. This would decide the all around winner.
> > > > >
> > > > > Not sure how to accomplise this. Any ideas out there? Thanks
> > > >
> > > >
> > > >
> >
> >
> >
Loading