Adding multiple "responses" under one parent ID

[MP220591]

Hello!

Fairly new to Access in the last few months after taking on a rather large project at work, slowly becoming addicted to Access and my database functionality has grown a lot bigger than we anticipated (not a bad thing!), there is only one thing that is bothering me about it.

We currently log provider responses to each case, The provider responses are added to a table "Provider Responses", and then a query pulls out all the duplicate Client ID's then spits them out into a report, which is then added as a sub report onto the page, the parent and child fields are then linked as appropriate. This works great, however, when asked to re-tender a case, we then need to add on another record, with the same client ID, and so obviously as per the query all responses are showing through for that record (even though we really only want to see new responses).

We're currently working around this by typing "1st tender, 2nd tender, 3rd tender" etc next to the provider's name, but what I am wondering is, is there anyway for us to add multiple provider responses to each individual record, maybe using the individual Access ID (as opposed to our client ID)?

Apologies if this doesn't come across as clear, my explaining skills are sub par!

Thank you in advanced.

 

[plog]

Your explanation might have been perfect, its just the situation is too complex for someone unfamiliar with it to hold in their mind at once--tables and forms and queries and queries that feed subforms and reports and etc.

Usually the best way to convey information on this site is via data examples. Show us what you have, show us what you want to occur. Either do that by zipping and uploading your database, or just with straight data typed into a post.

 

[The_Doc_Man]

We're currently working around this by typing "1st tender, 2nd tender, 3rd tender" etc next to the provider's name, but what I am wondering is, is there anyway for us to add multiple provider responses to each individual record, maybe using the individual Access ID (as opposed to our client ID)?

Focusing on this specific question, the general approach is a parent/child table where the parent record's prime key (PK) becomes a foreign key (FK) in individual response records. You can consider form/sub-form situations to do this, though other ways can work as well for adding responses.

Like a reporter getting a story of WHO, WHAT, WHEN, WHERE, HOW, WHY...

Part of your solution is going to be to decide WHAT you want to see and WHEN you want to see it and HOW you want to see it. We have to trust you for the WHY and you have to decide if the WHO makes a difference in the WHAT (can be seen) part of the problem. The WHERE is easy - on the workstation or on reports built from the workstation. (Probably the ONLY easy part of the problem.) When you can describe in simple English the WHAT, WHEN, HOW parts of this problem, we can give more specific help.

 

[Minty]

It sounds to me as if you are missing a stage / table in your design.

Your clientID shouldn't be the Unique identifier of a tender. You should have a TenderID that has your Client ID in it. That way you can have as many tenders for a client as you need.

Your provider responses would then be linked back to the Tender not the client.

 

[jdraw]

MP,
I agree with the others --seems you do not have your requirements clearly defined. Getting your tables and relationships designed based on your business facts is critical.
First step is to get a clear description of what your business is about; the things involved; in simple English (no jargon).
Research Normalization also.

Good luck.