Query for Form/Subform from single table

[Dave_FFM]

Good Morning,

I have created a database to act as a "customer service system". The current
process for handling customer complaints and queries is by a scribble on a
scrap of paper and so that's why i built it. Because i don't anticipate the
database to be of great size it is a single table. A brief explanation of how
it works is that the call handler will input the customers details onto a
multitab form which has a drop down called "Forward to:" As there is only one
table i have defined the Row Source as the names of the Seven managers who
will deal with the enquiry. Once the enquiry has been forwarded the manager
can view the details in their "Inbox" which is another form which is built on
a query where the user will type their name into a parameter value box
querying the "Forward to:" Field. The user can view all Enquirys which have
not been resolved (which appear on a subform based on the same query) and
then click a tab to take them to an input page where they can document the
resoloution to the customer enquiry. The problem i have lies with the
subform. As it is based on the same query to filter the "Forward to:" field
the Parameter value box flashes up twice, and having tried to create 2
queries (One for "Forward to:" field, other for rest of data) it turns out i
can't because they come from the same table.
Is there an easier way to do this? I have a feeling i may need to split the
table and create one for the "Forward to:" Field. I know i could do it by
creating an individual query/form for each manager but for obvious reasons i
want to keep it to just one.

Many thanks and sorry for waffling

Dave

 

[scubadiver]

The whole point of a subform is that a 1-to-many relationship has been
created between two tables!

What does your table look like at the moment?

 

[scubadiver]

I will also add the size of the database has nothing to do with
functionality. The design has to be correct.

 

[Dave_FFM]

The whole point of a subform is that a 1-to-many relationship has been
created between two tables!

What does your table look like at the moment?

My Table has the following fields;

ID (Autonumber)
Permission Form Number
Feeder Name
Site/Job Number
Client Centre Contact
Landowner Name
Address
Postcode
Contact Details (Phone no E-mail etc)
Date/Time Of Call
Callhandler
Type Of Enquiry
Details Of Enquiry
Forward To:
Enquiry Findings
Close Enquiry (Yes/No)

Looking at it the only thing which is One to Many would be Forward to: (One)
to All other columns (Many). So i guess creating a relationship on that basis
would be the way forward?

 

[scubadiver]

That would be possible but, strictly speaking, it should be a many to many
relationship because each manager can have many enquiries.

So you have a table for managers (ManagerID as PK) and a junction table in
that connects the two

Enquiry -> Junction <- Manager

But just treat like a 1-to many relationship using "junction" and "manager"
in the subform.

 

[Dave_FFM]

Ok i'll do that.
Thanks for your help as always

That would be possible but, strictly speaking, it should be a many to many
relationship because each manager can have many enquiries.

So you have a table for managers (ManagerID as PK) and a junction table in
that connects the two

Enquiry -> Junction <- Manager

But just treat like a 1-to many relationship using "junction" and "manager"
in the subform.



--

The 11th day of every month:

http://truthaction.org/forum/index.php