Long table into wide query

[Ivan Grozney]

I think I need a crosstab query but I don't understand how to create one that
outputs what I hope for…

My table is
TabID Number 1, 2, 3, 4 etc.
TagClass text A, or B, or C, or D
TagCaption text Christmas, CFE, etc.
TagConTip text Christmas, Call For Entries, etc.
Used Y/N True, False

Rows
1,C,Christmas,Christmas, True
2,C,CFE,Call For Entries, True

Query SELECT or???
.... WHERE tagClass = "C" AND Used = TRUE ORDER BY TABID

Hoped for Query Output
C1Cap C1Tip C2Cap C2Tip... up to 35
Christmas Christmas CFE Call for Entries...

tia

Vanya

 

[Clifford Bass]

Hi Vanya,

What is the end goal here? A report? If so, what is the planned
format? Or an Excel spreadsheet? Or something else?

Clifford Bass

 

[Ivan Grozney]

Clifford,

D'OH! I forgot that detail...

My end goal is to build a form with toggles on it. However, what the
toggles display and meaning changes. So for years they have had to go into
the code and make the changes. This way they can change the toggle captions
(and have a button to clear the old ones) via a form.

So I want to build a query with this information in length so I can
display it on the form as togs. The updateing is already done.

I can do it in a wide table but I hate to do it that way.

Thanks,

Vanya

 

[Clifford Bass]

Hi Vanya,

By toggles you mean what? Check boxes? Sorry, it remains rather
unclear what you are doing. Can you give a (partial) concrete example of
what it looks like now? You state it needs to change over time. How might
it change? What determines when it changes? It sounds like you are aiming
for a dymanic form of some sort. I am thinking you could design something
that dymanically adjusts in code to the desired configuration--perhaps in an
On Current event. Do you store the state of the toggles somewhere? If you
browse to older records does it need to adjust to prior configurations? Or
is it not linked to an underlying table?

