CrossTab Query - Display Is null Fields

G

Guest

I have a CrossTab Query that gives the current price of an SKU from a market
price survey. If the SKU hasn't been surveyed within the past 90 days the
zone price is ignored. I have one zone (76) that is surveyed very 6 months.
When this zone drops out of the CrossTab resolutes, it affects the MakeTable
Query by not recognizing the zone "76" as a valid field name. I would like
to be able to still provide the zone "76" with Null even if the CrossTab
Query doesn’t pick up any data. Also, still give the resolutes for zone "76"
when there is current data.

SURVEY TABLE
SKU ZONE RETAIL CHECKDATE
437640 74 1 / $10.99 14-May
437640 75 1 / $11.99 14-May
437640 76 1 / $12.99 12-Jan <== older than 90 days
438838 74 1 / $10.99 14-May
438838 75 1 / $11.99 14-May
438838 76 1 / $12.99 12-Jan <== older than 90 days

CROSSTAB (Apr 1)
SKU 74 75 76
437640 $10.99 $11.99 $12.99
438838 $10.99 $11.99 $12.99

CROSSTAB (July 2)
SKU 74 75 <== Zone "76" older than 90 days
437640 $9.99 $9.99
438838 $9.99 $9.99
 
D

Duane Hookom

Enter all of the zones in the Column Headings property of the crosstab
query.
 
G

Guest

How do I add all of the zones to Column Heading "ZONE"? I have forty (40)
price zones, 34, 35, 38 ...... 83, 85, 86. Could I use an expression like
between 34 and 86? How & where?

CrossTab Query
Field: SKU ZONE PRICE SKU
Table: PRICECK PRICECK PRICECK PRICECK
Total: Group By Group By First Where
Crosstab: Row Hdg Col Hdg Value
Criteria: Is Not Null
 
D

Duane Hookom

You first need to find the Column Headings property of the crosstab query.
This is not the field/column in the design grid. You need to view the query
properties to find it. Once you find it enter:
Column Headings: 34, 35, 38 ...... 83, 85, 86
 
G

Guest

Thanks Duane, your suggestion works. However, is there a way to join this to
a table or update through an input form that is already maintained by the end
user. I want to get away from manual maintenance to the query every time a
zone is added.
 
D

Duane Hookom

You can use code to modify the SQL property of the saved crosstab query. I
assume you have a table with each unique zone. You would need to loop
through this table and build the list of zones. Then use this to update the
SQL property of your crosstab.
 
G

Guest

I do have a unique table called ZONE TABLE, this holds geographical info.
Can you help me with script?
Table = ZONE TABLE
Column fields: ZONE (34, 35, 38 ...... 83, 85, 86)
 
D

Duane Hookom

The is a generic Concatenation function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane which can
build the list of values.
Concatenate("SELECT [Zone] FROM [ZONE TABLE]")
This can be combined with something like:
Dim strSQL as String
strSQL = "TRANSFORM ......" & _
"SELECT ....." & _
"FROM .... " & _
"GROUP BY ..." & _
"PIVOT [ZONE] IN (" & _
Concatenate("SELECT [Zone] FROM [ZONE TABLE]") & ");"
CurrentDb.QueryDefs("qxtYourName").SQL = strSQL
 
G

Guest

Is it possible to have an Unbound ComboBox where the source record is a
CrossTab Query? I’m having trouble with a form. The form is for the user to
choose only their regional ZONE & get a report of only their columnized
regional pricing.

I can filter this manually within the CrossTab Query: WHERE ((([Competitors
- CLP].ZONE)="BILL")). Then I changed it for the form to: WHERE
((([Competitors - CLP].ZONE) Like [Forms]![ZONE REPORTS Form]![ZONE])).

The following SQL works without the form.
TRANSFORM First([Competitors - CLP].[PRICE TEXT]) AS [FirstOfPRICE TEXT]
SELECT [Competitors - CLP].ITEMNO
FROM [Competitors - CLP]
WHERE ((([Competitors - CLP].ZONE)="BILL"))
GROUP BY [Competitors - CLP].ITEMNO
PIVOT [Competitors - CLP].COMP;

This is similar to a simple query I have that works with the same type of
form & the following WHERE criteria: WHERE ((([SALES_DATA].VENDOR) Like
[Forms]![SALES_REPORTS]![Vendor]));

