Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-13-2019, 09:05 PM   #1
lws
Newly Registered User
 
Join Date: Mar 2013
Posts: 20
Thanks: 6
Thanked 0 Times in 0 Posts
lws is on a distinguished road
How to get data from query using Expression Builder

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

lws is offline   Reply With Quote
Old 02-14-2019, 12:24 AM   #2
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,959
Thanks: 0
Thanked 463 Times in 459 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: How to get data from query using Expression Builder

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

You want to do a DLookup on a CROSSTAB query?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 02-14-2019, 05:06 AM   #3
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,063
Thanks: 36
Thanked 732 Times in 715 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: How to get data from query using Expression Builder

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

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 02-14-2019, 06:15 AM   #4
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,285
Thanks: 10
Thanked 2,251 Times in 2,203 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: How to get data from query using Expression Builder

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)
plog is offline   Reply With Quote
Old 02-14-2019, 06:35 PM   #5
lws
Newly Registered User
 
Join Date: Mar 2013
Posts: 20
Thanks: 6
Thanked 0 Times in 0 Posts
lws is on a distinguished road
Re: How to get data from query using Expression Builder

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?
lws is offline   Reply With Quote
Old 02-14-2019, 07:50 PM   #6
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,959
Thanks: 0
Thanked 463 Times in 459 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: How to get data from query using Expression Builder

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?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 02-14-2019, 08:03 PM   #7
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,863
Thanks: 17
Thanked 353 Times in 350 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: How to get data from query using Expression Builder

Quote:
Originally Posted by lws View Post
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.

Mark_ is offline   Reply With Quote
Old 02-14-2019, 08:19 PM   #8
lws
Newly Registered User
 
Join Date: Mar 2013
Posts: 20
Thanks: 6
Thanked 0 Times in 0 Posts
lws is on a distinguished road
Re: How to get data from query using Expression Builder

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 by lws; 02-14-2019 at 08:25 PM.
lws is offline   Reply With Quote
Old 02-14-2019, 10:28 PM   #9
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,959
Thanks: 0
Thanked 463 Times in 459 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: How to get data from query using Expression Builder

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.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Expression builder in query. TobyMace Queries 6 06-05-2018 05:23 AM
Expression Builder : Query with calculation El-d Queries 3 05-10-2015 05:34 AM
Expression Builder in Data Macro Korreia Macros 0 11-12-2012 04:23 PM
Subtotal in Query Using Expression Builder pnpez Queries 6 12-12-2011 03:31 PM
Using Expression Builder in Query The Sharkster Queries 5 05-21-2010 09:31 AM




All times are GMT -8. The time now is 03:14 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World