Help with MS Access, 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.

Before you ask, I did it in access because it is the only software I can use, and the only available. Please spare the jokes.

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"), and I cannot create a separate table of AM's for each PF (large number of PF's, and to be increased).


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

[Son Goku]

Are you being told you have to do this in one table? In one table, I can't think of a way off the top of my head. It does look like you have a 1 - to - many relationship between PF and AMs. If you could do it in multiple tables, you could have PF as the primary key in one table, AM as a primary key in another, and then link the 2 tables based on AF.

Then to view this...it's been awhile, but

select PF, AM
from PF_table, AM_table
where PF_table.PF = AM_Table.PF AND AM_Table.PF = <whatever you need>;

That could probably be modified for you needs... It's also been almost 2 years since I took SQL, and went off into the DBA (database administrator) side of things...

Perhaps you could make AM the primary key, and do a 1 - many relationship, making PF the many side of it, but only if a single AM can not belong to more then 1 PF. A many - to - many relationship really won't work in a relational database... Then just do your queries to show all instances where PF = whatever (will show multiple entries)...

[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.

[Tuork]

anyone????
pretty please?

[Clay]

You currently have a many-to-many relationship between the PF table and the AM table. This is not normalized. So, the first step is to normalize. You need to create an associative entity (table) that I'll call PF_AM. Both PF and AM will have a one-to-many relationship from themselves to PF_AM.

PF_AM would have a structure something like this:

id
pf_id
am_id

You would then need to add a field named pf_am_id in both your PF and AM tables.

Now, you can get your query results via an SQL statement that would go something like this:


-- query below will return all AM children of PF
SELECT *
FROM AM
WHERE AM.id IN
(SELECT am_id FROM PF_AM WHERE PF_AM.pf_id = 1)


-- query below will return all PF parents of AM
SELECT *
FROM PF
WHERE PF.id IN
(SELECT pf_id FROM PF_AM WHERE PF_AM.am_id = 1)

In each case the last line is where you would specify the ID (primary key) value of either the PF or AM related data you are wanting.

Here are some screens to further illustrate the table structures and the results of the two queries shown above. Note that this is very simplistic data that I used. Hope this helps. :)

PF table strucure
http://www.nvnews.net/temp/pf.gif

AM table structure
http://www.nvnews.net/temp/am.gif

PF_AM table structure (your new associative entity)
http://www.nvnews.net/temp/pf_am.gif

All AM children of PF (first query result from above)
http://www.nvnews.net/temp/am_children.gif

All PF parents of AM (second query result from above)
http://www.nvnews.net/temp/pf_parents.gif

[Clay]

oh, and regarding your report question. I'm not sure, haven't used the Access report engine in ages. Sounds like you need to set a detail band though?