This form has a combo box where a user can choose the filtering criteria for
their individual reports. This form runs great with the following DoCmd.

Private Sub Command6_Click()
Me.Visible = False
DoCmd.OpenQuery "MT_PROD_SALES", acViewNormal, acEdit
DoCmd.Close acForm, "SALES_REPORTS"
DoCmd.RunMacro "SALES_Macro"

--
Thanks, Kevin


Duane Hookom said:
The is a generic Concatenation function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane which can
build the list of values.
Concatenate("SELECT [Zone] FROM [ZONE TABLE]")
This can be combined with something like:
Dim strSQL as String
strSQL = "TRANSFORM ......" & _
"SELECT ....." & _
"FROM .... " & _
"GROUP BY ..." & _
"PIVOT [ZONE] IN (" & _
Concatenate("SELECT [Zone] FROM [ZONE TABLE]") & ");"
CurrentDb.QueryDefs("qxtYourName").SQL = strSQL
Click to expand...
 
D

Duane Hookom

A combobox doesn't have a "source record", it does have "Row Source" but I
don't understand how that fits with the remainder of your question.

Are you having a problem using a control on a form to filter a crosstab
query? If so, you must enter the data type of all parameters in crosstab
queries. Select Query->Parameters and enter your parameters and data types.
[Forms]![ZONE REPORTS Form]![ZONE] Text

--
Duane Hookom
MS Access MVP

AFSSkier said:
Is it possible to have an Unbound ComboBox where the source record is a
CrossTab Query? I'm having trouble with a form. The form is for the user
to
choose only their regional ZONE & get a report of only their columnized
regional pricing.

I can filter this manually within the CrossTab Query: WHERE
((([Competitors
- CLP].ZONE)="BILL")). Then I changed it for the form to: WHERE
((([Competitors - CLP].ZONE) Like [Forms]![ZONE REPORTS Form]![ZONE])).

The following SQL works without the form.
TRANSFORM First([Competitors - CLP].[PRICE TEXT]) AS [FirstOfPRICE TEXT]
SELECT [Competitors - CLP].ITEMNO
FROM [Competitors - CLP]
WHERE ((([Competitors - CLP].ZONE)="BILL"))
GROUP BY [Competitors - CLP].ITEMNO
PIVOT [Competitors - CLP].COMP;

This is similar to a simple query I have that works with the same type of
form & the following WHERE criteria: WHERE ((([SALES_DATA].VENDOR) Like
[Forms]![SALES_REPORTS]![Vendor]));

This form has a combo box where a user can choose the filtering criteria
for
their individual reports. This form runs great with the following DoCmd.

Private Sub Command6_Click()
Me.Visible = False
DoCmd.OpenQuery "MT_PROD_SALES", acViewNormal, acEdit
DoCmd.Close acForm, "SALES_REPORTS"
DoCmd.RunMacro "SALES_Macro"

--
Thanks, Kevin


Duane Hookom said:
The is a generic Concatenation function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane which
can
build the list of values.
Concatenate("SELECT [Zone] FROM [ZONE TABLE]")
This can be combined with something like:
Dim strSQL as String
strSQL = "TRANSFORM ......" & _
"SELECT ....." & _
"FROM .... " & _
"GROUP BY ..." & _
"PIVOT [ZONE] IN (" & _
Concatenate("SELECT [Zone] FROM [ZONE TABLE]") & ");"
CurrentDb.QueryDefs("qxtYourName").SQL = strSQL


--
Duane Hookom
MS Access MVP


AFSSkier said:
I do have a unique table called ZONE TABLE, this holds geographical
info.
Can you help me with script?
Table = ZONE TABLE
Column fields: ZONE (34, 35, 38 ...... 83, 85, 86)
--
Thanks, Kevin


:

You can use code to modify the SQL property of the saved crosstab
query.
I
assume you have a table with each unique zone. You would need to loop
through this table and build the list of zones. Then use this to
update
the
SQL property of your crosstab.

--
Duane Hookom
MS Access MVP

Thanks Duane, your suggestion works. However, is there a way to
join
this
to
a table or update through an input form that is already maintained
by
the
end
user. I want to get away from manual maintenance to the query every
time
a
zone is added.
--
Thanks, Kevin


