Merging two expression columns into one

[Andy__F]

Hi all I have been banging my head into a brick wall with this one for ages but with out success so am after HELP !!!!

Ok i have two column of expressions

1=(<(Now()+365)-([Service Interval]*30) Or <(Now()*365)-(([Service Interval]*2)*30))

2= (<(Now()+365)-(([Service Interval]*2)*30) Or <(Now()*365)-(([Service Interval]*2)*30))

I want to merge these two columns into one but then have two entry's not one !

and then i can sort by date in a report (i am trying to sort all service due for the customer in the next year) any surgestions

 

[pr2-eugin]

Hello Andy_F, Welcome to AWF.

I am unable to understand your description of the problem. Could you give another shot at explaining it? Maybe with some sample data and the result you wish to see.

 

[Andy__F]

Hi pr2-eugin

Yea sure I have

Customer IDCompany NameService IntervalLastOfDateExpr1Expr215401Mr Smith1203-Jun-0929/05/1024-May-11

and I would like

Customer IDCompany NameService IntervalLastOfDate 15401Mr Smith1203-Jun-0929/05/1015401Mr Smith1203-Jun-0924/05/11

some how !

hope that helps

 

[pr2-eugin]

Could you re post the data either by using comma separated values or using the CODE tags.

 

[Andy__F]

Sorry the best I can get is the attached

hope this helps !

 

[namliam]

What you want to do you cannot achieve quite that easily.... perhaps this way...

1) Make a table that only contains the values 1,2,3,4,5 for 5 seperate records, lets call the column xyz for the the moment
2) Make a query with your table and "my" table created from 1
3) Adjust your expression column to:
(<(Now()+365)-(([Service Interval]*[xyz])*30) Or <(Now()+365)-(([Service Interval]*xyz)*30))

NOTE:
a) I changed your now()*365 to now()+365, since the * doesnt make sence
b) I do not quite get the "OR" part of this, it indicates that you want to put this into the where clause.... which again doesnt make sence.
c) LastOfDate suggests that you have a query or table that has the column called "DATE", which is a reserved word.... Not the best of practice to use a reserved word as a column name.
d) Is Service Interval always months?
e) if you want to make a single expression Column, try something along the lines of:
MissedDates: LastOfDate + ([Service Interval]*xyz)

 

[Andy__F]

Sorry I should read my post more carefully !!!

should read
Expr1: Last([Date]+([ServiceInterval])*30)
Expr2: Last([Date]+(([ServiceInterval]*2)*30)


Service Interval is different for different customers

The project is to list all our service in the next year, however this start from the last service i.e if the service was one was 01/01/13 and its every 6 months the second service is due 01/06/13 then 01/01/14 ... and another customer was serviced on the 01/01/13 with a service interval of 12 months the second is due 01/01/14, I want to be able to put both these record together so I can list all the services due in the next 12 months from today, it need to be dynamic as the engineers probably won't do it on the right date

I totally see your point about "Date" don't know why I done that ! :banghead:lol

 

[namliam]

1) did you create said table?
2) did you create said query?
d) Service Interval I guess is always different for each customer, but always still months
e) did you try using this column in the query created in 2)

If you prefer I guess you can also use a little piece of code... particular if you are only intrested in the next service date (even if its every 3 months thus 4 times a year, you are only intrested in the next needed service date)

In which case this should work:

NextService: dateadd("M", (
                           int(
                               datediff("M",LastOfDate,date())
                               /[Service Interval]
                              )+1
                          ) * [Service Interval]
                        , LastOfDate )

Note: the spacing isnt needed but might help you understand WTF is goings on.

 

[Andy__F]

Hi Namliam


The problem is my boss want all the occurrence for the next year in one report, not just the next one (at this stage I given up on the four month ones as there only 1 and just concentrated on the 6 and 12 months thus the two expressions which are in a query.

The service Interval is a number from one to twelve (so months!)

best wishes

 

[namliam]

Well if you want multiple occurances per year your only recourse is to create "Dummy" records by using a cartesian product....
Like I described earlier, make a table containing (in this case) 12 records... since that will cover even if the interval is 1

then make the query and use the expresion I gave at my point e above and you can solve any future 12 mainenance dates you want.... with a 1 month interval, for 1 year
With 12 months interval for 12 years, but that is easily solved by using a quick where clause.

If you want to limit to 3,4,5,6,7,8,9,10,11,12 as possible values you would only need 4 possible values instead of 12 in the extra table.

Worse comes to worse you can also make a union query but I would definately shy away from that cause that is a maintenance nightmare IMHO

 

[bmal]

This is interesting. Unless I'm off my rocker, I would have addressed the OP's task with a Union Query. I do this with debit and credit journal entries all the time that I want in one output. As long as the fields are the same, the criterion for Service_Interval can be distinct.