Special characters in field.

[XTB2004]

Dear Friends,

I have a problem with special characters in a particular field in a table,
which is causing me great problems. Allow me to elaborate.

Background of the original tables and software:

Both the tables, the details of which I have mentioned below, belong to a
particular software which was designed on Visual FoxPro 3.0. This software
was designed and developed by my colleague. However, my boss wants a
revision of certain reports and new ones based on existing data.

The two tables are, viz., "qpatadm" and "qpatchgs":

In the "qpatadm" table there are the following fields:
1. admcode
2. admdate
3. patcode
4. disdate
5. bildate

In the "qpatchgs" table there are the following fields:
1. admcode
2. patchgcode
3. patchgamt

As would be obvious, "qpatadm" table stores records of admission of patients
spread over a period in a year.
Whereas, the "qpatchgs" table stores the charges incurred vis-a-vis each
patient based on his/her unique admission code which is given as "admcode".


What I need to do is the following:

Create a query based on the admission details of each patient and build a
sum total of charges incurred by the patient against the respective
"admcode".


The problem is:

The "admcode" field, in both the tables, is filled with unique sets of
special characters (other than alphabets and numerals) as code identifier of
each patient.

However, when I try to build a SQL statement based on the "admcode" field in
order to reach my goal, Access throws up indeterminate results.

Interestingly, when I select and copy a particular "admcode" from the table
"qpatadm" and make a filter search in the table "qpatchgs", the search
results so produced are exactly what are in actuals and is truly reflected
by the original software in situ.


Can someone please help.
Thanks in advance.

 

[John Vinson]

The two tables are, viz., "qpatadm" and "qpatchgs":

In the "qpatadm" table there are the following fields:
1. admcode
2. admdate
3. patcode
4. disdate
5. bildate

Does this table have a Primary Key? I.e. if you open it in design
view, is there a key icon by one (or more) of the fields? If not, try
selecting admcode and clicking the Key icon on the toolbar. Does it
succeed, or does it complain about duplicate records?

In the "qpatchgs" table there are the following fields:
1. admcode
2. patchgcode
3. patchgamt

As would be obvious, "qpatadm" table stores records of admission of patients
spread over a period in a year.
Whereas, the "qpatchgs" table stores the charges incurred vis-a-vis each
patient based on his/her unique admission code which is given as "admcode".


What I need to do is the following:

Create a query based on the admission details of each patient and build a
sum total of charges incurred by the patient against the respective
"admcode".

A simple Totals query should do the job here.

The problem is:

The "admcode" field, in both the tables, is filled with unique sets of
special characters (other than alphabets and numerals) as code identifier of
each patient.

However, when I try to build a SQL statement based on the "admcode" field in
order to reach my goal, Access throws up indeterminate results.

Indeterminate results almost surely has nothing to do with the CONTENT
of the fields; rather, it means that neither field in the join has a
unique Index. If Access cannot tell which field is unique (should be
admcode in qpatadm, if I'm understanding aright), then it cannot
decide which table is the one, and which is the many, and gives this
"indeterminate" result.

Making qpatadm.admcode the Primary Key (a unique index by definition)
will make qpatadm the "one" side, and qpatchgs the "many".

If there are multiple records in each table for a particular admcode,
you'll need to identify some combination of fields to serve as a PK -
and that may be a real problem.

Interestingly, when I select and copy a particular "admcode" from the table
"qpatadm" and make a filter search in the table "qpatchgs", the search
results so produced are exactly what are in actuals and is truly reflected
by the original software in situ.

It sounds like the Access implementation was not done to completion:
no primary keys, and probably no defined relationships.

John W. Vinson[MVP]

 

[Shotodru]

Thanks John, I shall get back to you after I have tried your suggestions.
[Just for the records, (e-mail address removed) does not exist anymore.]

Best regards,
Satadru