:

You first need to find the Column Headings property of the crosstab
query.
This is not the field/column in the design grid. You need to view
the
query
properties to find it. Once you find it enter:
Column Headings: 34, 35, 38 ...... 83, 85, 86

--
Duane Hookom
MS Access MVP

How do I add all of the zones to Column Heading "ZONE"? I have
forty
(40)
price zones, 34, 35, 38 ...... 83, 85, 86. Could I use an
expression
like
between 34 and 86? How & where?

CrossTab Query
Field: SKU ZONE PRICE SKU
Table: PRICECK PRICECK PRICECK PRICECK
Total: Group By Group By First Where
Crosstab: Row Hdg Col Hdg Value
Criteria: Is Not Null

--
Thanks, Kevin


:

Enter all of the zones in the Column Headings property of the
crosstab
query.
--
Duane Hookom
MS Access MVP

I have a CrossTab Query that gives the current price of an SKU
from a
market
price survey. If the SKU hasn't been surveyed within the past
90
days
the
zone price is ignored. I have one zone (76) that is surveyed
very 6
months.
When this zone drops out of the CrossTab resolutes, it affects
the
MakeTable
Query by not recognizing the zone "76" as a valid field name.
I
would
like
to be able to still provide the zone "76" with Null even if
the
CrossTab
Query doesn't pick up any data. Also, still give the resolutes
for
zone
"76"
when there is current data.

SURVEY TABLE
SKU ZONE RETAIL CHECKDATE
437640 74 1 / $10.99 14-May
437640 75 1 / $11.99 14-May
437640 76 1 / $12.99 12-Jan <== older than 90 days
438838 74 1 / $10.99 14-May
438838 75 1 / $11.99 14-May
438838 76 1 / $12.99 12-Jan <== older than 90 days

CROSSTAB (Apr 1)
SKU 74 75 76
437640 $10.99 $11.99 $12.99
438838 $10.99 $11.99 $12.99

CROSSTAB (July 2)
SKU 74 75 <== Zone "76" older than 90 days
437640 $9.99 $9.99
438838 $9.99 $9.99
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks, I was missing the parameters in the SQL of the CrossTab. I thought I
was missing something in the form commands. I works now!

PARAMETERS [Forms]![ZONE REPORTS Form]![ZONE] Text;
TRANSFORM First([Competitors - CLP].[PRICE TEXT]) AS [FirstOfPRICE TEXT]
SELECT [Competitors - CLP].ITEMNO
FROM [Competitors - CLP]
WHERE ((([Competitors - CLP].ZONE) Like [Forms]![ZONE REPORTS Form]![ZONE]))
GROUP BY [Competitors - CLP].ITEMNO
PIVOT [Competitors - CLP].COMP;

--
Thanks, Kevin


Duane Hookom said:
A combobox doesn't have a "source record", it does have "Row Source" but I
don't understand how that fits with the remainder of your question.

Are you having a problem using a control on a form to filter a crosstab
query? If so, you must enter the data type of all parameters in crosstab
queries. Select Query->Parameters and enter your parameters and data types.
[Forms]![ZONE REPORTS Form]![ZONE] Text

--
Duane Hookom
MS Access MVP

AFSSkier said:
Is it possible to have an Unbound ComboBox where the source record is a
CrossTab Query? I'm having trouble with a form. The form is for the user
to
choose only their regional ZONE & get a report of only their columnized
regional pricing.

I can filter this manually within the CrossTab Query: WHERE
((([Competitors
- CLP].ZONE)="BILL")). Then I changed it for the form to: WHERE
((([Competitors - CLP].ZONE) Like [Forms]![ZONE REPORTS Form]![ZONE])).

The following SQL works without the form.
TRANSFORM First([Competitors - CLP].[PRICE TEXT]) AS [FirstOfPRICE TEXT]
SELECT [Competitors - CLP].ITEMNO
FROM [Competitors - CLP]
WHERE ((([Competitors - CLP].ZONE)="BILL"))
GROUP BY [Competitors - CLP].ITEMNO
PIVOT [Competitors - CLP].COMP;

This is similar to a simple query I have that works with the same type of
form & the following WHERE criteria: WHERE ((([SALES_DATA].VENDOR) Like
[Forms]![SALES_REPORTS]![Vendor]));

