Query that seperates the date?

[patchesohouli]

Hi,
Every week a file will be imported into a table which breaks out into three separate queries, depending on the pay frequency. Within that, I know one of the pay frequencies will have two calendar dates associated, another will have two dates, and a third will have four dates. These dates will be changing each week, so it’s not like I can set the criteria for a specific date.

I need to further break these down so I have a separate query for each pay frequency with only one pay date. Therefore, I essentially need 8 separate queries. Is there any way for access to do that? It will need to recognize that a pay frequency has two distinct calendar dates associated, and separate it out by the different dates.

There should be separate queries for:

Biweek 26 – 1/1/2016
Biweek 26 – 1/15/2016
Weekly – 1/1/2016
Weekly – 1/8/2016
Weekly – 1/15/2016
Weekly – 1/22/2016
Ect.

But again, those calendar dates will change constantly. The number per pay frequency however will remain constant.

 

[plog]

Putting values into names is never a good idea (e.g. ... - 1/1/2016). Instead that data should be in the datasource itself as a field.

My guess is that you only need one query, but you need a calculated field to determine the pay frequency (Biweek 26, Weekly, etc.) and date (1/1/2016, 1/15/2016, etc.). Kind of like how you wouldn't have a new table for every person you paid--you'd simply have a field in that table/query to tell you who got paid.

 

[patchesohouli]

Thanks! I have a query that separates the pay frequencies. But how would access separate by the distinct pay dates in each frequency?

 

[plog]

But how would access separate by the distinct pay dates in each frequency

I don't know what that means. Can you provide sample data?

 

[patchesohouli]

Hi,

I have uploaded sample data. So, there should be a separate output for:

Biweek 26 – 1/1/2016
Biweek 26 – 1/15/2016
Weekly – 1/1/2016
Weekly – 1/8/2016
Weekly – 1/15/2016
Weekly – 1/22/2016
ect.

Thanks for all your help

 

[plog]

If that's the data you are starting with, there's no need for seperate queries. What's the end goal? What are you doing in Access that makes you think you need so many seperate queries?

 

[patchesohouli]

I need to quickly separate the data each week by those parameters. And then, export that report into an excel file. It would be nice if it was automated, and the system automatically could export each of the 8 excel files.

 

[patchesohouli]

Does anyone know how this would work? Thanks!

 

[JHB]

It sounds Russian for me:
Can you explain what you want to get in a different way and what problems you have to achieve your result?