473,320 Members | 2,164 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Creating a dynamic view for a union query based on a rolling date

I am trying to create a query with a rolling date range to combine log files from the last week. Each day a new log table is created (log_file_07_01_2013).

I want to create a view which would dynamically update the query for the last seven days of histoy:

i.e.

Expand|Select|Wrap|Line Numbers
  1. Select * 
  2. from log_file_07_01_2013
  3. union
  4. Select * 
  5. from log_file_07_02_2013
  6. ...
  7. Union
  8. Select * 
  9. from log_file_07_07_2013
Jul 25 '13 #1

✓ answered by Rabbit

If you can't change the table structure, then you'll need to create a function that builds and executes a dynamic SQL string. More on that can be found in the PostGreSQL documentation here: http://www.postgresql.org/docs/curre...-EXECUTING-DYN

3 3743
Rabbit
12,516 Expert Mod 8TB
Please use code tags when posting code or formatted data.

Your current table structure breaks the rules of normalization. You should not have a different table for each date. Instead you should have just one table with a date field. That way, querying would be a lot easier by using a dynamic date range in the WHERE clause. What you are trying to do now with dynamic table names is very difficult because of the flawed table structure.

You can read more about normalization in our article here: http://bytes.com/topic/access/insigh...ble-structures
Jul 25 '13 #2
The database is externally managed so I cannot change the schema as much as I would like to.
Jul 25 '13 #3
Rabbit
12,516 Expert Mod 8TB
If you can't change the table structure, then you'll need to create a function that builds and executes a dynamic SQL string. More on that can be found in the PostGreSQL documentation here: http://www.postgresql.org/docs/curre...-EXECUTING-DYN
Jul 25 '13 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Buttercup | last post by:
I am having alot of trouble with a union query Im trying to create. The bizarre thing is I initially created this query and managed to save it as a view SELECT ID, DepartureDate, City,...
1
by: tolisss | last post by:
Hi i have created a new database and a new linked server that points to an AccessDB using an ODBC DSN. Now inside that new sql db i have create i need to created a new view so i open EM went to...
3
by: brendan_gallagher_2001 | last post by:
Hi, I have a view(A) and I am trying to do a join on another table (B) to include only rows where date values in view A is greater than in table B. I also want the view to pick up rows in viewA...
0
by: Len Coleson | last post by:
I am at wits end with this. I have a transaction report that has a union query as a record source that asks date parameters from an input form. The sub report is based on a variation of the same...
4
by: shaun palmer | last post by:
when or Where do you use a union query ? how can I wright sql, tblPopulation,* tblcustomer,* one to one with all the appropriate attributes(field). Your help would be greatly...
1
by: CrystalDBA | last post by:
I usually design applications in SQL Server and Crystal Reports. I now need to create a crystal report on an MS Access database. I have two tables: Services: Date datetime Entry text...
4
by: Mark | last post by:
Hi all, I am currently in the design stages of a database for work. I have come up with a way to get informaion I need using a union query but was wandering what effects this will have on...
1
by: PATGMorris | last post by:
I've got a Union Query that pulls data from two different tables for chemistry and micro testing. The tables containing very similar data but for reasons not necessary here, cannot be put into one...
2
by: planetthoughtful | last post by:
Hi All, I'd like to place a subreport onto a report that draws its records from a UNION query. When I design the query and view the records all looks fine. However, when I put the query as...
8
by: kepston | last post by:
I have a situation where I need to record scrap quantities for parts that we sell and parts that are supplied, in a single table. I have a UNION query that combines the part numbers successfully,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.