multiple data with multiple contacts

[CozmicOne]

I am cleaning up a dbase that has single contacts, with a possible 6 contact
types, taken from a list of 27 contact types.

The current Contact table has fields like this:
Contact Name – Contact Type 1 - Contact Type 2 - Contact Type3 – etc…
which results in creating queries that require you to duplicate your
criteria in six Contact Types.

To make reporting this information easier, and to not waste space with empty
cells, I setup a dbase like this. I created two tables. The first table
“Contacts†has Contact ID and Contact Name. The second table “Types†has
contact ID and Contact Type. The two tables are joined together in a
one-to-many relationship. So the resulting data is
Contact ID | Contact Type
1 A
1 B
2 C
3 A
3 D

The problem is when reporting out the data the single Contact Type field can
create a very long report as each contact type creates a new line on the
report. Is there a way to list out the types horizontally on a report with
this type of setup?
i.e. A B C D instead of
A
B
C
D

Or to have multiple Contact Type fields on the report that reports all the
Contact Types for a contact? (Which resembles our current reports)

If not, what would be a better way to setup the tables?




(I apologize if this has been asked and resolved somewhere else. I wasn't
sure how to ask this question when conducting a search)

 

[KARL DEWEY]

Try this --
TRANSFORM First(CozmicOne.[Contact Type]) AS [FirstOfContact Type]
SELECT CozmicOne.[Contact ID]
FROM CozmicOne
GROUP BY CozmicOne.[Contact ID]
PIVOT IIf([Contact Type]="A",1,IIf([Contact Type]="B",2,IIf([Contact
Type]="C",3,IIf([Contact Type]="D",4,IIf([Contact Type]="E",5,6)))));

 

[CozmicOne]

I see where that is going, but here is the problem. The resulting crosstab
query would have 27 individual fields, which are 27 fields I’ll need to have
on my report. (That is if I’m correctly interpreting what you wrote)

This is on the right path for me though. Ultimately I’d like to create a
crosstab query that can convert this:

Contact ID | Contact Type
1 A
1 D
1 E
2 B
2 F
3 F
4 B
4 C
4 G

to this:

Contact ID | Contact Type 1 | Contact Type 2 | Contact Type 3
1 A D E
2 B F
3 F
4 B C G

So the first Contact Type value for the Contact ID gets placed into Contact
Type 1, the second value gets placed in Contact Type 2, etc…

(What I want to do is starting to become clearer. Now I just need to figure
out how to get to there from here.)

Try this --
TRANSFORM First(CozmicOne.[Contact Type]) AS [FirstOfContact Type]
SELECT CozmicOne.[Contact ID]
FROM CozmicOne
GROUP BY CozmicOne.[Contact ID]
PIVOT IIf([Contact Type]="A",1,IIf([Contact Type]="B",2,IIf([Contact
Type]="C",3,IIf([Contact Type]="D",4,IIf([Contact Type]="E",5,6)))));

I am cleaning up a dbase that has single contacts, with a possible 6 contact
types, taken from a list of 27 contact types.

The current Contact table has fields like this:
Contact Name – Contact Type 1 - Contact Type 2 - Contact Type3 – etc…
which results in creating queries that require you to duplicate your
criteria in six Contact Types.

To make reporting this information easier, and to not waste space with empty
cells, I setup a dbase like this. I created two tables. The first table
“Contacts†has Contact ID and Contact Name. The second table “Types†has
contact ID and Contact Type. The two tables are joined together in a
one-to-many relationship. So the resulting data is
Contact ID | Contact Type
1 A
1 B
2 C
3 A
3 D

The problem is when reporting out the data the single Contact Type field can
create a very long report as each contact type creates a new line on the
report. Is there a way to list out the types horizontally on a report with
this type of setup?
i.e. A B C D instead of
A
B
C
D

Or to have multiple Contact Type fields on the report that reports all the
Contact Types for a contact? (Which resembles our current reports)

If not, what would be a better way to setup the tables?




(I apologize if this has been asked and resolved somewhere else. I wasn't
sure how to ask this question when conducting a search)

 

[KARL DEWEY]

Try this. The first query makes a table.

SELECT T.[Contact ID], T.[Contact Type], (SELECT COUNT(*)
FROM [CozmicOne] T1
WHERE T1.[Contact ID] = T.[Contact ID]
AND T1.[Contact Type] <= T.[Contact Type]) AS Parcel INTO CozmicOne_1
FROM CozmicOne AS T
ORDER BY T.[Contact ID], T.[Contact Type];

