Make table query, Report, module, macro?

[Rebecca]

This is my second time posting this quesiton. I need help and am hoping it
is seen by someone who missed it last time. I have also posted in another
access help community and no help. Am I asking something that is too
difficult?

I have a database with the two different tables of information (committees &
Activities) that I need to gather the information from.

What I need is a table that list
MemberID | Activity | Date | Activity | Date | Activity | Date | ... and so
on.

Then I need another table that list:
MemberID| committee | Date | committee | date | committee | date| ... and so
on

Right now the information is in this format:
MemberID | Activity | Date
MemberID | Activity | Date
MemberID | Activity | Date
and so on, same with the committees table. I have a sample database with
just these tables in it and also the macro and module that someone made for
me the last time I asked this question. However, I can't get them to work
this year. Maybe I changed something? I don't know..

I'm sorry for being so stupid about this. I want to learn, but visual basic
just will not click in my head!

Thanks again,
Becca

 

[Van T. Dinh]

It sounds horrible but I got a feeling that the Table Structure you have
might not be the appropriate one. (Blame the "someone" who made the
database for you. <smile>)

I think you need a lot more than 2 Tables since the relationship between
Members and Committees is usually a Many-to-Many relationship and you need 3
Tables to represent this M-to-M relationship. On top of that, you have the
Activity entity to be included.

I would suggest deferring what you are trying to do until later. In the
mean time, read up on Relational Database Design Theory and the Database
Normalization and then consider whether your Database Structure follows the
Relational Database Design Principles and properly normalized. Only then,
you will be able to use Access (or in fact, any RDBM software) efficiently
the way it is designed to be used.

HTH
Van T. Dinh
MVP (Access)

P.S. It has nothing to do with VB or VBA so far.

 

[Martin]

It seems the problem may exist in defining the input data and outcome(s)
desired.
Q1: Are the members of the committee promoted members?
Q2 : Are you logging member activities?

As membership in general may change, including promotion of member to
committee position, then it may be worth considering a table for members,
identifying if a committee member and then a seperate table linked by member
ID to record activities.
Example:
tbl_members
memberID ; (unique code to identify this record and a key value)
position ; member or commitee member
date ; of promotion to committee

tbl_activities
memberID ; number or integer to link with similar field in tbl_members
date ;
activity;

join tables in 1 to many, where members is 1 and activities is many.

If needs are to record committee activities as well, then a third table will
be required thus;
tbl_committee
committee ; (unique code to identify this record and a key value)
members ; build the committee from memberIDs'
date ;
activity ;

This is quickly put together and untested!
Please reply via group if it works or not.

No need to be sorry - we are all stupid.

 

[Rebecca]

I know this is the way to keep it stored in the database. I totally
understand the normalization of it.
However, because of a report I need to produce, I need it to be in the other
table form for the merge. I can not do the report in the form that is being
asked of me with the information stored that way. (don't get me wrong, for
the database and storing I am keeping the information the way it is, with
the look up table and such, but I need a make table query or something so
that I can do the merge)

Rebecca

 

[Rebecca]

I have :
MemberID table
Committess table
Committesslist table

I can only blame myself!

I can send you a sample database if you have time to look at it?

Rebecca

 

[Rebecca]

I believe this is how I have my database set up. I responded to someone
else about the tables that I have. I do have a sample database if you can
look at it.

My problem is getting the information in the form that I need to do the
report merge.
Here is a sample of the report I have to prepare:

Akers Firm nameHouston (18)

Date Joined: 4/3/1983 Bar License:953250
License Date:1981 DOB: 10/30/1956 DRI Member:
YES Number of PAC Contributions: 18

Meetings:

91 Spring; 91 Fall; 94 Fall; 95 Winter; 96 Fall

Seminars:

92 Law Mgmt; 98 Law Mgmt; 00 Law Mgmt; 00 One Day

Activities:

1990 EDITOR Bad Faith; 1990 LEGISLATIV; 1990 Trial Academy;
1990 WORK COMP; 1990 CORP SPKR; 1991 TEXAS; 1991 V P; 1991 LEG
DTPA; 1992 District Director; 1993 LEGISLATIV; 1993 District Director;
1994 LEGISLATIV; 1994 District Director; 1995 District Director; 1996
District Director; 1997 District Director; 1997 Nominating; 1997
PROFESS; 1998 District Director; 1998 SPKR Law Firm Mngt; 2000
Director at Large; 2001 Director at Large



Alderman Firm Name2, L.L.P., Lufkin (2)

Date Joined: 12/18/1981 Bar License:0000000 License
Date:1979 DOB: 11/6/0000 DRI Member: NO
Number of PAC Contributions: 6

Meetings:

01 Fall

Seminars:

01 Nacogdoches CLE

Activities:

 

[Van T. Dinh]

What fields do you have in the CommitteeList?

Don't you have a Table for Activities?

Perhaps, it is not of any benefit to you to send me the database. It is
better for YOU to understand the Relational Database Design Theory then you
will know how to do this and other databases in the future! Please check
the suggestion in my last post.

 

[Rebecca]

I'm sorry but I don't think you understand my problem. I believe I have the
information "stored" correctly. I just don't know how to get it in the
format that I need to run my report.

Committee list fields:
Committee

Comittee fields:
MemberID
Committee
Date

Then I have my main Memberfile that contains the MemberID

The activities table is set up the same but replace committee with activity

Again, my problem is the report I need. How I can get the information to
list in rows of MemberID so that I can do a merge. If you look at one of my
last responses to another reply to my post, I posted a sample of the report
I need to produce.

Rebecca

 

[Van T. Dinh]

OK. In the first post, you wrote:

"... I have a database with the TWO different tables of information. ..."

In your last reply (after my questions), you now have FOUR Tables:
CommitteeList, Committee, MemberFile and Activities!

There is a BIG difference between using 2 Tables and 4 Tables to model the
entities and relationships you are storing in your database. Hence, my
first reply to your original post which was correct with the information
given!

That's said, you want to create a concatenated list of "sub-record" values
(i.e. a list of Activities or Committees for each Member) and the following
The Access Web article should help:

http://www.mvps.org/access/modules/mdl0004.htm

 

[Rebecca]

I am sorry I didn't word my original question correctly, thank you for
taking your time with me.

Rebecca