Don't know what to call it--relationship search?

[Jana Marcus]

Help! I have a large database which is primarily contact information. What i need to do is gather information for each contact that is "a year" and the amount of $ given for that year. Currently i have a pulled-down menu that displays "2007." I also have a separate field where the # amount given that year is displayed. I need to make the year field a pulled down menu that displays several years. BUT, when i search the dollar amount for 2007 it is not be tagged with the specific year correctly.

Can some one please tell me how I should set up this relationship?

Ultimately i need to have several years that each correspond with a different $ value.

Thank you!

jana

[bcooney]

I would structure it with two tables: Contact and Donations. Then have a portal on contacts that shows the related donation records. Each donation record would have Amount and Year.

Then you can create calc fields in the Donation tables for reports by year or a sub-summary report.

[Jana Marcus]

Thank you for your response. I have bought a book and trying to figure this out...but it is very comlex (to me). I guess i'm not understand how, if i have two tales, they are relating to each other when i input data. For instance:

If i have one table for contacts

one table for donations

DO i put contacts as a field in both tables so I know who I'm dealing with?

I'm not creating this from scratch. I have a database with 9000 names in it that i have to modify, so putting a contact ID number seems out of the question.

Help! I'm loosing it here!

Jana

[Fenton]

A flat table with 9000 entries is not terribly big. Whether what you've proposed is difficult or not depends a great deal on whether the contact was entered consistently.

The reason bcooney suggests 2 tables is because:

1. It's the correct structure, and,

2. You said "gather information for each contact..."

This implies that you can positively tie together records of each contact. Once you've done that, you might as well put them in another table, so that you don't keep having these kind of problems.

It implies that every time a "contact" was entered in your file it was entered exactly the same. If not, you've got to fix the variations. You can see how bad the situation is by going into Find mode (Cmd-F), then putting your cursor in the name field, and hitting Cmd-I (Insert from Index). This will show you the "index" of the field, ie., all unique entries. Or, Show All Records, Sort by name, Export, [x] Group by name, export the name field.

In other words, you're going to have to clean up inconsistent entries, or else you cannot "gather" the info. That is why we use separate tables and IDs; it avoids this problem.

Adding an ID is not a problem, no matter how many records, if the data is consistent. Nothing is really workable until it is. Since we cannot see your data, we cannot judge that. We can walk you through the steps, but you're going to have to do the cleanup. Let us know and we can give more extensive directions (it's late here). There are some posts here about it, but likely spread here and there.

[Jana Marcus]

Thanks Fenton (?)

The data is pretty clean for 9000 contact database I have to modify. If I could pay someone to help me with this i would. I think because i'm a visual person, I'm not seeing the connections in my mind and thus getting stumped.

Do you recommend a consultant in my area (Santa Cruz County)? Or can I call you? You can email me at [email protected]

thanks!

J