need help designin a report

[Guest]

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

 

[Larry Daugherty]

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/spreadsheet 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

 

[Guest]

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

 

[PC Datasheet]

How much money do you think it takes to hire a professional? Try me, my fees
are very reasonable!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

 

[Larry Daugherty]

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

 

[Guest]

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]

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

 

[Guest]

Sorry but that is not the results that I am looking for. If I have all the
events listed in a drop down box on the form, is there an expression i could
use in a query that would draw out any person that entered more than one
event?

 

[Larry Daugherty]

Look in Help for Totals Query.

--
-Larry-
--

Sorry but that is not the results that I am looking for. If I have all the
events listed in a drop down box on the form, is there an expression i could
use in a query that would draw out any person that entered more than one
event?