TRANSFORM First(CozmicOne_1.[Contact Type]) AS [FirstOfContact Type]
SELECT CozmicOne_1.[Contact ID]
FROM CozmicOne_1
GROUP BY CozmicOne_1.[Contact ID]
PIVOT "Contact Type " & [Parcel];

I see where that is going, but here is the problem. The resulting crosstab
query would have 27 individual fields, which are 27 fields I’ll need to have
on my report. (That is if I’m correctly interpreting what you wrote)

This is on the right path for me though. Ultimately I’d like to create a
crosstab query that can convert this:

Contact ID | Contact Type
1 A
1 D
1 E
2 B
2 F
3 F
4 B
4 C
4 G

to this:

Contact ID | Contact Type 1 | Contact Type 2 | Contact Type 3
1 A D E
2 B F
3 F
4 B C G

So the first Contact Type value for the Contact ID gets placed into Contact
Type 1, the second value gets placed in Contact Type 2, etc…

(What I want to do is starting to become clearer. Now I just need to figure
out how to get to there from here.)

Try this --
TRANSFORM First(CozmicOne.[Contact Type]) AS [FirstOfContact Type]
SELECT CozmicOne.[Contact ID]
FROM CozmicOne
GROUP BY CozmicOne.[Contact ID]
PIVOT IIf([Contact Type]="A",1,IIf([Contact Type]="B",2,IIf([Contact
Type]="C",3,IIf([Contact Type]="D",4,IIf([Contact Type]="E",5,6)))));

I am cleaning up a dbase that has single contacts, with a possible 6 contact
types, taken from a list of 27 contact types.

The current Contact table has fields like this:
Contact Name – Contact Type 1 - Contact Type 2 - Contact Type3 – etc…
which results in creating queries that require you to duplicate your
criteria in six Contact Types.

To make reporting this information easier, and to not waste space with empty
cells, I setup a dbase like this. I created two tables. The first table
“Contacts†has Contact ID and Contact Name. The second table “Types†has
contact ID and Contact Type. The two tables are joined together in a
one-to-many relationship. So the resulting data is
Contact ID | Contact Type
1 A
1 B
2 C
3 A
3 D

The problem is when reporting out the data the single Contact Type field can
create a very long report as each contact type creates a new line on the
report. Is there a way to list out the types horizontally on a report with
this type of setup?
i.e. A B C D instead of
A
B
C
D

Or to have multiple Contact Type fields on the report that reports all the
Contact Types for a contact? (Which resembles our current reports)

If not, what would be a better way to setup the tables?




(I apologize if this has been asked and resolved somewhere else. I wasn't
sure how to ask this question when conducting a search)

 

[Larry Daugherty]

I looked down the thread and decided to come back here to post. Your
original schema was wrong. Someone was thinking in Excel. Your new
schema is better. Some notes:

Any time you have repeating fields that record the same kind of data
you have a huge clue that you need to create a one-to-many
relationship to a new table in which each of the repeating events
becomes a new row.

I get hints that you may need more fixes in your schema but I'll stick
with the current issue.

I recommend a new structure in which tblContactName is on the one side
and tblContactEvent is on the many side.

There should also be a table ~tblContactType that lists every type of
contact. Its fields might be ContactTypeID, Autonumber;
ContactTypeName, Text; ContactTypeDescription, Text; ContactTypeNotes,
Text.

