Combining multiple queries (on a single table), so the results appear side by side?

[Mike]

I have a table with a list of states and which user is currently responsible
for them. I am trying to get the Users across the top and the States listed
below each one with no spaces so that it can be the source for a multicolumn
listbox. I can accomplish what I want using a value list and vba but I run
into the 2048 character limit for the rowsource so I was hoping a query
could be used instead.

I can make individual queries that get all the states for a single user
however if I add them together into one master query it looks like a
staircase::

User1 | User2
_________|_______
Alaska |
Washington |
|
| Arkansas
| Texas
|


A crosstab query comes very close but it leaves null values similar to the
above. I need them all pushed all the way to the top.


Here is what the table looks like:

Table:

State | User
________________

Alaska | User1
Arkansas | User2
Alabama | User3
California | User2
Texas | User2
Washington | User 1



Wanted Result:

User1 | User 2 | User 3
_______________________________

Alaska | Arkansas | Alabama
Washington | Texas |

 

[Ken Snell [MVP]]

What you want to set up strikes me as a strange thing for which to use a
listbox, unless you're just wanting to display data? And if it's just to
display data, this could be done by side-by-side subforms or subreports.

To create what you want, you'd need to create a table to hold the data the
way you wish (note that this table will be very denormalized!):

TempTable
PKey Autonumber primary key
User1
User2
User3
User4
User5
(etc.)

You then will need to use VBA code to fill the table. This code would have
to open a recordset that is based on TempTable (ordered by PKey Ascending),
and open another recordset that will return the records for User1. Loop
through the User1 recordset and write the data to the table. Then close the
User1 recordset and open a recordset for User2 data. Put the first table
recordset back to the first record, and repeat the process of writing the
data to the field for User2. Repeat for as many users as you have.

Then the listbox's RowSource should be this TempTable.

 

[Mike]

Yes it is kind of strange I suppose but I am just wanting to display the
data as you guessed.

I have successfully used a simple textbox to display it by moving through
the recordset but that is only a vertical list and would be a major headache
to try and make columnar. The side-by-side subforms would work but the
number of users could change and it seemed like the code could get really
messy to make that dynamic with placement on the form and all. I suppose I
could replace the textbox with a browser control and put them in a html
table as I iterate through the recordset. It just seems like every
solution I can think of is some kind of non standard hack job.

 

[Dale Fye]

Here is another idea.

1. Create a query that looks something like:

SELECT T1.User,
T1.State,
DCount("User","tbl_UserStates", "[User] = '" & [T1].[User] &
"' AND [State] <= '" & [T1].[State] & "'") AS UserRow
FROM tbl_UserStates AS T1;

Watch the word wrap.

2. Now create a crosstab query using the "UserRow" value as the rowheading,
User as the column heading, and the State column as your value column,
selecting the First option.

TRANSFORM First(qry_UserStates.State) AS FirstOfState
SELECT qry_UserStates.UserRow
FROM qry_UserStates
GROUP BY qry_UserStates.UserRow
PIVOT qry_UserStates.User;

I think that should give you what you are looking for. Use the second query
as your row source, then set the first column (UserRow) width to zero.

 

[Ken Snell [MVP]]

Creative!

--

Ken Snell
<MS ACCESS MVP>

Here is another idea.

1. Create a query that looks something like:

SELECT T1.User,
T1.State,
DCount("User","tbl_UserStates", "[User] = '" & [T1].[User] &
"' AND [State] <= '" & [T1].[State] & "'") AS UserRow
FROM tbl_UserStates AS T1;

Watch the word wrap.

2. Now create a crosstab query using the "UserRow" value as the rowheading,
User as the column heading, and the State column as your value column,
selecting the First option.

TRANSFORM First(qry_UserStates.State) AS FirstOfState
SELECT qry_UserStates.UserRow
FROM qry_UserStates
GROUP BY qry_UserStates.UserRow
PIVOT qry_UserStates.User;

I think that should give you what you are looking for. Use the second query
as your row source, then set the first column (UserRow) width to zero.


--
HTH

Dale

I have a table with a list of states and which user is currently responsible
for them. I am trying to get the Users across the top and the States listed
below each one with no spaces so that it can be the source for a multicolumn
listbox. I can accomplish what I want using a value list and vba but I run
into the 2048 character limit for the rowsource so I was hoping a query
could be used instead.

I can make individual queries that get all the states for a single user
however if I add them together into one master query it looks like a
staircase::

User1 | User2
_________|_______
Alaska |
Washington |
|
| Arkansas
| Texas
|


A crosstab query comes very close but it leaves null values similar to the
above. I need them all pushed all the way to the top.


Here is what the table looks like:

Table:

State | User
________________

Alaska | User1
Arkansas | User2
Alabama | User3
California | User2
Texas | User2
Washington | User 1



Wanted Result:

User1 | User 2 | User 3
_______________________________

Alaska | Arkansas | Alabama
Washington | Texas |

 

[Mike]

Thanks for the tips, I couldn't seem to get this to work right though. It
looks pretty much like a regular crosstab does? Maybe I didn't set it up
right.

I think the callback function for the listbox is probably what I am going to
have to work with and just try and do it all in code.

Here is another idea.

1. Create a query that looks something like:

SELECT T1.User,
T1.State,
DCount("User","tbl_UserStates", "[User] = '" & [T1].[User] &
"' AND [State] <= '" & [T1].[State] & "'") AS UserRow
FROM tbl_UserStates AS T1;

Watch the word wrap.

