How to get data from query using Expression Builder

[lws]

I hope I can explain this clearly.

I need to build an expression using information from a form to get data from a Crosstab query by using a Row header and a Column header. How would I go about doing this?

Thanks for your time

[June7]

Not clear what you want. Show example of source data and desired output.

You want to do a DLookup on a CROSSTAB query?

[theDBguy]

Hi. Why does it have to be an expression? Why not a query?

[plog]

First you take the goose over and return to the initial side of the river, then you take the beans over and return with the goose, next you take the fox..

You've made your problem harder than it needs to be. The answer to this is to use a Dlookup on your unpivoted/uncrosstabbed data.

First, make a copy of your cross tab qeury and uncrosstab it. Those crosstab columns now become values in their own column. Now, you have a static field to use in the Dlookup and a static column to apply criteria on to retreive the specific data you want using Dlookup (https://support.office.com/en-us/art...b-bed10dca5937)

[lws]

Sorry if I didn’t explain myself very well. What I would like to do is create a report for a daily time card summery with a page header as the employee’s name, along with some miscellaneous information. I have that down. The following is the SQL for my cross tab Query.

TRANSFORM Sum(TTimeCard.ID) AS SumOfID
SELECT TTimeCard.TCDate, TTimeCard.RateDiscription, TTimeCard.EffectiveRate, Round([TotalSeconds]/3600,0) & ':' & Right("00" & Round(((TotalSeconds/3600)-Round(TotalSeconds/3600,0))*60,0),2) AS TotalTime, (Sum(DatePart("h",[TCLTotal])*3600+DatePart("n",[TCLTotal])*60)) AS TotalSeconds, Sum(TTimeCard.ID) AS [Total Of ID]
FROM TTimeCard
WHERE (((TTimeCard.Billed)=False))
GROUP BY TTimeCard.TCDate, TTimeCard.RateDiscription, TTimeCard.EffectiveRate, TTimeCard.Billed
PIVOT TTimeCard.Full_Name;

The Query creates columns for TCDate, RateDiscription, EffectiveRate, TotalTime, TotalSecounds, TotalID, and for each employee name.

On the report I would like to display other information under the group TCDate such as RateDiscription, EffectiveRate, TotalTime only for the selected employee name. How would I build an expression to do this?

[June7]

Still not sure what you want nor how CROSSTAB is involved.

Is CROSSTAB query report RecordSource?

You want to filter report to show only one selected employee?

[Mark_]

Originally Posted by lws

I would like to display other information under the group TCDate such as RateDiscription, EffectiveRate, TotalTime only for the selected employee name. How would I build an expression to do this?

RateDescription looks like it would be shown as is for this employee, same with EffectiveRate.
TotalTime looks like it would be pulled as a total query.

This looks like information you would pull from its original source, not from a crosstab.

[lws]

OK I will simplify the question. Is there a way to glean data from a crosstab query by using 2 sets of criteria? 1 being if there is data in a cell under a particular column header, i.e. Employee Name and 2 for a particular date. If so how would I write an expression to get all of that particular record information? If not then I thank you for your time for looking at this and I will figure out how to accomplish by using other means.

[June7]

That doesn't help much.

You can use CROSSTAB in another query and apply filter or you can do DLookup() on CROSSTAB or you can apply filter criteria within CROSSTAB.

Provide info requested in post 2 and answer questions in post 4, then maybe we can help.