The fields in tblContactEvent might be something like:
ContactEventID, Autonumber; ContactNameID, long integer;
ContactEventSequence (if you need it), integer; ContactTypeName, Text
(I'd use the name rather than the Primary Key if there is any
liklihood that the contact list might change over time - that way, the
ContactTypeName will remain forever what it was when you entered it
rather than becoming something new if you change things later.
ContactEventNote, text or memo if the notes might be longer than 255
characters.

My first thought is that paper is cheap. Just a few hours of your
time will more than pay for all of the paper that the reports are
likely to consume over the lifetime of your project. However, I'd
probably be just as fussy about it as you are.

Don't apologize about the possibility of repeating a previously asked
question. Most posted issues are about things that have posted
before. Answering them keeps everyone's skills sharp and others
lurking the newsgroups will benefit too.

The first look at your issue makes one think of unlti-column reports.
I don't think the standard answer there will address your problem.

I'll present one solution with lots of moving parts but the end result
is that it will resolve your issue:

Keep on your new schema or even look to the schema suggested above. (I
had created it before I read all of your original post because my
thinking was locked on to where the thread had gotten.

I suggest that you make a copy of what you have and store it away in a
safe place.

Open your narrow but tall current report for design. In the
underlying query, remove the name in the ContactType field and replace
it with CombinedTypes: =FormatCombinedTypes(ContactID).

Now you need to create the function procedure

FormatCombinedTypes(ContactID as Long Integer) as text

in a standard module. It will open a recordset ~rst on
tblContactType? where ContactID=the value passed into the function as
ContactID. for each record found,
FormatCombinedTypes=FormatCombinedTypes & rst!ContactType & ", ".

To clean up the trailing ", ",

FormatCombinedTypes=left(len(FormatCombinedTypes)-2)
Return

In the design of your current report I assume that you are grouping on
ContactName. In the detail section create a textbox named
txtCombinedTypes. Make it as wide as you would like. Set its Can
grow property to yes. Set the can Grow property of the detail section
to yes. txtCombinedTypes' record source is the new field
CombinedTypes.

You can fiddle with formats in the report and with spacing or tabs in
the function until you have something you're willing to tolerate.

Post back if you have problems.

HTH

 

[CozmicOne]

Your description of setting up the tables, lookups, and joins is exactly how
I’ve set the new dbase up. For simplicity sake I only setup the tables like
this until I can get the reporting to work correctly:

tblContacts
Contact ID, Auto Number (Primary Key); Contact Name, text

tblContactTypeJoin
Contact ID, number (lookup tblContacts, Contact ID); Contact Type, text
(lookup tblContactType, Contact Types)

TblContactType
Contact Types, text

I could basically see what you are getting at in your description but I
wasn’t able to implement it.

Another thought I had was wanting to know if it’s possible to auto sequence
numbering based upon the subreport. So each contact type you enter in the
subreport for a contact gets a number automatically assigned to it based upon
how many previous contact types have been entered:

Contact ID | Contact Type | Contact type number
1 A 1
1 B 2
2 C 1
3 A 1
3 D 2
4 A 1
4 C 2
4 F 3
4 G 4

I did a test table setup like this and then created a crosstab query using
the table. Contact ID as the row, Contact Type Number as the column and
Contact Type for data. It resulted in the following:

Contact ID | 1 | 2 | 3 | 4
1 A B
2 C
3 A D
4 A C F G


I’m stuck in not being sure if it’s possible to have each contact type
entered for a Contact be automatically numbered in sequence.

Another issue with this would be if a contact type was removed. If in the
case of Contact ID 4, if A was deleted would C then become Contact Type
Number 1, F number 2, and G number 3?

 

[Duane Hookom]

It generally helps if you provide names of all tables and fields.

TRANSFORM First(tblCozmicOne.[Contact Type]) AS [FirstOfContact Type]
SELECT tblCozmicOne.[Contact ID]
FROM tblCozmicOne
GROUP BY tblCozmicOne.[Contact ID]
PIVOT "Contact Type " & DCount("[Contact ID]","tblCozmicOne","[Contact ID]="
& [Contact ID] & " AND [Contact Type]<='" & [Contact Type] & "'");

 

[Larry Daugherty]

Your description of setting up the tables, lookups, and joins is
exactly how

I've set the new dbase up. For simplicity sake I only setup the tables like
this until I can get the reporting to work correctly:

I'd get the reports to work on the actual table design. No good
reason to get them to work on tables configured any other way.

tblContactTypeJoin
Contact ID, number (lookup tblContacts, Contact ID); Contact Type, text
(lookup tblContactType, Contact Types)

I don't know what tblContactTypeJoin is. I didn't suggest it

When you have a form open on tblContact and a subform on
tblContactEvent there will be a combobox in each row of the subform
(*not* of the underlying table) on tblContactEventType that retrieves
the Contact Event Type.

----

I can see where you might want to number the wequence of contact
events for a particular Contact Name but not why you might want to
number the contact type. In any case, you'd have to manage the
sequence number yourself. A word of caution: Do not use the
Autonumber datatype for this purpose. Create and manage those numbers
yourself. See www.mvps.org/access

Another issue with this would be if a contact type was removed. If in the
case of Contact ID 4, if A was deleted would C then become Contact Type
Number 1, F number 2, and G number 3?

You can define it to behave any way you want it to behave. That's
really a business rule whether you recognize it as such or not. I
would complete that definition before starting to implement it in
code. It's up to you to write all of the code to manage it for the
proper results.

HTH