How to get data from query using Expression Builder (1 Viewer)

lws

Registered User.
Local time
Today, 08:15
Joined
Mar 14, 2013
Messages
22
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

AWF VIP
Local time
Today, 07:15
Joined
Mar 9, 2014
Messages
5,425
Not clear what you want. Show example of source data and desired output.

You want to do a DLookup on a CROSSTAB query?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:15
Joined
Oct 29, 2018
Messages
21,359
Hi. Why does it have to be an expression? Why not a query?
 

plog

Banishment Pending
Local time
Today, 10:15
Joined
May 11, 2011
Messages
11,613
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/article/dlookup-function-8896cb03-e31f-45d1-86db-bed10dca5937)
 

lws

Registered User.
Local time
Today, 08:15
Joined
Mar 14, 2013
Messages
22
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

AWF VIP
Local time
Today, 07:15
Joined
Mar 9, 2014
Messages
5,425
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_

Longboard on the internet
Local time
Today, 08:15
Joined
Sep 12, 2017
Messages
2,111
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

Registered User.
Local time
Today, 08:15
Joined
Mar 14, 2013
Messages
22
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.
 
Last edited:

June7

AWF VIP
Local time
Today, 07:15
Joined
Mar 9, 2014
Messages
5,425
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.
 

Users who are viewing this thread

Top Bottom