Capturing mandatory data

[hughess7]

Hi all

I have a design question I am hoping someone will be able to help me with?
We have a system to audit claims to a subset of issue codes. If a claim fails
an issue the Issue Code is recorded against it (one to many). The Issue text
can have various bits of data which has to be captured by the end user. At
the moment we do this by having a text field associated to an issue code,
with fixed text

eg the hours recorded were xxx but xxx were claimed.

The end user completes by overtyping the x's. Not every issue code has
required date though, others have between approx 1 and 4 bits, all varying
types - some free text, some dates, some numbers etc.

Is there a different, more efficient way to do this? So we can identify the
incorrect date or amount etc but still concatenate it in a sentence as above
on reports?

Sorry, bit complicated to explain but hopefully you get the gist...

Thanks in advance for any help.
Sue

 

[Mr. B]

Sue,

The thoughts that I have are that you could create a table that would define
the variable data that would be needed for each of the Issue Codes. Since
you already know the constant text that is in each issue, you would not have
to store that. (If there is the posibility that there may be a need to change
the "constant" text you may have to reconsider this)

Once you have created the table that would hold all of the variable pieces
you can present controls to the user to collect only the variable info. Then
at report time, you can simply have the fields inserted in the constant text
where they belong and it will act just like a murge.

I know that I have over simplified the issue, but sometime all someone needs
is a nudge in the right direction. There will certainly be more issues to
deal with than I have address here, but it might be a good place to start.

 

[aaron.kempf]

SQL Server can enforce _ANYTHING_ much much much better than jet

 

[Larry Kahm]

Aaron,

That may very well be the case - but you haven't provided the OP with
anything constructive to help solve the problem.
How would you set up the necessary tables and constraints to do what was
requested?

Larry


SQL Server can enforce _ANYTHING_ much much much better than jet

 

[hughess7]

Thanks Mr.B!

I had actually come to the same conclusion of having a specification table
defining the parameters, but I haven't got it all clear in my head yet as to
what this would like and how the system would know how many parameters to ask
for. I guess maybe a lookup to the Issue Code in the parameters table would
work somehow.

I am not sure I understand your point about not having to store the std
text, but yes this CAN change so it would need to be stored.

The other problem is how to insert the parameters in between the std text in
the right place, like a merge - as you know an Access report is hugely
different from Word and not as flexible (or maybe you were thinking of
Word?). The reports are produced in Access for this. I think maybe I could
achieve this by splitting the Issue Text, so I store it in parts in different
fields, so I can concatenate this all together with the parameters. Again,
not sure how this would work in theory but it gives me ideas to start with
anyway, thanks...

Sue

 

[hughess7]

Ok some more thoughts, I am trying to work out how to define and use the
variable table. I had thought against each Issue Code I could have 5 fields
(max variables obtained per code I think) and specify each format type of the
variable

eg 2100, d, n, t, etc where d = date, n = number and t = text

Then somehow on the fly create these control when a specific Issue Code is
chosen to prompt the user for data. I am not sure though how I would then
store the collated data in a table for future use, I could however use this
to concatentate in to a new text string to form the whole of the Issue
description.

Am I on the right lines do you think? Although I don't have any experience
of creating controls on the fly and I don't think parameter boxes for example
have any formatting/input masks applied to them to ensure a valid date is
entered etc.

Sue

 

[aaron.kempf]

ms access design view.

File, New, Project (existing data)

or Tools, Database Utilities, Upsizing Wizard if you must