Re: More SQL by Tom
Tom
Thu Feb 02 13:09:28 CST 2006
Hey Cindy, thanks for the response, and sorry for posting this is 2 different
posts.
yes i want the data horizontally on the report, i dont want to match up
Agencies and Depts as they will be different, or more in on group than the
other. My text box sources in the report are agency.tom and angency.Tom1
respectfully for each field.
im not trying to be difficult here, i could do a set relation but i want to
do this in SQL, i like it so far and i want to continue with it. i will try
your option and let you know.
Thank you!
Tom
"Cindy Winegarden" wrote:
> Hi Tom,
>
> When you run a report it uses the current selected work area if no alias is
> given in the control source of the textboxes. In other words, if you use
> Agency instead of Tom1.Agency it will show Tom.Agency since that is the last
> SQL to run before your report.
>
> Your data is not the typical "Parent - Child" data. What do you want to do
> with the data? Do you want to show all the rows that are last month's and
> then all the rows that are this month's? Or, do you want to match up
> Agencies and departments and horizontally show the count of last month's and
> count of this month's data?
>
> I'm assuming you want to show the data "horizontally." You can do this
> either by setting a relation between the tables, or by using SQL to get what
> you want. I'd use the SQL method, especially if you want all the Agency-Dept
> combinations even if some don't have activity in both last month and this
> month.
>
> This would look like the SQL I posted before:
>
> Select ;
> Agency, Dept, ;
> Sum(Iif(Between(EmpDate, gomonth(checkhdr.procdate,-1),
> checkhdr.procdate), 1, 0)) As LastMonth, ;
> Sum(Iif(Between(EmpDate, checkhdr.procdate ,
> gomonth(checkhdr.procdate,1), 1, 0)) As ThisMonth ;
> From ....... ;
> Into ..... :
> Group By Agency, Dept
>
> Another thing: it looks like you're using a value from the current row of
> another table, CheckHdr.ProcDate. How can you be sure which row you're on in
> that table? Your best bet is to find the row, pick up the ProcDate as a
> variable, and then use that variable in your SQL. Something like:
>
> *-- Assume the user has located the row in CheckHdr that they want.
> m.ProcDate = CheckHdr.ProcDate
> *-- Use the following SQL
> Sum(Iif(Between(EmpDate, gomonth(m.ProcDate,-1), m.ProcDate) && etc.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn.com www.cindywinegarden.com
>
>
> "Tom" <Tom@discussions.microsoft.com> wrote in message
> news:9149221B-62DB-43E5-8847-92A8D5E88B24@microsoft.com...
> > here is my code, as you can see i am running these in 2 queries. and then
> > into a report. my report references the queries TOM AND TOM1. the problem
> > is
> > this, when i run the report, whch ever query is first, only shows the
> > first
> > record oover and over. ....
> >
> > SELECT AGENCY,DEPT,COUNT(*);
> > FROM newmem ;
> > where empdate between gomonth(checkhdr.procdate,-1) and checkhdr.procdate
> > AND INLIST(dept,24,34,35,38);
> > Group by 1,2;
> > into cursor TOM1
> >
> > SELECT AGENCY,DEPT,Count(*);
> > FROM newmem ;
> > WHERE empdate between checkhdr.procdate and gomonth(checkhdr.procdate,1)
> > AND (INLIST(dept,24,34,35,38));
> > Group by 1,2;
> > INTO CURSOR TOM
> > report form f:\lopvfp\newmem\listing.frx preview
>
>
>