Help create a query using a row from one table as row headings, rows from another as column headings 1

[TheresAlwaysAWay]

I have one table which has a list of state abbreviations as a column. I have another table that lists insurance companies by name as a column. I want to combine them so that I have a new, editable query with state names as the row headings and Insurance Companies as the column headings so that I can enter a note. Supposing that Safeco insurance has some restrictions in California. At the point where the company and state intersect I'd like to type in a note which can then be displayed if someone is generating a new matching record.

I don't know how to take the column containing Ins. co. names and turn it into a row dynamically, so that any new additions would generate a new column automatically. A Crosstab query does that but its result is not editable and it requires a value field to calculate. I don't want to calculate anything. I just want to create the grid where I can type what I want.

All assistance will be appreciated.

 

[MajP]

There is no way to do this in a query. You would have to have some kind of temp table. Ideally MS would provide us a real grid control like you have in visual studio. This type of thing would be easy to do. Unfortunately there is no easy way to do this.

If you only want to have one note per state/company then create the state columns as fields and one other field for company name
tblGrid
CompanyName
Al
AZ
AK
...
WY


You could do a simple insert query to input all the company names and then when you add a new company to the database you run another insert query.

If you plan to have multiple notes then Your data needs to be stored in a normalized table, but you can create this "grid" table to use for input and display.
tblState_Company
stateID
CompanyID
Note
When you display your grid table you pull the data out of the above table and put it into your grid table. When you enter information in your grid table you push it into the data table.

 

[TheresAlwaysAWay]

I only need to have one field per state/company combo. That field could contain info for several different notes, but the actual trigger for the lookup would be simply matching the two parameters.

Can you give an example of the insert query? And where is the data stored? BTW, this is an SQL Server 2012 db. The table containing state abbreviations is tblStateAbbreviations and field is State, while the insurance company table is InsuranceCompany and name is ShortName. Ideally I'd like a display output based on the ShortName of a form. If I'm scrolling through a form based on the InsuranceCompany table and I click a button on that form to display the state notes, I'd like to display just that company if it's possible, rather than all of them.

Ideal display configuration
tblGrid
JustActiveFormCompanyName
AL
AZ
AK
...
WY

The lookup would obviously have access to all data, and a message would be triggered containing the field information if the corresponding grid intersection is not null as a new record is being generated. However, if one is working in the form containing the insurance company info it would be perfect if just that one company's field could be displayed.

Thanks for your advice.

 

[MajP]

I am a little confused on how you want to enter and display information. It sounds now that you really do not need a "grid" display. I would focus on storing you information in a table like

tblState_Company
stateID
CompanyID
Note

Information stored would then look like

GEICO AL Some Note
GEICO AK Some Note
...
State Farm AL Some Note
....
State Farm WY Some Note

Index StateID and CompanyID so you can only have one input for each company state combination

So you would store your information in that table, but unclear how you want to enter and display it.

 

[TheresAlwaysAWay]

OK, so I have a new table which is created with all the state abbreviations and all the company names. That answers where it is stored. I thought it might be possible to just generate that on the fly by bringing in the ShortName field from the InsuranceCompany table and generating new columns in the existing tblStateAbbreviation table, and using that for my data.

Obviously, the entire new table would have 52 rows (50 states + DC and PR), and let's say 20 columns for 20 different companies. A simple lookup would display the appropriate notes/warning message as a record is being created if it matches.

The query display question comes from looking ahead and considering the form on which all of the information relating to each individual insurance company is displayed. Each page obviously shows a different company. Ideally I'd like a button on that form which would display all 52 rows and only one column, the one corresponding to the company on screen at that moment, if that's possible to do. It would make it much easier to view/edit if only the info relevant to that company were displayed instead of all 20 companies, but it isn't something I've seen done before.

You're right. For storage of course I'd need a grid with all info, but for display purposes I'd just like to see the relevant column.

Also, I'm not sure if I understood whether it's possible to generate a new table column programmatically if a new company is added to the InsuranceCompany table. Please clarify if you can, as that was one of my main questions.

And thanks again for your help.

 

[MajP]

No. I was not suggesting you would have a grid to store the data. I was suggesting a grid for display purposes only. You never want to make a column heading to be data. You want to store the data in a normalized table, but you could display it for viewing and entry as you describe.

The data is stored in the table I described above
tblState_Company
stateID
CompanyID
Note

I would make an unbound form. With 52 Text boxes representing each state/territory. To make it easy in the tag property of each text box put the ID of the state. That will help with code. I would simply have a combobox at the top of this form with each company. You pick a company and it code would load the textboxes pulling from tblState_Company and sticking data in the create unbound textbox. These textboxes would not be editable. But to edit one of them, you could have an edit button next to each textbox or use the double click event of each textbox. You would pull the company ID from the combobox and the state ID from the textbox. Now you would pop open a form to edit the input for that state and company. This you could do with a bound form bound to the tblState_Company and you would pass to it in the open args the name of state and company. It will either open with an existing record for that combination or open to a new record for that state / company combination. You will reload your form once new data was added or deleted.

Bottom line. Do not create any table with columns that are either states or company names. That is data and these should be rows in any table.

 

[MajP]

Although doing this with an unbound form makes it less technically difficult it is more labor intensive and you would have to be precise with naming and tagging over 50 controls. You could do it with less labor using a table called tblDisplay. This would have 2 fields.

tblDisplay
StateLabel
Note

This table is for display purposes only and you do not add or delete from it. You simply clear out the notes field and then populate with the notes for each state.

