related tables? - help please!

[Roberto]

Hello,

I have a table that contains a project name for each record. These projects
can generate one or more publications. However, if I put them on the same
table and record, it is hard to query them and count the number of
publications by type or by status.

I was wondering if it is possible to create a table with only the project
name and then another table that contains the different publications for each
project name. I am not sure table relationships work for this.

For example, my current table has the fields: project name, pub1, pub1_type,
pub1_status, pub2, pub2_type, etc. Is it possible to move the publication
information into another table and link them to a single project name record
in another table even though the publications may be more than one record?

Hopefully this is clear. Help please!

Roberto

 

[strive4peace]

Hi Roberto,

yes, the related table would look something like this:

ProjectPublications
- ProjPubID, autonumber -- PK
- ProjectID, long integer -- FK to Projects
- PubTypeID, long integer -- FK to PublicationTypes
- PubName, text

and you would have these other tables:

Projects
- ProjectID, autonumber -- PK
- other fields describing the project

PublicationTypes
- PubTypeID, autonumber -- PK
- PubType, text


FK = Foreign key
PK = Primary key

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

*
have an awesome day
*

 

[Peter Hibbs]

Roberto,

As Crystal has pointed out, you will need to create two tables that
are linked on a common field. If you already have a large amount of
data in your main table it is difficult to move the data from your
main table into the other two tables while keeping the relationships
intact.

You can, however, do this automatically using the Excel to Access
Converter Utility program at the following Web site :-
http://www.rogersaccesslibrary.com/...?TID=183&SID=cffd26c188ce42852zza9dza3z4c9fd6

If you export your main table as a .csv file (using the Access Export
facility) and create your two new tables you can then import the data
directly into the two tables using the utility program.

HTH

Peter Hibbs.

 

[Roberto]

Crystal,

Thank you very much for your prompt response. I will try what you suggested.
One final question, will I be able to collect data for all those tables via
email? I think I read on a tutorial or something that AutoNumber fields
cannot be collected using email.

Thanks a lot.

Roberto

 

[Roberto]

Peter,

Thanks for your quick response. I have not populated the table yet. I am
still designing to make sure the one-to-many relationship works. I am new at
this, so I may post other questions for you later. Bottom line, the project
table will need to be related to three other tables: publications, additional
funding, and project names. I will follow Crystal's steps. Thanks

 

[strive4peace]

Hi Roberto,

sorry, I will not be able to help you with the email portion... once
your data structure is good, you can ask that question in a separate thread

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*