x

access queries to sql stps.

I have two queries in access i have to get into sql one query calls the other can anyone that programs help me. They want it yesterday and i am clueless.

SELECT [Prior Approvals].[Dlr Num], [Prior Approvals].Promo, Sum([Prior Approvals].[Est Cost]) AS TotalCost, Sum([Prior Approvals].Balance) AS TotalPaid FROM [Prior Approvals] INNER JOIN Promos ON [Prior Approvals].Promo = Promos.Promo WHERE ((([Prior Approvals].Active)=True) AND ((Promos.Paranet)=True)) GROUP BY [Prior Approvals].[Dlr Num], [Prior Approvals].Promo;

SELECT Funds.*, ActivePriorTotals.TotalCost, ActivePriorTotals.TotalPaid, Promos.[Promo Desc], Promos.Year, Promos.Rolling FROM ((Funds LEFT JOIN ActivePriorTotals ON (Funds.[Dlr Num] = ActivePriorTotals.[Dlr Num]) AND (Funds.Promo = ActivePriorTotals.Promo)) INNER JOIN Promos ON Funds.Promo = Promos.Promo) INNER JOIN [Dealer Names And Addresses] ON Funds.[Dlr Num] = [Dealer Names And Addresses].[Dlr Num] WHERE ((([Dealer Names And Addresses].ParaNet)=True) AND ((Promos.Paranet)=True)) ORDER BY Funds.[Dlr Num], Promos.Year DESC , Funds.Promo;

more ▼

asked Jan 17, 2013 at 07:08 PM in Default

avatar image

artistlover
866 49 64 68

which one calls the other? I presume one of them is named as something that the other one references?

Jan 17, 2013 at 07:13 PM Kev Riley ♦♦

the second calls the first

Jan 17, 2013 at 08:06 PM artistlover
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You can simply put the whole of the first query into an inline-view (or derived table) and reference it as the same name ActivePriorTotals

 select  Funds.* ,
         ActivePriorTotals.TotalCost ,
         ActivePriorTotals.TotalPaid ,
         Promos.[Promo Desc] ,
         Promos.Year ,
         Promos.Rolling
 from    ( ( Funds
             left join ( select  [Prior Approvals].[Dlr Num] ,
                                 [Prior Approvals].Promo ,
                                 sum([Prior Approvals].[Est Cost]) as TotalCost ,
                                 sum([Prior Approvals].Balance) as TotalPaid
                         from    [Prior Approvals]
                                 inner join Promos on [Prior Approvals].Promo = Promos.Promo
                         where   ( ( ( [Prior Approvals].Active ) = True )
                                   and ( ( Promos.Paranet ) = True )
                                 )
                         group by [Prior Approvals].[Dlr Num] ,
                                 [Prior Approvals].Promo
                       ) ActivePriorTotals on ( Funds.[Dlr Num] = ActivePriorTotals.[Dlr Num] )
                                              and ( Funds.Promo = ActivePriorTotals.Promo )
           )
           inner join Promos on Funds.Promo = Promos.Promo
         )
         inner join [Dealer Names And Addresses] on Funds.[Dlr Num] = [Dealer Names And Addresses].[Dlr Num]
 where   ( ( ( [Dealer Names And Addresses].ParaNet ) = True )
           and ( ( Promos.Paranet ) = True )
         )
 order by Funds.[Dlr Num] ,
         Promos.Year desc ,
         Funds.Promo;
more ▼

answered Jan 17, 2013 at 08:30 PM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

what do i use instead of = true in the above

Jan 17, 2013 at 09:35 PM artistlover

Are they bit datatypes? And if so have you implemented that 1 = True, 0 = False? In that case use '=1'

Jan 17, 2013 at 09:37 PM Kev Riley ♦♦

Thank you

Jan 17, 2013 at 09:55 PM artistlover
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x479

asked: Jan 17, 2013 at 07:08 PM

Seen: 410 times

Last Updated: Jan 17, 2013 at 09:55 PM

Copyright 2018 Redgate Software. Privacy Policy