Automatic update of field names

[Dougalsworld]

Hi Folks,

I have a bit of an odd question that I'd like to pick your brains about...

I have a report that runs from a date selection on a form...for e.g.
If you select you reporting month as June, it will provide the data for April May and June.

at the moment I have 3 queries, query1, query2, query3
query 1 = reporting month -2
query 2 = reporting month -1
query 3 = reporting month

What I want to know is on my final query where I add the three queries above together I now have three data columns (query1.data etc)

Is there a way I can have the field in the final query autmoatically rename (or at least have an automatic caption) so that it changes to Feb_Data etc

Cheers

 

[Singh400]

Change it to (in Query Builder):

TheFieldNameYouWant: query1.data

eg

TotalDue: query1.data

In SQL View it would be:

query1.data AS TotalDue

eg

<caculation> AS TotalDue

 

[Dougalsworld]

Sorry I should probably have been a bit clearer.
Manually changing field names I can do, but for e.g. if we just look at Query1,
If I select from my form the reporting month of May, then the data in this query will be that of March, however if I selected June then this query will hold April data etc (always 2 months behind), I want the field name to change "Automatically" to match the data it is showing,

it might not be possible but I thought I'd ask the question just in case...

 

[MrQ]

Hi Dougalsworld,
You could add a table with the twelve months in and the mm part of the date in a second field then find the mm part of the reporting month and use the corresponding value in your table as the caption.

Q

 

[namliam]

Only if you open the query in a form or report, they have captions. Queries do not.

Maybe an alternative, if you need it to be a query... is to create this query in code, show it to the user/export/whatever and delete the object/query again.
Then again, if your periods are always months. That would make 12 queries, not to bad to just have them reside in your DB and open the right one from code depending on the month selected.

Let me know what you use and if you were able to use it...

Good Luck!

 

[Dougalsworld]

Thanks MrQ,

That is sounding hopefull, can I ask how you would set it as a caption tho as I could not get the Caption entry to take anything other than basic text?

 

[MrQ]

You could include the months table in your main queries then add a field to the query which holds the name of the resulting month. This would give the month on each line but wouldn't matter if it's going to be the same month all the way through the results for that query.
Hope that helps.
If not post a sample of your db and I'll have a butchers!

 

[namliam]

Thanks MrQ,

Feeling a little left out

 

[MrQ]

True true,
Kudos to namliam, he's a Jedi next to my Padawan

 

[Dougalsworld]

Namliam,

My utmost appologies, you must have posted as i was replying to MrQ.
I will have a play and failing that I will post a copy of the db for you guys to have a look at,

Many thanks to both of you for your help, it really is appreciated