ACCESS FEED: RE: [MS_AccessPros] Crosstab Query

Senin, 27 April 2015

RE: [MS_AccessPros] Crosstab Query

Jan,

IMO, you should be storing the primary key of the lookup table in the status/activity table. I would not use a lookup field in the table design of the status/activity table. If you are using a form with a combo box to enter records into the status/activity table, the bound column of the combo box should be the primary key field.

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 27 Apr 2015 15:01:36 -0600
Subject: Re: [MS_AccessPros] Crosstab Query

Hi Duane.

It seems when I reply using the Yahoo Group REPLY icon this happens. I will try to just answer the emails in the future.

I have a lookup table with the 6 statuses listed. It has only 2 fields. The primary key and the status names.

The status/activity table accesses the lookup table in order to populate the status field. It has Three fields. Primary key, Contact id and activity/status description.

The research I have done seems to indicate the problem may be using the lookup table but I don't want to hard code the choices in the activity/status table. I may have quite a few when I get done.

Thanks

Jan

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Jan

On 4/27/2015 2:21 PM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] wrote:

Jan,

Please include the significant parts of previous messages in the thread so we don't have to look back to find the context. If you are seeing some numbers mixed in with text values, your [Status or Event] is apparently being populated through different methods. One method is getting the values from some ID column while another method is grabbing the text value.

Can you provide information/records from your Status or Event lookup table?

Typically you would have a lookup table of [Status or Event] values with a numeric primary key and a text title.

Duane Hookom, MVP
MS Access

Previous Message:

I have created a table with six statuses and assigned to each person on my contact list. Some people have more than one status. I want to do a crosstab with ID, FirstName, LastName as rows and Status' as columns.

For some reason, it does not seem to pick up all the status names as column headings. I am getting 3, 6, Bike, Friends, etc. Any idea what would cause it to reject my status' as column headings? Thanks

Bike

Friends

Officer

Tax_Sensitive

Trees

LOWV

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 27 Apr 2015 13:10:10 -0700
Subject: Re: [MS_AccessPros] Crosstab Query

TRANSFORM Count([Activities & Statuses for Contacts].[ID]) AS CountOfID
SELECT [Activities & Statuses for Contacts].[Contact ID]
FROM [Activities & Statuses for Contacts]
GROUP BY [Activities & Statuses for Contacts].[Contact ID]
PIVOT [Activities & Statuses for Contacts].[Status or Event];