how to use present date as start date in access?

[benzylamine]

hello experts,

i have a problem with my query. i created a query that contains employee codes in the first column and transaction dates in the header with hour worked for each employee each day. for example.

20150101 20150203 20150303 ........ 20150518 TOTAL
A110 5HR 3HR ....... 400HR
B110 5HR 3HR ....... 400HR


C110 5HR 3HR ....... ...... 400HR

how can i show all the record that is BETWEEN 300HR AND 400HR including the date? thank you. i am stuck for all day about this problem. thank you

[June7]

This is a CROSSTAB query?

What is wrong with it? Why is it not giving you what you want?

Show example of desired output.

[benzylamine]

This is a CROSSTAB query?

What is wrong with it? Why is it not giving you what you want?

Show example of desired output.

because A110 has worked for total 400 hours, i want to get the last 200 hours record like pic showed above. thank you.

[benzylamine]

anyone know? I was so confused. thank you.

[June7]

Not understanding. If there is data for 20150101 and 20150202, why do you not want to display it under those columns? If you don't want 01 and 02 data then apply filter criteria to exclude them. There will then not be any columns for those months.

[benzylamine]

Not understanding. If there is data for 20150101 and 20150202, why do you not want to display it under those columns? If you don't want 01 and 02 data then apply filter criteria to exclude them. There will then not be any columns for those months.

because I want to get record 200 hours preceding "20150404". I do not know when it will reach 200 hours. the above data is just an example. is there any function such as iif hours reach 200 from "20150404", it will show all the record? thank you.

[June7]

You want only the most recent records and only those that add up to 200 hours? No, there is no simple way to accomplish. Will probably involve a running sum in query and then criteria to pull records where the running sum is less than 200. Running sum in query is not easy. Search forum and web on the topic. Here is one https://support.microsoft.com/en-us/kb/290136