Queryingly [how's that for a word ],

Clifford Bass

 

[Ivan Grozney]

Clifford,

Thanks for sticking with me on this, I hope this will explain it better.

They use toggle buttons instead of the check boxes but they should act the
same way (they like them better than check boxes). The query I am trying to
build is to set the toggle buttons caption and control tip.

Form:
Museum Patron Information (Name, Address, etc.)
Subform:
togC01 togC02 togC03 togC04
togC05
togC06 togC07 togC08 togC09
togC10
…
The query I am trying to build (or however I can build it, maybe a loop)
will show the caption and control tip for each of the toggle buttons.
togC01 Caption: Christmas Control Tip: Christmas
togC02 Caption: CFE Control Tip: Call for Entries
…

The code for the on load event of the subform is:

togC01.Caption = Me!C1Cap
togC01.ControlTipText = Me!C1Tip
togC02.Caption = Me!C2Cap
togC02.ControlTipText = Me!C2Tip
…

As for it changing, this is a museum mailing list. So they might have a
showing of Russian Lacquer boxes and have a toggle for that. Then maybe 150
folk/folkette have that toggle set. When they museum folks want to send out
an eamil, then a mailing routine will set up a spaminator to email those
patrons that have that toggle set.

After the showing is over, they don't need that toggle anymore. I have a
form where they can select the information about the toggle, clear it out of
all the patron records and change the name or just choose to hide it. I can
get it all to work with a wide table but I was hoping to figure out how to
make it work with a long table.

If you want, I can set up a demo version with fake name and addresses and
put it on our FTP site and you can take a look at it directly. It might be
late tomorrow before I can get that ready.

I hope this makes it clearer.

Thanks,

Vanya

Hi Vanya,

By toggles you mean what? Check boxes? Sorry, it remains rather
unclear what you are doing. Can you give a (partial) concrete example of
what it looks like now? You state it needs to change over time. How might
it change? What determines when it changes? It sounds like you are aiming
for a dymanic form of some sort. I am thinking you could design something
that dymanically adjusts in code to the desired configuration--perhaps in an
On Current event. Do you store the state of the toggles somewhere? If you
browse to older records does it need to adjust to prior configurations? Or
is it not linked to an underlying table?

Queryingly [how's that for a word ],

Clifford Bass

Clifford,

D'OH! I forgot that detail...

My end goal is to build a form with toggles on it. However, what the
toggles display and meaning changes. So for years they have had to go into
the code and make the changes. This way they can change the toggle captions
(and have a button to clear the old ones) via a form.

So I want to build a query with this information in length so I can
display it on the form as togs. The updateing is already done.

I can do it in a wide table but I hate to do it that way.

Thanks,

Vanya

 

[Clifford Bass]

Hi Vanya,

This gives me a good view of what is going on. I do think you can do
it without going to the wide query. I am going to let it percolate--probably
until Sunday--then try something. Then I will let you know if I need the
example db or have an possible solution. So enjoy your weekend!

Clifford Bass

 

[Clifford Bass]

Hi Vanya,

There are two ways (at least) that you can do this. The easier one is
to add a couple of columns to your table. I did not know it's name so I used
"tblMailSettings". Add a Yes/No column named "Selected" and a text column
named ColumnName (64 characters). Backing up slightly. I am presuming that
you have in your Patron's table a number of Yes/No columns for the kinds of
mailings they are interested in receiving (with column names such as
"Christmas" and "CFE"). Anyway, back to the mail settings table. I am
presuming that the Used column indicates if the particular toggle is to be
displayed, not that it means that is is being used in the current mailing.
Enter the actual column names from your patron table into the mail settings
table. Then design a new form that will use "Continuous Forms" for the
Default View. Set its Record Source to something like "select TabID,
TagClass, TagConTip, Selected from tblMailSettings where Used" and its Allow
Additions and Allow Deletions properties to No. On it you will display the
TagConTip in a text box that is Locked and is not a Tab Stop. You will also
have a toggle button for the Selected column. In the header or footer you
will add Print Mailings button (or whatever fits the needs). You might also
add a tag class combo box in the header (or footer), with which the user will
select the appropriate class. Its Row Source might be something like "select
distinct TagClass from tblMailSettings". When the user opens the form the
tag class will be initially set the the first one and he will see the options
listed down the form for that tag class, with the selected toggle buttons.
He will click those he wants to use for the mailing. Or he can change the
tag class and see settings appropriate to that. When he clicks the Print
Mailings button you will open up a recordset from the mail settings table and
use that to compose your where clause that you will pass to the report.

Here is the code for what I described:

-----------------------------------------------------------------------------

Private Sub cbTagClass_AfterUpdate()

Filter = "TagClass = """ & cbTagClass & """"
FilterOn = True

End Sub

Private Sub cmdPrintMailings_Click()

Dim rstWork As DAO.Recordset
Dim strCondition As String

If Dirty Then
' Save current, unsaved record
DoCmd.RunCommand acCmdSaveRecord
End If

Set rstWork = CurrentDb.OpenRecordset( _
"select ColumnName " & _
"from tblMailSettings " & _
"where TagClass = """ & cbTagClass & """ and Selected and Used " & _
"order by ColumnName", _
dbOpenSnapshot, dbReadOnly)
With rstWork
If .BOF And .EOF Then
MsgBox "Nothing selected."
.Close
Else
strCondition = vbNullString
Do While Not .EOF
If strCondition <> vbNullString Then
strCondition = strCondition & " and "
End If
strCondition = strCondition & ![ColumnName]
.MoveNext
Loop
.Close

DoCmd.OpenReport "rptMailingLabels", acViewPreview, , strCondition
End If
End With
Set rstWork = Nothing

End Sub

Private Sub Form_Open(Cancel As Integer)

' Set tag class combo box to first item
cbTagClass = cbTagClass.Column(0, 0)
cbTagClass_AfterUpdate

End Sub

-----------------------------------------------------------------------------

This method should work regardless of the number of settings needed.
No need to modify the form again. Neither on the fly or manually. Just add
new rows to the mail settings table. You will need to adapt it for your
actual situation, changing as needed where I made assumptions.

The other method, which would make your current form dynamic is more
involved. It involves setting up lots of toggles and labels (35 of each you
stated) and each time the form is opened, reading in the table and setting
the label and toggle information. But then the rest would be similar. If
you do desire to go that direction, let me know.

Clifford Bass

 

[Ivan Grozney]

Clifford,

Thank you. I'll give it a try here in a little bit and get back to
you.

Vanya

Hi Vanya,

There are two ways (at least) that you can do this. The easier one is
to add a couple of columns to your table. I did not know it's name so I used
"tblMailSettings". Add a Yes/No column named "Selected" and a text column
named ColumnName (64 characters). Backing up slightly. I am presuming that
you have in your Patron's table a number of Yes/No columns for the kinds of
mailings they are interested in receiving (with column names such as
"Christmas" and "CFE"). Anyway, back to the mail settings table. I am
presuming that the Used column indicates if the particular toggle is to be
displayed, not that it means that is is being used in the current mailing.
Enter the actual column names from your patron table into the mail settings
table. Then design a new form that will use "Continuous Forms" for the
Default View. Set its Record Source to something like "select TabID,
TagClass, TagConTip, Selected from tblMailSettings where Used" and its Allow
Additions and Allow Deletions properties to No. On it you will display the
TagConTip in a text box that is Locked and is not a Tab Stop. You will also
have a toggle button for the Selected column. In the header or footer you
will add Print Mailings button (or whatever fits the needs). You might also
add a tag class combo box in the header (or footer), with which the user will
select the appropriate class. Its Row Source might be something like "select
distinct TagClass from tblMailSettings". When the user opens the form the
tag class will be initially set the the first one and he will see the options
listed down the form for that tag class, with the selected toggle buttons.
He will click those he wants to use for the mailing. Or he can change the
tag class and see settings appropriate to that. When he clicks the Print
Mailings button you will open up a recordset from the mail settings table and
use that to compose your where clause that you will pass to the report.

Here is the code for what I described:

-----------------------------------------------------------------------------

Private Sub cbTagClass_AfterUpdate()

Filter = "TagClass = """ & cbTagClass & """"
FilterOn = True

End Sub

Private Sub cmdPrintMailings_Click()

Dim rstWork As DAO.Recordset
Dim strCondition As String

If Dirty Then
' Save current, unsaved record
DoCmd.RunCommand acCmdSaveRecord
End If

Set rstWork = CurrentDb.OpenRecordset( _
"select ColumnName " & _
"from tblMailSettings " & _
"where TagClass = """ & cbTagClass & """ and Selected and Used " & _
"order by ColumnName", _
dbOpenSnapshot, dbReadOnly)
With rstWork
If .BOF And .EOF Then
MsgBox "Nothing selected."
.Close
Else
strCondition = vbNullString
Do While Not .EOF
If strCondition <> vbNullString Then
strCondition = strCondition & " and "
End If
strCondition = strCondition & ![ColumnName]
.MoveNext
Loop
.Close

DoCmd.OpenReport "rptMailingLabels", acViewPreview, , strCondition
End If
End With
Set rstWork = Nothing

End Sub

Private Sub Form_Open(Cancel As Integer)

' Set tag class combo box to first item
cbTagClass = cbTagClass.Column(0, 0)
cbTagClass_AfterUpdate

End Sub

-----------------------------------------------------------------------------

This method should work regardless of the number of settings needed.
No need to modify the form again. Neither on the fly or manually. Just add
new rows to the mail settings table. You will need to adapt it for your
actual situation, changing as needed where I made assumptions.

The other method, which would make your current form dynamic is more
involved. It involves setting up lots of toggles and labels (35 of each you
stated) and each time the form is opened, reading in the table and setting
the label and toggle information. But then the rest would be similar. If
you do desire to go that direction, let me know.

Clifford Bass

Clifford,

Thanks for sticking with me on this, I hope this will explain it better.

They use toggle buttons instead of the check boxes but they should act the
same way (they like them better than check boxes). The query I am trying to
build is to set the toggle buttons caption and control tip.

Form:
Museum Patron Information (Name, Address, etc.)
Subform:
togC01 togC02 togC03 togC04
togC05
togC06 togC07 togC08 togC09
togC10
…
The query I am trying to build (or however I can build it, maybe a loop)
will show the caption and control tip for each of the toggle buttons.
togC01 Caption: Christmas Control Tip: Christmas
togC02 Caption: CFE Control Tip: Call for Entries
…

The code for the on load event of the subform is:

togC01.Caption = Me!C1Cap
togC01.ControlTipText = Me!C1Tip
togC02.Caption = Me!C2Cap
togC02.ControlTipText = Me!C2Tip
…

As for it changing, this is a museum mailing list. So they might have a
showing of Russian Lacquer boxes and have a toggle for that. Then maybe 150
folk/folkette have that toggle set. When they museum folks want to send out
an eamil, then a mailing routine will set up a spaminator to email those
patrons that have that toggle set.

After the showing is over, they don't need that toggle anymore. I have a
form where they can select the information about the toggle, clear it out of
all the patron records and change the name or just choose to hide it. I can
get it all to work with a wide table but I was hoping to figure out how to
make it work with a long table.

If you want, I can set up a demo version with fake name and addresses and
put it on our FTP site and you can take a look at it directly. It might be
late tomorrow before I can get that ready.

I hope this makes it clearer.

Thanks,

Vanya

 

[Clifford Bass]

HI Vanya,

You are welcome! One change to the code: it should use "or" instead of
"and":

strCondition = strCondition & " and "

should be:

strCondition = strCondition & " or "

Clifford Bass

 

[Ivan Grozney]

Clifford,

I think that I got it working. I was doing it last night but I had
some syntax errors 'cause I can type so very well....

Thank you so very much.

 

[Ivan Grozney]

Clifford,

By the way, if you have the "other" way to do it as you describe all
set, I would be interested to see it. If not, no worries 'cause this seems
to have solved my issue.

Thanks again for your efforts on my behalf.

Vanya

Hi Vanya,

There are two ways (at least) that you can do this. The easier one is
to add a couple of columns to your table. I did not know it's name so I used
"tblMailSettings". Add a Yes/No column named "Selected" and a text column
named ColumnName (64 characters). Backing up slightly. I am presuming that
you have in your Patron's table a number of Yes/No columns for the kinds of
mailings they are interested in receiving (with column names such as
"Christmas" and "CFE"). Anyway, back to the mail settings table. I am
presuming that the Used column indicates if the particular toggle is to be
displayed, not that it means that is is being used in the current mailing.
Enter the actual column names from your patron table into the mail settings
table. Then design a new form that will use "Continuous Forms" for the
Default View. Set its Record Source to something like "select TabID,
TagClass, TagConTip, Selected from tblMailSettings where Used" and its Allow
Additions and Allow Deletions properties to No. On it you will display the
TagConTip in a text box that is Locked and is not a Tab Stop. You will also
have a toggle button for the Selected column. In the header or footer you
will add Print Mailings button (or whatever fits the needs). You might also
add a tag class combo box in the header (or footer), with which the user will
select the appropriate class. Its Row Source might be something like "select
distinct TagClass from tblMailSettings". When the user opens the form the
tag class will be initially set the the first one and he will see the options
listed down the form for that tag class, with the selected toggle buttons.
He will click those he wants to use for the mailing. Or he can change the
tag class and see settings appropriate to that. When he clicks the Print
Mailings button you will open up a recordset from the mail settings table and
use that to compose your where clause that you will pass to the report.

Here is the code for what I described:

-----------------------------------------------------------------------------

Private Sub cbTagClass_AfterUpdate()

Filter = "TagClass = """ & cbTagClass & """"
FilterOn = True

End Sub

Private Sub cmdPrintMailings_Click()

Dim rstWork As DAO.Recordset
Dim strCondition As String

If Dirty Then
' Save current, unsaved record
DoCmd.RunCommand acCmdSaveRecord
End If

Set rstWork = CurrentDb.OpenRecordset( _
"select ColumnName " & _
"from tblMailSettings " & _
"where TagClass = """ & cbTagClass & """ and Selected and Used " & _
"order by ColumnName", _
dbOpenSnapshot, dbReadOnly)
With rstWork
If .BOF And .EOF Then
MsgBox "Nothing selected."
.Close
Else
strCondition = vbNullString
Do While Not .EOF
If strCondition <> vbNullString Then
strCondition = strCondition & " and "
End If
strCondition = strCondition & ![ColumnName]
.MoveNext
Loop
.Close

DoCmd.OpenReport "rptMailingLabels", acViewPreview, , strCondition
End If
End With
Set rstWork = Nothing

End Sub

Private Sub Form_Open(Cancel As Integer)

' Set tag class combo box to first item
cbTagClass = cbTagClass.Column(0, 0)
cbTagClass_AfterUpdate

End Sub

-----------------------------------------------------------------------------

This method should work regardless of the number of settings needed.
No need to modify the form again. Neither on the fly or manually. Just add
new rows to the mail settings table. You will need to adapt it for your
actual situation, changing as needed where I made assumptions.

The other method, which would make your current form dynamic is more
involved. It involves setting up lots of toggles and labels (35 of each you
stated) and each time the form is opened, reading in the table and setting
the label and toggle information. But then the rest would be similar. If
you do desire to go that direction, let me know.

Clifford Bass

Clifford,

Thanks for sticking with me on this, I hope this will explain it better.

They use toggle buttons instead of the check boxes but they should act the
same way (they like them better than check boxes). The query I am trying to
build is to set the toggle buttons caption and control tip.

Form:
Museum Patron Information (Name, Address, etc.)
Subform:
togC01 togC02 togC03 togC04
togC05
togC06 togC07 togC08 togC09
togC10
…
The query I am trying to build (or however I can build it, maybe a loop)
will show the caption and control tip for each of the toggle buttons.
togC01 Caption: Christmas Control Tip: Christmas
togC02 Caption: CFE Control Tip: Call for Entries
…

The code for the on load event of the subform is:

togC01.Caption = Me!C1Cap
togC01.ControlTipText = Me!C1Tip
togC02.Caption = Me!C2Cap
togC02.ControlTipText = Me!C2Tip
…

As for it changing, this is a museum mailing list. So they might have a
showing of Russian Lacquer boxes and have a toggle for that. Then maybe 150
folk/folkette have that toggle set. When they museum folks want to send out
an eamil, then a mailing routine will set up a spaminator to email those
patrons that have that toggle set.

After the showing is over, they don't need that toggle anymore. I have a
form where they can select the information about the toggle, clear it out of
all the patron records and change the name or just choose to hide it. I can
get it all to work with a wide table but I was hoping to figure out how to
make it work with a long table.

If you want, I can set up a demo version with fake name and addresses and
put it on our FTP site and you can take a look at it directly. It might be
late tomorrow before I can get that ready.

I hope this makes it clearer.

Thanks,

Vanya

 

[Clifford Bass]

Hi Vanya,

You are quite welcome! Glad to hear it is functional.

I only have the other way in my head, knowing generally how I would do
it. Create the form with all of the labels and toggles. Name them using a
pattern: lblCaption01, lblCaption02, ..., togSelector01, togSelector02, ....
Oh, I still would add a ColumnName column to the mail settings table (why
will be clear shortly). In the form's On Open event open up a recordset that
selects from the mail settings table the captions, control tips and column
names that apply for the current mailing (however that is determined). Use a
counter starting at 1. Go through the recordset setting the labels captions.
This can be done with the form's Controls collection. For example:

Controls("lblCaption" & Format(intCounter, "00")).Caption =
rstMailSettings![TagCaption]

Also, I set the Tag property of the toggle to the column name. If
there are less rows in the recordset than toggles, hide the labels and
toggles after that number. When the user is done selecting and wants to
produce the mailing I would go through the visible toggles using the Controls
collection. For those that are true, use the stored column name in the tag
to construct the where clause as in the code of the first method.

An advantage of the first method is that it stores the current
selections so if someone has to repeat the mailing, all they have to do is
open up the form again and do the mailing--no reselecting. The second method
does not store the settings, unless you do so manually at some point.

Clifford Bass