Help with contact management data base, ASAP!

[Tuork]

Here's the deal:
I am designing a contact management data base for the company my sister works for. It is very informal and nothing fancy, but I am having some trouble with one particular issue.

There are 2 types of companies, Pension Funds (PF) and Asset Managers (AM), each with it's own list of various contacts.

A PF can hire multiple AM's, and an AM can be hired by multiple PF's.

The data base must be able to show me a list of AM's working for a PF, and vice versa (PF's hiring a certain AM).

Problem is: I can think of no practical way of doing this, seeing as I CAN'T just simply list (separated by comma) the ID of the different AM's inside a certain field (for instance, "Asset Managers hired") because access can't match part of the data in the field, it has to match exactly.
Also, I cannot create a separate table of AM's for each PF because there is large number of PF's which is to increase as time passes.


For the love of God, please help me out!
I will greatly appreciate it.

 

[ByteMyzer]

Would it be possible for you to post a copy of the database file? It would be easier to point you in the right direction.

 

[Tuork]

Yes, and no... I have no medium by which I could post it, unfortunately.

I will try and get my hands on some hosting from one of my friends and come back.

If any idea pops to mind, any at all, pray tell!

 

[aleb]

Try this dbase

 

[ByteMyzer]

Tuork, you mean to say, you are not able to use this web page's own Attachment Manager to upload the file to the forum??? When you post a reply, you should see a button toward the bottom of the screen that says Manage Attachments.

 

[Tuork]

DOH!

Sorry about that.. I thought I could only upload jpg images and whatnot. My bad.


As for the dBase... I talked to someone who gave me some ideas.
And the one by Alexei also gave some good ideas, even though I don't understand well the workings of it.

I will see where the new leads take me and report back soon.

Once again, thank you very much for your help, I really appreciate it.

 

[Tuork]

Here's the DB, by the way....

... this is the new version.. which I think I over-complicated with the relation ships.
I will proceed to create the form with list which are limited by other lists.

 

[Tuork]

Still no go with the PF-AM relationship... although I have a new approach with SQL, but not quite perfect (more info further on).

I want to leave that for last. For now I have other problems.

Aside from the relationship I already mentioned between PF and AM's, there are also lists of multiple contacts for each AM and each PF, as well as cal logs for each company.

How can I create a report that can print out the "sub-data" involved?
If I do a query, it will only show ONE fo the contacts and one of the call logs, not all.
Same goes for using Sub-reports.

Anyone have any ideas?


And now onto SQL...
A contact gave me the following idea:
A third table that links the data between AM and PF tables, and a SQL query that looks up the data for a respectivo PF ID.

SELECT * FROM pf 
WHERE pf.id = link.pf_id 
AND link.am_id = am.id 
AND am.id = "number"

where "number would be the paramater for which to search.

Now, how does one go about Access in order to create a query that asks for a parameter upon opening it?????


That's all for now. Thank's, again.

 

[aleb]

Hello Tyork,
Have you tried the example ?
And what didn't you understand exactly:
When you open form you need to select the type of company.ONce you select the type of the company the drop down list will show you list of the contacts for that company. Once you select the contact you go to the subform and select the hiring person from the oposite company. This info is stored in the table "hired". Is it what you wanted ?

 

[Tuork]

What I didn't understand is the way that it works... kinda.

The contacts are not involved in this part of the dBase... each company has it's contacts, but they are not related (the contacts).

It would be fine if once you chose the type of company (AM or PF), it would display a list of such companies, and when you chose one of those (I would imagine based on the name), it should show you which AMs are hired by it (if you chose a PF)
or which PFs are HIRING (if you chose an AM).

I'll be away over the weekend to boston.
I'll get back to you with updates as soon as I get back.


what about the idea using he SQL thingy?

 

[Tuork]

Help, please?


...


pretty please?

 

[ByteMyzer]

Try this file now. Go to the Debug Window (CTRL-G) and type the following and press Enter:
?AMList(1)

This should return a list of the related AM's to PF ID 1, separated by commas.

Likewise, if you enter:
?PFList(1)

This should return a list of the related PF's to AM ID 1, separated by commas.


To see the code, open the included Module modList.

See if this works for you.

 

[Tuork]

GOT IT!!!

w00t!

So I finally managed to do the damn thing. Thanks a lot people, you really helped me out.

ByteMyzer, I haven't had a chance to look at the one you just posted, but I will soon.


Thanks, again.