2. Now create a crosstab query using the "UserRow" value as the rowheading,
User as the column heading, and the State column as your value column,
selecting the First option.

TRANSFORM First(qry_UserStates.State) AS FirstOfState
SELECT qry_UserStates.UserRow
FROM qry_UserStates
GROUP BY qry_UserStates.UserRow
PIVOT qry_UserStates.User;

I think that should give you what you are looking for. Use the second query
as your row source, then set the first column (UserRow) width to zero.


--
HTH

Dale

I have a table with a list of states and which user is currently responsible
for them. I am trying to get the Users across the top and the States listed
below each one with no spaces so that it can be the source for a multicolumn
listbox. I can accomplish what I want using a value list and vba but I run
into the 2048 character limit for the rowsource so I was hoping a query
could be used instead.

I can make individual queries that get all the states for a single user
however if I add them together into one master query it looks like a
staircase::

User1 | User2
_________|_______
Alaska |
Washington |
|
| Arkansas
| Texas
|


A crosstab query comes very close but it leaves null values similar to the
above. I need them all pushed all the way to the top.


Here is what the table looks like:

Table:

State | User
________________

Alaska | User1
Arkansas | User2
Alabama | User3
California | User2
Texas | User2
Washington | User 1



Wanted Result:

User1 | User 2 | User 3
_______________________________

Alaska | Arkansas | Alabama
Washington | Texas |

 

[Dale Fye]

Mike,

I built a table that looks just like yours (at least what you described
below), and then built these two queries. They came out looking precisely
the way you asked for it. If you would like, post your code (SQL) and I
will take a look at it.

Dale

Thanks for the tips, I couldn't seem to get this to work right though. It
looks pretty much like a regular crosstab does? Maybe I didn't set it up
right.

I think the callback function for the listbox is probably what I am going to
have to work with and just try and do it all in code.

Here is another idea.

1. Create a query that looks something like:

SELECT T1.User,
T1.State,
DCount("User","tbl_UserStates", "[User] = '" &

[T1].[User]

&
"' AND [State] <= '" & [T1].[State] & "'") AS UserRow
FROM tbl_UserStates AS T1;

Watch the word wrap.

2. Now create a crosstab query using the "UserRow" value as the rowheading,
User as the column heading, and the State column as your value column,
selecting the First option.

TRANSFORM First(qry_UserStates.State) AS FirstOfState
SELECT qry_UserStates.UserRow
FROM qry_UserStates
GROUP BY qry_UserStates.UserRow
PIVOT qry_UserStates.User;

I think that should give you what you are looking for. Use the second query
as your row source, then set the first column (UserRow) width to zero.


--
HTH

Dale

I have a table with a list of states and which user is currently responsible
for them. I am trying to get the Users across the top and the States listed
below each one with no spaces so that it can be the source for a multicolumn
listbox. I can accomplish what I want using a value list and vba but

I
run

into the 2048 character limit for the rowsource so I was hoping a query
could be used instead.

I can make individual queries that get all the states for a single user
however if I add them together into one master query it looks like a
staircase::

User1 | User2
_________|_______
Alaska |
Washington |
|
| Arkansas
| Texas
|


A crosstab query comes very close but it leaves null values similar to the
above. I need them all pushed all the way to the top.


Here is what the table looks like:

Table:

State | User
________________

Alaska | User1
Arkansas | User2
Alabama | User3
California | User2
Texas | User2
Washington | User 1



Wanted Result:

User1 | User 2 | User 3
_______________________________

Alaska | Arkansas | Alabama
Washington | Texas |

 

[Mike]

Dale,

I figured out what was causing my problem. Your code works perfect up until
it hits 10+ records. Then it sorts like this:

1
1
1
10
10
11
11
12
2
3
4
5

Which makes gaps in the 2nd query. It is sorting it as text instead of a
number. I solved it by enclosing the whole DCount expression with a Cint()
to make sure it sorted as an Integer. Works great now, Thanks for the
excellent help and very creative solution! If you would like maybe I can
post my MDB somewhere when I finish this little project and you can see how
it is being utilized.

Mike,

I built a table that looks just like yours (at least what you described
below), and then built these two queries. They came out looking precisely
the way you asked for it. If you would like, post your code (SQL) and I
will take a look at it.

Dale

Thanks for the tips, I couldn't seem to get this to work right though. It
looks pretty much like a regular crosstab does? Maybe I didn't set it up
right.

I think the callback function for the listbox is probably what I am

going

to
have to work with and just try and do it all in code.

Here is another idea.

1. Create a query that looks something like:

SELECT T1.User,
T1.State,
DCount("User","tbl_UserStates", "[User] = '" &

[T1].[User]

&
"' AND [State] <= '" & [T1].[State] & "'") AS UserRow
FROM tbl_UserStates AS T1;

Watch the word wrap.

2. Now create a crosstab query using the "UserRow" value as the rowheading,
User as the column heading, and the State column as your value column,
selecting the First option.

TRANSFORM First(qry_UserStates.State) AS FirstOfState
SELECT qry_UserStates.UserRow
FROM qry_UserStates
GROUP BY qry_UserStates.UserRow
PIVOT qry_UserStates.User;

I think that should give you what you are looking for. Use the second query
as your row source, then set the first column (UserRow) width to zero.


--
HTH

Dale


I have a table with a list of states and which user is currently
responsible
for them. I am trying to get the Users across the top and the States
listed
below each one with no spaces so that it can be the source for a
multicolumn
listbox. I can accomplish what I want using a value list and vba

but

I to
the