Deb,
How far along are you in development of this database application? The
reason I ask, is that although I would strongly recommend you redesign the
database structure along the lines I mentioned before, doing the redesign
could require a lot of re-engineering of the forms, queries, and reports you
already have created. BTW, most database developers will strongly advise
against putting spaces in the name of your tables or fields. If you feel
you need a space for readability, use and underscore ( _ ).
From the appearance of [tblClient_Visits], it appears that you will only
have data in one of the Other or Child blocks per visit, is that correct?
You didn't send me the indices, but I'll assume that VisitNumber is an
Autonumber primary key (if that is not correct, please let me know).
How do you know which fields (Other1 - Other7, ChildFirst1-ChildFirst14) to
use to record for a particular visit, or does a client bring in multiple
dependents during a given visit, and you just log them all in? For purposes
of the query below, I'm going to assume that the value that goes in these
fields is a name.
What are the values that ultimately go in the following fields ([Other1],
[OtherGender1], and [Relationship1])?
Once you have addressed these questions, I'll continue the re-engineering
thread.
In the meantime, let me address the query from your previous post. The
first thing I would do is create a normalizing query. This query will take
data from a non-normal data structure and put it into one that is easier to
query. To do this, we will create a union query which groups all of the
Other, Gender, DOB, and Relationship fields into 6 columns. The key to a
union query is that each individual SELECT statement in the query must
return the same number of fields, in the same sequence, and they must be of
the same data type as the fields in the same position in the first of the
SELECT statements.
You can start this out by using the query grid to get the first set of
values but it is really just as easy to start out in the SQL view. Create a
new query. Add [tblClient Visit] to the grid, close the dialog box, and
then change to the SQL view. The query will look like the following (I'm
not going to do all of the rows, but you will get the idea). Since you can
join back to [tblClient Visit] to get the visit date and other pertinent
data, I'll leave that out of the union query.
SELECT VisitNumber, "Other1" as FieldTitle, [Other1] as DepName,
[OtherDOB1] as DOB, OtherGender1 as Gender,
Relationship1 as Relation
FROM [tblClient Visit]
WHERE [Other1] is NOT NULL
UNION ALL
SELECT VisitNumber, "Other2" as FieldTitle, [Other2] as DepName,
[OtherDOB2] as DOB, OtherGender2 as Gender,
Relationship2 as Relation
FROM [tblClient Visit]
WHERE [Other2] is NOT NULL
UNION ALL
....
SELECT VisitNumber, "ChildFirst1" as FieldTitle, [ChildFirst1] as DepName,
[ChildDOB1] as DOB, ChildGender1 as Gender,
RelationshipCh1 as Relation
FROM [tblClient Visit]
WHERE [ChildFirst1] is NOT NULL
UNION ALL
SELECT VisitNumber, "ChildFirst1" as FieldTitle, [ChildFirst1] as DepName,
[ChildDOB1] as DOB, ChildGender1 as Gender,
RelationshipCh1 as Relation
FROM [tblClient Visit]
WHERE [ChildFirst1] is NOT NULL
UNION ALL
....
SELECT VisitNumber, "ChildFirst14" as FieldTitle, [ChildFirst14] as DepName,
[ChildDOB14] as DOB, ChildGender14 as Gender,
RelationshipCh14 as Relation
FROM [tblClient Visit]
WHERE [ChildFirst1] is NOT NULL
Hopefully, this will work the first time you run it, but you must pay
meticulous attention as you copy and paste from one group of fields to the
next that you don't forget to change the numeric value at the end of the
field. Once you fill in all of the ... to take account of all of the other
field groups, this query will give you a list of all of the dependents (or
others) that were seen during a given visit. This assumes that the [Other1]
or [ChildFirst1] field will be NULL if those segments were not used. Once
the query runs, save it query as [qryClientVisitNormal].
Then, to test whether you got all of the colums right, you might try
something like the following. This should give you a list of all of the
fields that you have used (Other1 - Other7, ChildFirst1-ChildFirst14) and
the count of the number of times they are used in the table. Don't worry
about the count, it is just there to give you an idea of how many times that
field (or group of fields) are used in the table.
SELECT [FieldTitle], Count([VisitNumber] FROM [qryClientVisitNormal]
GROUP BY [FieldTitle]
To expand this to include the actual client, you could add another UNION ALL
and SELECT statement as shown below, although I didn't see fields for the
client name, dob, gender in [tblClient Visit], so I'm thinking that you may
have these stashed away in [tblClient]. The addition to the above query
might look like:
UNION SELECT
SELECT VisitNumber, "Client" as FieldTitle, [ClientName] as DepName,
[Client DOB] as DOB, [Client Gender] as Gender,
"Client" as Relation
FROM tblClient INNER JOIN [tblClient Visits]
ON tblClient.[Client Nb] = [tblClient Visits].[Client Nb];
The next step is to join this query to [tblClient Visits] to get the Number
in household, for each visit. Since the same client may report different
dependents on different visits. Don't know if this is really an option, or
of importance. If you don't add the last step mentioned above, then you
will need to change the Count( ) to Count( ) + 1 as [# in HH] to account for
the actual client.
SELECT [tblClient Visits].[Client Nb], [tblClient Visits].VisitNumber,
Count(qryClientVisitNormal.FieldTitle) as [# in HH]
FROM [tblClient Visits] INNER JOIN [qryClientVisitNormal]
ON [tblClient Visits].VisitNumber = [qryClientVisitNormal].VisitNumber
GROUP BY [tblClient Visits].[Client Nb], [tblClient Visits].VisitNumber
Getting the age of each of the members of the HH is the next step. The first
thing I notices looking at your query is that you are not computing the ages
on the date of the visit, but rather, on the date that you run the report.
WAS THAT YOUR INTENT? I'll assume not, since that really doesn't make a lot
of sense. So, the query would now look like:
SELECT [tblClient Visits].[Client Nb], [tblClient Visits].VisitNumber,
[FieldTitle], [DepName]
DateDiff("yyyy", [DOB], [Date of Visit]) - IIF(Format([DOB],
"mmdd") > Format([Date of Visit], "mmdd"), 1, 0)
FROM [tblClient Visits] INNER JOIN [qryClientVisitNormal]
ON [tblClient Visits].[VisitNumber] = [qryClientVisitNormal].[VisitNumber]
This is a lot to absorb, so if you get to this point, and are ready to
continue, let me know. Then we can address your original Totals query and
the transformation of that.
HTH
Dale
Deb H said:
Here is the report for the table. I am sending the report for the Client
visits which is the related table that contains the fields we discussed.
This
is the many side of a relationship to a Client table that contains fields
for
the main client (head of household). Thanks for your patience and help.
Columns
Name Type Size
VisitNumber Long Integer 4
Client Nb Long Integer 4
Date of Visit Date/Time 8
Notes Text 255
ClothingOnly Yes/No 1
ExtraFood Yes/No 1
PersonalProd Yes/No 1
Backpack Long Integer 4
Clothes Long Integer 4
Diapers Long Integer 4
GasVoucher Long Integer 4
Layette Long Integer 4
Assistance Long Integer 4
BirthdayBag Long Integer 4
Other Long Integer 4
HHitems Long Integer 4
GiftCertif Currency 8
Other1 Text 50
OtherDOB1 Date/Time 8
OtherGender1 Text 50
Relationship1 Text 50
Other2 Text 50
OtherDOB2 Date/Time 8
OtherGender2 Text 50
Relationship2 Text 50
Other3 Text 50
OtherDOB3 Date/Time 8
OtherGender3 Text 50
Relationship3 Text 50
Other4 Text 50
OtherDOB4 Date/Time 8
OtherGender4 Text 50
Relationship4 Text 50
Other5 Text 50
OtherDOB5 Date/Time 8
OtherGender5 Text 50
Relationship5 Text 50
Other6 Text 50
OtherDOB6 Date/Time 8
OtherGender6 Text 50
Relationship6 Text 50
Other7 Text 50
OtherDOB7 Date/Time 8
OtherGender7 Text 50
Relationship7 Text 50
ChildFirst1 Text 50
ChildDOB1 Date/Time 8
ChildGender1 Text 50
RelationshipCh1 Text 50
C:\Documents and Settings\Deb\My Documents\Database\ACBC
clients.mdb Tuesday, March 17, 2009
Table: tblClient Visits Page: 2
ChildFirst2 Text 50
ChildDOB2 Date/Time 8
ChildGender2 Text 50
RelationshipCh2 Text 50
ChildFirst3 Text 50
ChildDOB3 Date/Time 8
ChildGender3 Text 50
RelationshipCh3 Text 50
ChildFirst4 Text 50
ChildDOB4 Date/Time 8
ChildGender4 Text 50
RelationshipCh4 Text 50
ChildFirst5 Text 50
ChildDOB5 Date/Time 8
ChildGender5 Text 50
RelationshipCh5 Text 50
ChildFirst6 Text 50
ChildDOB6 Date/Time 8
ChildGender6 Text 50
RelationshipCh6 Text 50
ChildFirst7 Text 50
ChildDOB7 Date/Time 8
ChildGender7 Text 50
RelationshipCh7 Text 50
ChildFirst8 Text 50
ChildDOB8 Date/Time 8
ChildGender8 Text 50
RelationshipCh8 Text 50
ChildFirst9 Text 50
ChildDOB9 Date/Time 8
ChildGender9 Text 50
RelationshipCh9 Text 50
ChildFirst10 Text 50
ChildDOB10 Date/Time 8
ChildGender10 Text 50
RelationshipCh10 Text 50
ChildFirst11 Text 50
ChildDOB11 Date/Time 8
RelationshipCh11 Text 50
ChildGender11 Text 50
ChildFirst12 Text 50
ChildDOB12 Date/Time 8
ChildGender12 Text 50
RelationshipCh12 Text 50
ChildFirst13 Text 50
ChildDOB13 Date/Time 8
ChildGender13 Text 50
RelationshipCh13 Text 50
ChildFirst14 Text 50
ChildDOB14 Date/Time 8
ChildGender14 Text 50
RelationshipCh14 Text 50
FTemployment Yes/No 1
PTemployment Yes/No 1
C:\Documents and Settings\Deb\My Documents\Database\ACBC
clients.mdb Tuesday, March 17, 2009
Table: tblClient Visits Page: 3
MFIP Yes/No 1
SocSec Yes/No 1
SSD Yes/No 1
GA Yes/No 1
UsingFS Yes/No 1
UsingWIC Yes/No 1
MA Yes/No 1
Relationships
tblClienttblClient Visits
tblClient tblClient Visits
Client Nb 1 ? Client Nb
Attributes: Enforced
RelationshipType: One-To-Many
