Is it possible to loop through a Pivot tables fields

[Rasho]

The following code is repeated for 31 days.
'fldCurr.Name = Date continues on for current date + (ie Date+1, Date+2)
'Me.Crew_1.ControlSource = strFieldName continues on (Me.Crew_1, Me.Crew_2)

The code below works fine for each day though I would like to know if it
is possible to loop through a Pivot tables fields using a For Each type loop
or
For i = 1 to Pivot table fields.count type loop? As the below code when
repeated 31 times slows the program down.

Sub C1()
On Error GoTo errControl

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim fldCurr As DAO.Field
Dim strFieldName As String

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("qry_BACWhite_register")

For Each fldCurr In qdfCurr.Fields
If fldCurr.Name = Date Then
strFieldName = fldCurr.Name
Exit For
End If
Next
If fldCurr.Name <> "" Then
Me.Crew_1.ControlSource = strFieldName
End If

errControl:
If Err.Number = 91 Then
Me.Crew_1.ControlSource = ""
End If
Resume Next

End Sub

 

[Stefan Hoffmann]

hi,

The code below works fine for each day though I would like to know if it
is possible to loop through a Pivot tables fields using a For Each type loop

Yes, it's possible.

For i = 1 to Pivot table fields.count type loop? As the below code when
repeated 31 times slows the program down.

There should be no signicifcant difference between these two loops.

The following code is repeated for 31 days.
'fldCurr.Name = Date continues on for current date + (ie Date+1, Date+2)
'Me.Crew_1.ControlSource = strFieldName continues on (Me.Crew_1, Me.Crew_2)

Here you cen do something: You can use fixed field names in your pivot
query, thus you only need to bind your controls once in the design view.


mfG
--> stefan <--

 

[Rasho]

Hi Stefan,
The date column auto changes from the date the program is opened, and the
crew allocation is based on the date using visual basic code
(Me.Crew_1.ControlSource = Date).

The program is based on the same format as a Gantt chart though collecting
data from other sources within the database. The crew that is shown along the
chart is related to the date.

As the fields are constantly changing from day to day, and the field name is
related to each day for the next 31 days I am unable to allocate a fixed
field name.

Cheers,

Wayne

 

[Stefan Hoffmann]

hi Rasho,

As the fields are constantly changing from day to day, and the field name is
related to each day for the next 31 days I am unable to allocate a fixed
field name.

Create a pivot query and assign fixed column names.

Take a look at the SQL statement. You can rewrite your pivot such that
you can use Day1 - Day31 as constant field names, while your content
still changes day for day.


mfG
--> stefan <--

 

[Rasho]

Hi Stefan,
When it comes to SQL statements I am a complete dummy. Can you please guide
me. The following is the SQL statement for the pivot table:

SELECT tbl_quote_register.*, tbl_contractors.Contractor, [Contractor] & " "
& [CashSaleCustomer] AS ContractorName, qry_ForemansJobRegister_Crosstab.*
FROM (tbl_contractors INNER JOIN (tbl_ap_codes RIGHT JOIN tbl_quote_register
ON tbl_ap_codes.AP_ItemNumber = tbl_quote_register.AP_ItemNumber1) ON
tbl_contractors.AccountNumber = tbl_quote_register.AccountNumber) INNER JOIN
qry_ForemansJobRegister_Crosstab ON tbl_quote_register.AP_SINumber =
qry_ForemansJobRegister_Crosstab.SalesNumber_1
WHERE (((tbl_contractors.Contractor)>"1") AND ((tbl_quote_register.Type)=2)
AND ((tbl_quote_register.Complete)=0))
ORDER BY tbl_quote_register.Type DESC , [Contractor] & " " &
[CashSaleCustomer];

Cheers,

Wayne

 

[Rasho]

Sorry Stefan,
This is the pivot table that feeds to the previous SQL statement I sent.

TRANSFORM First(qry_ACprogram_date.CrewAbbrev) AS Expr1
SELECT qry_ACprogram_date.SalesNumber_1, qry_ACprogram_date.MixExPlant
FROM qry_ACprogram_date
GROUP BY qry_ACprogram_date.SalesNumber_1, qry_ACprogram_date.MixExPlant
PIVOT qry_ACprogram_date.Date;

Cheers,

Wayne

 

[Rasho]

Sorry Stefan,
This is the SQL for the pivot table that feeds to the previous SQL I posted:

