Primary key used in PivotTable view

[TomBP]

Hi,

I have a table (Quarter) which acts as a source for another table. When I create a query based on the latter in the Datasheet view the entries of the second column of table Quarter are shown. The PivotTable field however displays the primary key.

Where do I need to make the change to only have the second column entries (Q1, Q2, ...) shown in the PivotTable view as well?

See attachment for print screens.

 

[vbaInet]

It's been a long time I used this functionality but Pivot Chart Vendor is a form right? And I'm guessing Quarter in your form is a combo box?

 

[TomBP]

It's not a form but a query.

 

[vbaInet]

Let's see the SQL of the query.

 

[TomBP]

SELECT Data.Quarter, Data.Vendor, [EH&S]+[Adherence DD] AS [Sum]
FROM Data
WHERE (((Data.Vendor) Like "*" & [Geef de naam] & "*"));

 

[vbaInet]

Can you upload a sample db?

 

[TomBP]

Sample database is attached.

 

[vbaInet]

See attached.

The main problem was that in the table you made the field a Lookup field. You should avoid doing that at all cost. Here's why:
http://access.mvps.org/access/lookupfields.htm

So to note/do:
1. Open the table in design view > click on a field > click the Lookup tab in the property sheet and change it from List/Combo box to Textbox.
2. Once you've done that, remember to match the data type to the true data type of the field. In your case, after removing the Lookup you had the field as Text whereby it's supposed to be Number.
3. Open your query in design view > add the other table > link it to the existing table via ID > drag and drop the Quarter field from your other table to the grid.

 

[TomBP]

That would solve my problem but create another one

If making use of a text box within table the user can no longer select a quarter from the list.

 

[vbaInet]

The user should not be using a table to manipulate data. You use forms.

 

[TomBP]

Forms it is

Thanks for your assistance.

 

[vbaInet]

No problem!