Mike, Thanks for coming back to me.
I'm sorry I didn't realise oracle has sql commands that won't run in access hmm clearly i am a beginner... On Oct 29, 3:29 am, Michael Moore <michaeljmo...@gmail.com> wrote: > PLSQL is Oracle's Procedural Language which is tightly integrated with > Oracle SQL. > > You have said you are using Access. Why are you asking an Oracle group for > an Access solution? > > I might be able to provide an Oracle SQL solution but I don't want to spend > the time on it if it will not be useful to you. > > Mike > > On Thu, Oct 27, 2011 at 10:45 PM, Aine Ni She <ain...@gmail.com> wrote: > > > > > > > > > Hi there, > > > I have 4 colums of data (with 2,000 rows) > > > Column 1: Subscriber Types (2) > > Column 2: Serial Numbers (2000) > > Column 3: Service Types (5) > > Column 4: Rates > > > Column 1: There are 2 subscriber types: Type_A, Type_R > > Column 3: There are 5 Service Types: B, T, DP, H, F > > Column 2: The Serial numbers in Column 2 can have more than 1 Service Type > > associated with it. > > Column 4: The rate associated with the service Type > > > B, T, H, F have set rates. > > DP is a variable rate. (it depends on if the service type includes B + DP > > or B & DP + T) > > > For example: > > Serial number 123456 can have service types B + T + H if this is the case I > > apply a set rate > > > My issue arises when when a serial number has service types: B + DP or > > B + T+ DP. A special rate applies to B + DP and a different rate applies to > > B + T + DP. > > > So what I have done is in access using sql (unsuccessfully) is > > > counted service type > > apply the rate > > Produce invoice > > > What I want to do is: > > select by distinct serial number > > check the types of services associated with it > > add all the B's, T's, H's and F's up > > IF service type = DP then check if B or B & T are associated > > create 2 new service types B_DP & T_DP so > > when service type = B,T,DP service T_DP is assigned to it and > > when service type = B,DP service B_DP is assigned to it. > > > Then I want create a basic table showing a breakdown of the calculations. > > Service Type Usage number Rates > > Count B xx $$ > > Count T xx $$ > > Count H xx $$ > > Count F xx $$ > > Count B_DP xx $$ > > Count T_DP xx $$ > > > I can do it for all service types except when it comes to when service type > > DP. I don't know how to code it.. > > > Can anyone offer advise on this please > > > Thanking you kindly in advance, > > Aine > > > -- > > You received this message because you are subscribed to the Google > > Groups "Oracle PL/SQL" group. > > To post to this group, send email to Oracle-PLSQL@googlegroups.com > > To unsubscribe from this group, send email to > > oracle-plsql-unsubscr...@googlegroups.com > > For more options, visit this group at > >http://groups.google.com/group/Oracle-PLSQL?hl=en -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