TRANSFORM First(qry_ACprogram_date.CrewAbbrev) AS Expr1
SELECT qry_ACprogram_date.SalesNumber_1, qry_ACprogram_date.MixExPlant
FROM qry_ACprogram_date
GROUP BY qry_ACprogram_date.SalesNumber_1, qry_ACprogram_date.MixExPlant
PIVOT qry_ACprogram_date.Date;

Cheers,

Wayne

Hi Stefan,
When it comes to SQL statements I am a complete dummy. Can you please guide
me. The following is the SQL statement for the pivot table:

SELECT tbl_quote_register.*, tbl_contractors.Contractor, [Contractor] & " "
& [CashSaleCustomer] AS ContractorName, qry_ForemansJobRegister_Crosstab.*
FROM (tbl_contractors INNER JOIN (tbl_ap_codes RIGHT JOIN tbl_quote_register
ON tbl_ap_codes.AP_ItemNumber = tbl_quote_register.AP_ItemNumber1) ON
tbl_contractors.AccountNumber = tbl_quote_register.AccountNumber) INNER JOIN
qry_ForemansJobRegister_Crosstab ON tbl_quote_register.AP_SINumber =
qry_ForemansJobRegister_Crosstab.SalesNumber_1
WHERE (((tbl_contractors.Contractor)>"1") AND ((tbl_quote_register.Type)=2)
AND ((tbl_quote_register.Complete)=0))
ORDER BY tbl_quote_register.Type DESC , [Contractor] & " " &
[CashSaleCustomer];

Cheers,

Wayne

hi Rasho,

Create a pivot query and assign fixed column names.

Take a look at the SQL statement. You can rewrite your pivot such that
you can use Day1 - Day31 as constant field names, while your content
still changes day for day.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Wayne,

This is the pivot table that feeds to the previous SQL statement I sent.

TRANSFORM First(qry_ACprogram_date.CrewAbbrev) AS Expr1
SELECT qry_ACprogram_date.SalesNumber_1, qry_ACprogram_date.MixExPlant
FROM qry_ACprogram_date
GROUP BY qry_ACprogram_date.SalesNumber_1, qry_ACprogram_date.MixExPlant
PIVOT qry_ACprogram_date.Date;

Take a look at the Northwind database, there is good example.

The

PIVOT qry_ACprogram_date.Date

clause controls the building of your pivot. You can extend it by using
the IN clause:

PIVOT qry_ACprogram_date.Date IN ("Day1", "Day2", .., "Day31")

assuming that your pivot only displays an entire month.


mfG
--> stefan <--

 

[Rasho]

Stefan,
The pivot table does not produce dates for days that are not worked. The
original code that I posted has an error trapment that helped to leave each
day blank when there was no control source.

Is there a way for the SQL statement to ignore skiped dates.

Cheers,

Wayne

 

[Stefan Hoffmann]

hi Wayne,

Is there a way for the SQL statement to ignore skiped dates.

No, the mapping to the values in the IN clause is "dumb".

Can you describe what you are actually trying to do with fields and
controls? What is your desired result?

I'm not quite sure, if my answers lead you into the right direction.

mfG
--> stefan <--

 

[Rasho]

Stefan,
I have created a database program that provides information for crews and
plants. Part of this program requires a whiteboard similar to a Gantt chart
showing the contractor and location in a column on the left of the page,
dates and day of the week in a row along the top of the page, and the
designated crew corresponding to the contractor and the date.
The data collected is through a cross tab query with common links, the final
link that the cross tab is formed on is a select query that only provides
dates within a set period, and only if the crews are working in that period
i.e days allocated = 10; days working = 9. This means that one date is
missing in the cross tab query.

The original code I provided works fine though it makes the program
cumbersome as I have to repeat the code to suit each individual day of the
month. This is because when no date is allocated the error generated had to
be trapped to provide a null value.

I am playing around with what you have provided me with so far. If we can
make the crosstab query do the work it will eliminate the need for VBA code
in the form and reports that present the data.

The SQL statement you have shown is close to the solution. Is it possible to
place an If() function within the brackets so that if no date is allocated
the field returns a null value. The following is what I am thinking:
PIVOT qry_ACprogram_date.Date IN (If "Day1" <> Date Then "Day1" = "" End If
, If "Day2" <> Date + 1 Then "Day2" = "" End If .., If "Day31" <> Date + 30
Then "Day31" = "" End If)

Cheers,

Wayne