Query Help -

[ardy]

Hello All:
I am having a heck of a time figuring this out….. . I Think there needs to be a conditional statement to the nearest date to the 15th day of the month. Most of date statement I have found are between and math driven(add subtract etc...). I have table with bunch of wells, divided into two types Monthly and Quarterly, each well has several reads spanning over time(time series). I need to a query that can make a new table with all the reads that fall on or closest to the 15th of each month. The kicker is that I need only one read if the type is Quarterly for each 3 months(the quarter) and 1 read for each month if the type is monthly(total of 3 read for the quarter). Can somebody help me on this? The example below is kind of showing this ……

Well_Num, Type, Read, Date
Well1, Monthly, 10.2, 1/1/2013
Well1, Monthly, 10.2, 1/2/2013
Well1, Monthly, 10.1, 1/20/2013
Well1, Monthly, 10.3, 2/10/2013
Well1, Monthly, 10.3, 2/16/2013
Well1, Monthly, 10.3, 3/1/2013
Well1, Monthly, 10.3, 3/15/2013
Well2, Quarterly, 9.1, 1/1/2013
Well2, Quarterly, 9.2, 1/5/2013
Well2, Quarterly,9.1, 2/2/2013
Well2, Quarterly,9.1, 2/15/2013
Well2, Quarterly,9.1, 3/2/2013
Well2, Quarterly,9.1, 3/18/2013

The resulting Table from the query should be

Well_Num, Type, Read, Date
Well1, Monthly, 10.1, 1/20/2013
Well1, Monthly, 10.3, 2/16/2013
Well1, Monthly, 10.3, 3/15/2013
Well2, Quarterly,9.1, 2/15/2013

Is this possible to achieve with Create Table Query. maybe it would be easier if I devide the query in several queries? any help is appricuiated

 

[plog]

First, you have an improperly structured database. You should have a seperate tables for Wells and WellReads. Type would be in Wells and Read and Date would be in WellReads.

I would also add an autonumber primary key to WellReads so you can uniquely identify rows of data (which is what you are asking to do).

Additionally, 'Date' is a poor choice for a field name because it is a reserved word in Access and makes coding for queries and VBA difficult--which we will run into trying to generate what you want. I suggest you rename it 'ReadDate'.


A question about your actual issue: A read occurs on Well1 on 4/14/2013 and another read occurs on 4/16/2013. They are both 1 day from the 15th, which one gets used?

One more because I am feeling pedantic: Do you actually mean the 15th of month or do you mean the middle of the month? There's a difference--February 14th is the middle of that month and (usually) the middle of Quarter 1. What exactly do you want.

 

[ardy]

First off I like to thank you for replying to this post……. I think I need to explain a bit more to clarify the situation. The actual database that the reads are stored in is in a different system, so what I start with is an export of the data (5000+- records) for any given quarter…… My goal is to streamline this process because we need to do this just abt every quarter. So I am in the midst of building this tool (Access project – Form driven). Once done anybody can process this data. I am %80 done, some minor hiccups with some forms but not a big deal, the one thing that is baffling me is this query. Now mind you I am a novice but I think I do OK……..So with that said let me see if I can respond to the concerns/Questions…….
*One of the processes in access is to import the export file and create a table, that table dose have a primary(Autonumber) for the reads…… This table is also gets cleared and ready for import at the start of the process…..
*Yes I found that out some months ago on a different project not to use Date, the actual field name is measurement_date, I was just trying to simplify the example, my bad…….
*the difference of exact one day before or after is not an issue, well what I mean is that either one is OK……
*I don’t think you are feeling pedantic, it has to get defined and in some respect the solution often times are in the detail…….either one works the 15th day of the month or mid month……I prefer the 15th day…….but either works……
Once again I really appreciate your help……….

 

[plog]

Can you repost your data with correct Field and table names?

 

[ardy]

To Simplify essentially the table in the database has all needed attributes to compile the query.....

 

[ardy]

Good Morning plog.....
I was wondering if you made any headway on this, is this possible you think through Query, Maybe it would be easier to do this in VB and make function........ommmm I wonder.....

 

[plog]

Sorry, it didn't notify me that you had posted. Attached is the solution--it took a VBA function (to determine the MidPoint of every TimeFrame) and then 3 sub queries to identify the correct records for each listing you needed.

Let me know if you need anything explained or it doesn't work exactly like you want.

I would still suggest you reconfigure your table as I initially suggested.

 

[ardy]

Thanks a bunch..... I see your approach. This is Good, I can modify from this point to Taylor to the project........ I really appreciate your help.