listbox

[gdanalakshmi]

I have a query that produces only row output at any given time.
The outout is
Q1 Q2 Q3 Q4
1 2 3 4

I want to fill the output of the query in a listbox. THe listbox contents should be like

Loop Time EstimatedTime Difference(Time - Estimated Time)

Q1 1 2 -1

Q2 2 3 -1

Q3 3 4 -1

Q4 4 4 0


The Time field is generated by the query and Estimated time are all contants and Difference is caluculated dynamically.


How do I implement the above? Should I use the sql query in VBA and manipulate or is there anyither better way ?

 

[Jack Cowley]

It sounds like you need to normalize the table that holds Q1, Q2, etc. You should have a record for each Q item, not 4 fields in a single record. If you normalize your table you will be able to fill your list box directly from the table, if you like.

I hope I correctly understood your question...

Jack

 

[gdanalakshmi]

I dont see a need for normalization. The Q1,Q2,Q3,Q4 correspond to one request.
It is just the way of displaying has to be different.

 

[Jon K]

Assuming the estimated times are contained in a table tblEstimatedTime as follows:-
QNum EstimatedTime
Q1 2
Q2 3
Q3 4
Q4 4

try this Union Query which is based on your one-row query (type/paste in the SQL View of a new query, using the correct name or your query):-

SELECT "Q1" as QNum, [Q1] as [Time], val(DLookup("EstimatedTime", "tblEstimatedTime", "QNum='Q1'")) as EstimatedTime, [Time]-EstimatedTime as Difference FROM [yourQueryName]
UNION
SELECT "Q2" as QNum, [Q2] as [Time], val(DLookup("EstimatedTime", "tblEstimatedTime", "QNum='Q2'")) as EstimatedTime, [Time]-EstimatedTime as Difference FROM [yourQueryName]
UNION
SELECT "Q3" as QNum, [Q3] as [Time], val(DLookup("EstimatedTime", "tblEstimatedTime", "QNum='Q3'")) as EstimatedTime, [Time]-EstimatedTime as Difference FROM [yourQueryName]
UNION
SELECT "Q4" as QNum, [Q4] as [Time], val(DLookup("EstimatedTime", "tblEstimatedTime", "QNum='Q4'")) as EstimatedTime, [Time]-EstimatedTime as Difference FROM [yourQueryName]


Hope it helps.