pivot table

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 datasheet 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?

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" wrote in message
news
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 datasheet 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?

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" wrote in message
news
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 datasheet 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?

You can use a crosstab as the source for a maketable query. I don't know the
SQL of your crosstab or the structure of your original table so I can't be
of much additional help.

--
Duane Hookom
MS Access MVP
--

"AccessAddict" wrote in message
...
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" wrote in message
news
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 datasheet 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?

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" wrote in message
news
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 datasheet 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?

Do you have any limits on the number of columns created in your crosstab?
I guess I would create an unbound form that code fills from a recordset.
When the user is done updating the unbound grid of text boxes, more code
would loop through the controls and either update or append values to your
original table.

--
Duane Hookom
MS Access MVP


"AccessAddict" wrote in message
news
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" wrote in
message
news 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 datasheet 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?

Duane...
As far as I know, I have no limit on the number of columns created in the
crosstab.
although, I do know the approximate limit to its use in this
application...of the twelve months the average additional odd dates would be
three additional per month...give or take a few. Some months will have more
than three others none. The way their calendar works 7/1/2005 is really a
June date and 7/31/2005 is really an August date. As it stands now
10/30/2005 & 10/31/2005 are the only two for November and 11/26/2005 is the
only date for December. These dates are dynamic in that all dates after the
current date are subject to the addition of another date before the year ends.
I am not familiar with "unbound" forms... I have always used forms bound to
queries or tables. I just tried to create an unbound form with text boxes as
you said...(of course, my text boxes are referencing tables EX: =[GROUP
Crosstab MAKE TABLE]![Item #]). However, the expressions come back with
#Name?... Should I be entering the code into the SQL view? Or am I using
the wrong syntax? Could you give me an example of what you are talking about?

I appreciate your help and just because I'm addicted doesn't mean that I
know everything...LOL I'm addicted to the learning of it...I'm a "can't stop
until I find a solution" type of addict..."where there's a will there's a way"

Thanks for your time Duane...

"Duane Hookom" wrote:

Do you have any limits on the number of columns created in your crosstab?
I guess I would create an unbound form that code fills from a recordset.
When the user is done updating the unbound grid of text boxes, more code
would loop through the controls and either update or append values to your
original table.

--
Duane Hookom
MS Access MVP


"AccessAddict" wrote in message
news
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" wrote in
message
news 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 datasheet 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?

This might be a bit complex but your question is complex. I created a form
in the Northwind database with 18 text boxes in a grid of 6 columns and 3
rows. The first column text boxes have the names of:
txtCust1 - txtCust3
The 2 - 6 text boxes of the first row have names like:
txtOrdDate1_1 - txtOrdDate1_5
The 2 - 6 text boxes of the second row have names like:
txtOrdDate2_1 - txtOrdDate2_5
The 2 - 6 text boxes of the third row have names like:
txtOrdDate3_1 - txtOrdDate3_5

I added a command button on the form that opens a recordset and places
customers and order dates into the text boxes. Code would need to be written
that would loop through the controls on the form and either update or append
records back to the table(s).

Private Sub cmdPullOrderDates_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim intCustomer As Integer
Dim strCustomer As String
Dim intOrder As Integer
strSQL = "SELECT CompanyName, OrderDate " & _
"FROM Customers INNER JOIN " & _
"Orders ON Customers.CustomerID = Orders.CustomerID " & _
"ORDER BY CompanyName, OrderDate"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
With rs
intCustomer = 0
Do Until .EOF Or intCustomer 2
strCustomer = .Fields("CompanyName")
intCustomer = intCustomer + 1
Me("txtCust" & intCustomer) = strCustomer
intOrder = 0
Do Until strCustomer .Fields("CompanyName") Or intOrder 4
intOrder = intOrder + 1
Me("txtOrdDate" & intCustomer & "_" & intOrder) =
..Fields("OrderDate")
.MoveNext
Loop
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
End Sub


--
Duane Hookom
MS Access MVP


"AccessAddict" wrote in message
...
Duane...
As far as I know, I have no limit on the number of columns created in the
crosstab.
although, I do know the approximate limit to its use in this
application...of the twelve months the average additional odd dates would
be
three additional per month...give or take a few. Some months will have
more
than three others none. The way their calendar works 7/1/2005 is really a
June date and 7/31/2005 is really an August date. As it stands now
10/30/2005 & 10/31/2005 are the only two for November and 11/26/2005 is
the
only date for December. These dates are dynamic in that all dates after
the
current date are subject to the addition of another date before the year
ends.
I am not familiar with "unbound" forms... I have always used forms bound
to
queries or tables. I just tried to create an unbound form with text boxes
as
you said...(of course, my text boxes are referencing tables EX: =[GROUP
Crosstab MAKE TABLE]![Item #]). However, the expressions come back with
#Name?... Should I be entering the code into the SQL view? Or am I using
the wrong syntax? Could you give me an example of what you are talking
about?

I appreciate your help and just because I'm addicted doesn't mean that I
know everything...LOL I'm addicted to the learning of it...I'm a "can't
stop
until I find a solution" type of addict..."where there's a will there's a
way"

Thanks for your time Duane...

"Duane Hookom" wrote:

Do you have any limits on the number of columns created in your crosstab?
I guess I would create an unbound form that code fills from a recordset.
When the user is done updating the unbound grid of text boxes, more code
would loop through the controls and either update or append values to
your
original table.

--
Duane Hookom
MS Access MVP


"AccessAddict" wrote in message
news
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" wrote in
message
news 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 datasheet 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?

Duane...
I created a form with 20 rows and 36 columns - formatted as you suggested
except:
The name intCustomer is replaced with the name intITEM
strCustomer is replaced with the name strITEM
intOrder is replaced with the name intPO
txtCust is replaced with txtITEM
txtOrdDate is replaced with txtPOPrice

below is the code from my button...
----------
Private Sub Command3191_Click()
On Error GoTo Err_Command3191_Click


DoCmd.GoToRecord , , acFirst

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim intITEM As Integer
Dim strITEM As String
Dim intPO As Integer

' Select the [field],[field] from [table] joined to [table] inner join
[table].[key field]
' [table].[first-key field of a combo-field-key field]

strSQL = "SELECT SUPSUBFL, Post Off Price, FROM N ITEMS PRICING TABLE INNER
JOIN N Post Off Table ON N items Pricing Table.Item # = N POST OFF TABLE.Item
# ORDER BY SUPSUBFL, Post Off Price"

' SUPSUBFL
' POST OFF PRICE
' N ITEMS PRICING TABLE
' N POST OFF TABLE
' N ITEMS PRICING TABLE.Item #
' N POST OFF TABLE.Item #
' sort by SUPSUBFL, POST OFF PRICE

' The syntax won't let me past this point...
' I get the error:

'Run-Time error '3075'
'Syntax error (missing operator) in query expression 'Post Off Price'
'Plus, the text boxes that I created on the form with the names show #Name
in all of
'them.

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst
With rs
intITEM = 0
Do Until .EOF Or intITEM 4152
strITEM = .Fields(SUPSUBFL)
intITEM = intITEM + 1
Me("txtITEM" & intITEM) = strITEM
intPO = 0

'This code groups by SUPSUBFL

Do Until strITEM .Fields(SUPSUBFL) Or intPO 11952
intPO = intPO + 1
Me("txtPOPrice" & intITEM & "_" & intPO) = .Fields("Post Off
Price")
.MoveNext
Loop
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing

Exit_Command3191_Click:
Exit Sub

Err_Command3191_Click:
MsgBox Err.Description
Resume Exit_Command3191_Click

End Sub
---------------

Thanks again Duane for your time...


"Duane Hookom" wrote:

This might be a bit complex but your question is complex. I created a form
in the Northwind database with 18 text boxes in a grid of 6 columns and 3
rows. The first column text boxes have the names of:
txtCust1 - txtCust3
The 2 - 6 text boxes of the first row have names like:
txtOrdDate1_1 - txtOrdDate1_5
The 2 - 6 text boxes of the second row have names like:
txtOrdDate2_1 - txtOrdDate2_5
The 2 - 6 text boxes of the third row have names like:
txtOrdDate3_1 - txtOrdDate3_5

I added a command button on the form that opens a recordset and places
customers and order dates into the text boxes. Code would need to be written
that would loop through the controls on the form and either update or append
records back to the table(s).

Private Sub cmdPullOrderDates_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim intCustomer As Integer
Dim strCustomer As String
Dim intOrder As Integer
strSQL = "SELECT CompanyName, OrderDate " & _
"FROM Customers INNER JOIN " & _
"Orders ON Customers.CustomerID = Orders.CustomerID " & _
"ORDER BY CompanyName, OrderDate"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
With rs
intCustomer = 0
Do Until .EOF Or intCustomer 2
strCustomer = .Fields("CompanyName")
intCustomer = intCustomer + 1
Me("txtCust" & intCustomer) = strCustomer
intOrder = 0
Do Until strCustomer .Fields("CompanyName") Or intOrder 4
intOrder = intOrder + 1
Me("txtOrdDate" & intCustomer & "_" & intOrder) =
..Fields("OrderDate")
.MoveNext
Loop
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
End Sub


--
Duane Hookom
MS Access MVP


"AccessAddict" wrote in message
...
Duane...
As far as I know, I have no limit on the number of columns created in the
crosstab.
although, I do know the approximate limit to its use in this
application...of the twelve months the average additional odd dates would
be
three additional per month...give or take a few. Some months will have
more
than three others none. The way their calendar works 7/1/2005 is really a
June date and 7/31/2005 is really an August date. As it stands now
10/30/2005 & 10/31/2005 are the only two for November and 11/26/2005 is
the
only date for December. These dates are dynamic in that all dates after
the
current date are subject to the addition of another date before the year
ends.
I am not familiar with "unbound" forms... I have always used forms bound
to
queries or tables. I just tried to create an unbound form with text boxes
as
you said...(of course, my text boxes are referencing tables EX: =[GROUP
Crosstab MAKE TABLE]![Item #]). However, the expressions come back with
#Name?... Should I be entering the code into the SQL view? Or am I using
the wrong syntax? Could you give me an example of what you are talking
about?

I appreciate your help and just because I'm addicted doesn't mean that I
know everything...LOL I'm addicted to the learning of it...I'm a "can't
stop
until I find a solution" type of addict..."where there's a will there's a
way"

Thanks for your time Duane...

"Duane Hookom" wrote:

Do you have any limits on the number of columns created in your crosstab?
I guess I would create an unbound form that code fills from a recordset.
When the user is done updating the unbound grid of text boxes, more code
would loop through the controls and either update or append values to
your
original table.

--
Duane Hookom
MS Access MVP


"AccessAddict" wrote in message
news 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

You are being punished for allowing spaces and punctuation in your object
names.

Figure out how to fix this. If you can't find the fix, come back for an
answer.

--
Duane Hookom
MS Access MVP


"AccessAddict" wrote in message
news
Duane...
I created a form with 20 rows and 36 columns - formatted as you suggested
except:
The name intCustomer is replaced with the name intITEM
strCustomer is replaced with the name strITEM
intOrder is replaced with the name intPO
txtCust is replaced with txtITEM
txtOrdDate is replaced with txtPOPrice

below is the code from my button...
----------
Private Sub Command3191_Click()
On Error GoTo Err_Command3191_Click


DoCmd.GoToRecord , , acFirst

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim intITEM As Integer
Dim strITEM As String
Dim intPO As Integer

' Select the [field],[field] from [table] joined to [table] inner join
[table].[key field]
' [table].[first-key field of a combo-field-key field]

strSQL = "SELECT SUPSUBFL, Post Off Price, FROM N ITEMS PRICING TABLE
INNER
JOIN N Post Off Table ON N items Pricing Table.Item # = N POST OFF
TABLE.Item
# ORDER BY SUPSUBFL, Post Off Price"

' SUPSUBFL
' POST OFF PRICE
' N ITEMS PRICING TABLE
' N POST OFF TABLE
' N ITEMS PRICING TABLE.Item #
' N POST OFF TABLE.Item #
' sort by SUPSUBFL, POST OFF PRICE

' The syntax won't let me past this point...
' I get the error:

'Run-Time error '3075'
'Syntax error (missing operator) in query expression 'Post Off Price'
'Plus, the text boxes that I created on the form with the names show #Name
in all of
'them.

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst
With rs
intITEM = 0
Do Until .EOF Or intITEM 4152
strITEM = .Fields(SUPSUBFL)
intITEM = intITEM + 1
Me("txtITEM" & intITEM) = strITEM
intPO = 0

'This code groups by SUPSUBFL

Do Until strITEM .Fields(SUPSUBFL) Or intPO 11952
intPO = intPO + 1
Me("txtPOPrice" & intITEM & "_" & intPO) = .Fields("Post
Off
Price")
.MoveNext
Loop
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing

Exit_Command3191_Click:
Exit Sub

Err_Command3191_Click:
MsgBox Err.Description
Resume Exit_Command3191_Click

End Sub
---------------

Thanks again Duane for your time...


"Duane Hookom" wrote:

This might be a bit complex but your question is complex. I created a
form
in the Northwind database with 18 text boxes in a grid of 6 columns and 3
rows. The first column text boxes have the names of:
txtCust1 - txtCust3
The 2 - 6 text boxes of the first row have names like:
txtOrdDate1_1 - txtOrdDate1_5
The 2 - 6 text boxes of the second row have names like:
txtOrdDate2_1 - txtOrdDate2_5
The 2 - 6 text boxes of the third row have names like:
txtOrdDate3_1 - txtOrdDate3_5

I added a command button on the form that opens a recordset and places
customers and order dates into the text boxes. Code would need to be
written
that would loop through the controls on the form and either update or
append
records back to the table(s).

Private Sub cmdPullOrderDates_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim intCustomer As Integer
Dim strCustomer As String
Dim intOrder As Integer
strSQL = "SELECT CompanyName, OrderDate " & _
"FROM Customers INNER JOIN " & _
"Orders ON Customers.CustomerID = Orders.CustomerID " & _
"ORDER BY CompanyName, OrderDate"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
With rs
intCustomer = 0
Do Until .EOF Or intCustomer 2
strCustomer = .Fields("CompanyName")
intCustomer = intCustomer + 1
Me("txtCust" & intCustomer) = strCustomer
intOrder = 0
Do Until strCustomer .Fields("CompanyName") Or intOrder
4
intOrder = intOrder + 1
Me("txtOrdDate" & intCustomer & "_" & intOrder) =
..Fields("OrderDate")
.MoveNext
Loop
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
End Sub


--
Duane Hookom
MS Access MVP


"AccessAddict" wrote in message
...
Duane...
As far as I know, I have no limit on the number of columns created in
the
crosstab.
although, I do know the approximate limit to its use in this
application...of the twelve months the average additional odd dates
would
be
three additional per month...give or take a few. Some months will have
more
than three others none. The way their calendar works 7/1/2005 is
really a
June date and 7/31/2005 is really an August date. As it stands now
10/30/2005 & 10/31/2005 are the only two for November and 11/26/2005 is
the
only date for December. These dates are dynamic in that all dates
after
the
current date are subject to the addition of another date before the
year
ends.
I am not familiar with "unbound" forms... I have always used forms
bound
to
queries or tables. I just tried to create an unbound form with text
boxes
as
you said...(of course, my text boxes are referencing tables EX: =[GROUP
Crosstab MAKE TABLE]![Item #]). However, the expressions come back
with
#Name?... Should I be entering the code into the SQL view? Or am I
using
the wrong syntax? Could you give me an example of what you are talking
about?

I appreciate your help and just because I'm addicted doesn't mean that
I
know everything...LOL I'm addicted to the learning of it...I'm a
"can't
stop
until I find a solution" type of addict..."where there's a will there's
a
way"

Thanks for your time Duane...

"Duane Hookom" wrote:

Do you have any limits on the number of columns created in your
crosstab?
I guess I would create an unbound form that code fills from a
recordset.
When the user is done updating the unbound grid of text boxes, more
code
would loop through the controls and either update or append values to
your
original table.

--
Duane Hookom
MS Access MVP


"AccessAddict" wrote in
message
news 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