Here is the code for picking a state and then updating the notes for display

Private Sub cmboState_AfterUpdate()
UpdateDisplay (Me.cmboState)
Me.Requery
End Sub

Public Sub UpdateDisplay(CompanyID As Integer)
  Dim rsData As DAO.Recordset
  Dim strSql As String
  Dim StateID As String
  Dim Note As String
  
  ClearDisplay
  strSql = "SELECT StateID, CompanyID, Note FROM tblState_Company_Notes "
  strSql = strSql & " WHERE CompanyID = " & CompanyID
  Set rsData = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  Do While Not rsData.EOF
    StateID = rsData!StateID
    Note = rsData!Note
    
    strSql = "Update tblDisplay SET [NOTE] = '" & Note & "' WHERE StateLabel = '" & StateID & "'"
    Debug.Print strSql
    CurrentDb.Execute strSql
    rsData.MoveNext
    
  Loop

End Sub

Public Sub ClearDisplay()
  Dim strSql As String
  strSql = "UPDATE tblDisplay SET tblDisplay.[Note] = Null"
  CurrentDb.Execute strSql
End Sub

The form would look like this

The next part to develop would be some event where you pick a state to update and it pops open a form to the given state and company. Then after updating the data you simply reload the display form.

 

[TheresAlwaysAWay]

This is awesome! Thanks so much.

Your example is exactly what I'm looking for. In fact, I could link the code to the ShortName field on the active form and just use a button on the insurance company display form to trigger it with Screen.ActiveForm!ShortName instead of a field called CompanyID. Then it would open with what I want to see with the same code from any company and not even need to use the dropdown.

I am confused about something, though. It's your table structure that has me puzzled. You provided the following example:

tblState_Company
stateID
CompanyID
Note

I understand this to mean a new table with three rows, StateID, CompanyID, and Note. That's how I read your instructions, but that doesn't seem logical at all, so I must be missing something.

I appreciate your help but I'd be even more grateful if you'd take time to explain this more fully. I must be misreading your table instructions somehow.

Thank you again.

 

[MajP]

The uploaded link is a working example.

I would think notes are stored in a table. The notes would have as a minimum a stateID and companyID.
example

[tt]
NoteID	StateID	CompanyID	Note
1	AK	1	Some note for GEICO AK …..
4	AL	2	Some Note for Prudential AL ….
6	AR	1	Some note for GEICO AR
3	MA	1	Note for GEICO MA
5	MD	2	Note for Prudential MD
2	WY	1	Note for GEICO WY
[/tt]

In your design you stated you would only have a single note for each state/company combination. If you would want to track multiple notes for a combination you could do that. You would have to modify the display code. Assume you stored 2 records

AK 1 Some note for GEICO, AK …..
AK 1 Second note for GEICO, AK
AK 3 Third note for GEICO, AK

You would have to modify the updateDisplay code to concatenate all of the notes before inserting into the display field.

 

[TheresAlwaysAWay]

Thank you so much! I didn't understand the table structure until I saw your sample db. I know you went through a lot of trouble to set this up for me.

If I could give you two stars I would!

 

[MajP]

Here is a little more detail about editing with a pop up. When you go to edit you need to check if a note exists. If it exists then open the popup form to the correct state and company. However, if it is a new note than you need to open the form in “add new” mode and also default the values to the correct state and company. You can pass the state and company in by using openargs. But opens arg is just a string so to pass 2 values you will have to use the split function. You would pass it in like
2;AZ

Private Sub cmdAddEdit_Click()
  Dim state As String
  Dim CompanyID As Integer
  Dim Args As String
  Dim strWhere As String
  If Not IsNull(Me.cmboCompany) Then
    CompanyID = Me.cmboCompany
    state = Me.StateLabel
    Args = CompanyID & ";" & state
    strWhere = "stateID = '" & state & "' AND CompanyID = " & CompanyID
    Debug.Print strWhere
    If Trim(Me.Note & " ") = "" Then
      'no note
       DoCmd.OpenForm "frmEnterNote", acNormal, , , acFormAdd, acDialog, Args
    Else
       'existing Note go to record
        DoCmd.OpenForm "frmEnterNote", acNormal, , strWhere, acFormEdit, acDialog
    End If
    UpdateDisplay (Me.cmboCompany)
  End If
End Sub

The code in the popup form would be

Private Sub Form_Load()
  Dim state As String
  Dim CompanyID As Integer
  If Me.NewRecord And Trim(Me.OpenArgs & " ") <> "" Then
    CompanyID = Split(Me.OpenArgs, ";")(0)
    state = Split(Me.OpenArgs, ";")(1)
    Me.StateID = state
    Me.CompanyID = CompanyID
  End If
End Sub

Here is an updated example

 

[Andrzejek]

MajP, shouldn't:

If Trim(Me.Note & [highlight #FCE94F]" "[/highlight]) = "" Then

be

If Trim(Me.Note & [highlight #FCE94F]""[/highlight]) = "" Then

???

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

It may not matter. I think what you show would work for all three cases, but I am sure what I show works for all cases. I saw it done that way a long time ago and assumed there was a reason for " " vice "".

The intent is to capture all possibilities in a field that appears to be empty
1. empty string
2. null value
3. space/s

trim(null & " ")
trim("" & " ")
trim(" " & " ")
all equal ""

Spaces and null values are the biggest issue. Getting an empty string cannot be done easily (or may not be possible) from the user interface and usually these result from an import or code.