This form has a combo box where a user can choose the filtering criteria
for
their individual reports. This form runs great with the following DoCmd.

Private Sub Command6_Click()
Me.Visible = False
DoCmd.OpenQuery "MT_PROD_SALES", acViewNormal, acEdit
DoCmd.Close acForm, "SALES_REPORTS"
DoCmd.RunMacro "SALES_Macro"

--
Thanks, Kevin


Duane Hookom said:
The is a generic Concatenation function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane which
can
build the list of values.
Concatenate("SELECT [Zone] FROM [ZONE TABLE]")
This can be combined with something like:
Dim strSQL as String
strSQL = "TRANSFORM ......" & _
"SELECT ....." & _
"FROM .... " & _
"GROUP BY ..." & _
"PIVOT [ZONE] IN (" & _
Concatenate("SELECT [Zone] FROM [ZONE TABLE]") & ");"
CurrentDb.QueryDefs("qxtYourName").SQL = strSQL


--
Duane Hookom
MS Access MVP


I do have a unique table called ZONE TABLE, this holds geographical
info.
Can you help me with script?
Table = ZONE TABLE
Column fields: ZONE (34, 35, 38 ...... 83, 85, 86)
--
Thanks, Kevin


:

You can use code to modify the SQL property of the saved crosstab
query.
I
assume you have a table with each unique zone. You would need to loop
through this table and build the list of zones. Then use this to
update
the
SQL property of your crosstab.

--
Duane Hookom
MS Access MVP

Thanks Duane, your suggestion works. However, is there a way to
join
this
to
a table or update through an input form that is already maintained
by
the
end
user. I want to get away from manual maintenance to the query every
time
a
zone is added.
--
Thanks, Kevin


:

You first need to find the Column Headings property of the crosstab
query.
This is not the field/column in the design grid. You need to view
the
query
properties to find it. Once you find it enter:
Column Headings: 34, 35, 38 ...... 83, 85, 86

--
Duane Hookom
MS Access MVP

How do I add all of the zones to Column Heading "ZONE"? I have
forty
(40)
price zones, 34, 35, 38 ...... 83, 85, 86. Could I use an
expression
like
between 34 and 86? How & where?

CrossTab Query
Field: SKU ZONE PRICE SKU
Table: PRICECK PRICECK PRICECK PRICECK
Total: Group By Group By First Where
Crosstab: Row Hdg Col Hdg Value
Criteria: Is Not Null

--
Thanks, Kevin


:

Enter all of the zones in the Column Headings property of the
crosstab
query.
--
Duane Hookom
MS Access MVP

I have a CrossTab Query that gives the current price of an SKU
from a
market
price survey. If the SKU hasn't been surveyed within the past
90
days
the
zone price is ignored. I have one zone (76) that is surveyed
very 6
months.
When this zone drops out of the CrossTab resolutes, it affects
the
MakeTable
Query by not recognizing the zone "76" as a valid field name.
I
would
like
to be able to still provide the zone "76" with Null even if
the
CrossTab
Query doesn't pick up any data. Also, still give the resolutes
for
zone
"76"
when there is current data.

SURVEY TABLE
SKU ZONE RETAIL CHECKDATE
437640 74 1 / $10.99 14-May
437640 75 1 / $11.99 14-May
437640 76 1 / $12.99 12-Jan <== older than 90 days
438838 74 1 / $10.99 14-May
438838 75 1 / $11.99 14-May
438838 76 1 / $12.99 12-Jan <== older than 90 days

CROSSTAB (Apr 1)
SKU 74 75 76
437640 $10.99 $11.99 $12.99
438838 $10.99 $11.99 $12.99

CROSSTAB (July 2)
SKU 74 75 <== Zone "76" older than 90 days
437640 $9.99 $9.99
438838 $9.99 $9.99
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Group by Range in Crosstab Query 4
Calculating totals on a Crosstab Query 34
Crosstab Query by Week with Dates as Headers 5
Is There A Problem With Crosstab Queries And Rounded Numbers? 3
Is There Anyway To Force Access 2000 to NOT skip null Row Values In A Crosstab Query 1
Crosstab query does not display all results 1
LOOKUP function 3
WCG Stats Thursday 17 October 2013 8

Top