Re: pivot table

I apologize for the name of the query (as it started out as a Pivot Table
then I tried the crosstab)

[PivotTable Update] is a select query combining the [Post-Off Table] to the
[Items Table] using the item # as key (Items table)
and [item #] combined with the [start date] as key [Post Off Table]. There
are several Post Off’s for each item #.

The crosstab query gives me the structure that I need for user input, but
does not let me type into the cells…
-------------
This is the SQL:
TRANSFORM First([PivotTable Update].[Post Off Price]) AS [FirstOfPost Off
Price]
SELECT [PivotTable Update].SUPSUBFL, [PivotTable Update].[Item #],
[PivotTable Update].[Item Description], First([PivotTable Update].[Post Off
Price]) AS [Total Of Post Off Price]
FROM [PivotTable Update]
GROUP BY [PivotTable Update].SUPSUBFL, [PivotTable Update].[Item #],
[PivotTable Update].[Item Description]
PIVOT Format([Post Off Start Date],"Short Date");
---------------------
SUPSUBFL = combination of three fields for grouping purposes (for updating
groups of similar data – with similar structures)

But….

When turned into a make table…gives me these fields

Item # Item Description Expr1003 FirstOfPost Off Price Total Of Post
Off Price
172173 Same description for all 1/2/2005 72 72
172173 Same description for all 1/30/2005 72 72
172173 Same description for all 10/2/2005 40.08 40.08
172173 Same description for all 10/30/2005 40.08 40.08
. . .
. . .
. . .
When I send it out to Excel it keeps the structure that I need, as follows:

* Date field names continue (as below) across the table…

SUPSUBFL | Item # | Item Description | Total Of Post Off | 1/13/2005 |
1/13/2005 | 1/16/2005 | 1/2/2005 | 1/23/2005 | 1/25/2005 | 1/30/2005 |
1/7/2005 | 1/9/2005 | 10/2/2005…….

Listed under SUPSUBFL

AIG Wine & Spirits/Napa-Saki/100
AIG Wine & Spirits/Napa-Saki/100
Allied Domecq/Azure Bay/90
Allied Domecq/Azure Bay/90
Allied Domecq/Azure Bay/90
Allied Domecq/Balbi/75.6
Allied Domecq/Balbi/75.6
..
..
..
Listed under Item #
Test 100
Test 101
172173
172181
172199
172207
..
..
..
Listed under Item Description
3 Graces
3 Graces
Cabernet California
Chardonnay, Austrailian
Souvignon Blanc/Semillon, New Zealand
Shiraz Austrailian
Malbec
Syrah
..
..
..
The [Total Of Post Off] field is not used but the crosstab puts it there

*the table continues horizontally across in the following order: 10/27/2005
10/30/2005 10/31/2005 11/27/2005 2/10/2005 2/13/2005 2/27/2005 2/6/2005
3/1/2005 3/13/2005 3/17/2005
3/20/2005 3/27/2005 4/1/2005 4/10/2005 4/13/2005 4/15/2005 4/17/2005
4/19/2005 4/26/2005 4/3/2005 5/1/2005 5/12/2005 5/15/2005 5/2/2005 5/22/2005
5/24/2005 5/28/2005
5/8/2005 6/1/2005 6/12/2005 6/14/2005 6/19/2005 6/26/2005 6/3/2005 6/4/2005
6/5/2005 7/1/2005 7/17/2005 7/24/2005 7/3/2005 7/31/2005 8/1/2005 8/14/2005
8/21/2005 8/22/2005
8/31/2005 9/1/2005 9/3/2005 9/4/2005
(This order is corrected in the new table (which is cleared and re-populated
each time)

Listed under SUPSUBFL (these are the concatenated fields [Supplier] [Brand]
[Frontline Price]

AIG Wine & Spirits/Napa-Saki/100
AIG Wine & Spirits/Napa-Saki/100
Allied Domecq/Azure Bay/90
Allied Domecq/Azure Bay/90
Allied Domecq/Azure Bay/90
Allied Domecq/Balbi/75.6
Allied Domecq/Balbi/75.6

Items table - contains all data relating to all items
Post Off Table (original table) – contains the item #, PO Start date, PO End
date, PO Amt, Notes, initial date
The New Table – is structured as above with the actual dates used as the
field names now
The update queries update each of these individual date fields to the post
off table by matching the date to the PO date
Since it is matching the date…if I type something in the blank date field in
the new table it does not update to the PO Table,
Whereas, if I type over an amount…it does.

Can anyone tell me how to get the blank fields (filled-in) to update to the
Post Off Table as well?


"AccessAddict" wrote:

> Duane...
> Thank you for your response to my dilema...it might work (I haven't given it
> the focus that it deserves yet) but I also need to bring in the data that is
> already there.
>
> I apologize for my delay in responding to your response, I was in-depth in
> formulating my own solution, as I read yours I wasn't sure if mine would work
> or not...but I was not ready to investigate yours yet...since the user needs
> to see the data in the field as it is currently in the table (before changing
> or adding data), that said:
>
> Here is what I did...
>
> I sent the crosstab query out to Excel and brought it back in as a new table
> (since you cannot turn a crosstab query into a make table). I, then, run an
> update macro updating my original table from this new table.
>
> This seems to work (partially)...the user sees the data in groups of records
> (horizontally) and (if there is data) can change the amounts in the
> "crosstab" new table which is then updated to the original table...however,
> the word "change" tells it all...
> I can change an amount fine...and it updates to the original table, but in
> the cells that are blank in the "crosstab" new table, I cannot add an amount.
> This is a problem, as these amounts move around from month to month and
> amount to amount. As it stands, only amounts that show can be changed. Is
> there something that I am missing here? I do know that now the date fields
> are no longer dates but field names which means that a date is not getting
> into the original table as a "start date" because now it is the name of the
> field. Do you have an idea on this? I removed the key (which combined [item
> #] with [start date] as the key), thinking that, of course, it wouldn't
> work...but now thinking...it is not putting a date in the original table.
> For the crosstab query, I have already concatenated three fields together to
> get the group and the user needs to see the item # and a description of the
> product...crosstabs only let you enter three row fields...and one column
> field and one value field. I also need "start date" to be updatable...I
> guess as a value.
>
> I don't know if I have confused you more or what...let me know...and I will
> try to simplify. Thanks again.
>
> "Duane Hookom" wrote:
>
> > I am not aware of any method of allowing the editing of records in a pivot
> > or crosstab query. You can possibly create an unbound form with lots of text
> > boxes in a grid format that you populate with code. After the user updates
> > the text boxes, you can run code to update the values back to your tables.
> >
> > --
> > Duane Hookom
> > MS Access MVP
> >
> >
> > "AccessAddict" <AccessAddict@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:D46C4FFA-BACA-435E-ADD3-0B511C935126@xxxxxxxxxxxxxxxx
> > >I can create a pivot table to show data from a query in the view that the
> > > user would like to enter the data into. However, the pivot table will not
> > > let me enter or change the data...the underlying query will let you enter
> > > or
> > > change the data (and if I change to data*** view I can add/change the
> > > data.
> > > The user needs to see and change or add data in the pivot table view. Is
> > > there a way to accomplish this?
> >
> >
> >
.