Date criteria to compile past years quarterly spending

[GSINC]

Hi Guys,

Table1 contains the following fields:
Name CompanyID SignUpDate

Joined by CompanyID

Table two contains the following fields:
CompID AmountSold1 AmountSold2 DateSold

I am trying to calculate the amount spent the previous year by quarters based on the SignUpDate.

SELECT TABLE1.COMPID, Sum(TABLE2!SOLD1+TABLE2!SOLD2) AS [Amount Sold], TABLE1.NAME, TABLE1.SIGNUPDATE
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.NAME = TABLE2.COMPID
GROUP BY TABLE1.COMPID, TABLE2.NAME, TABLE1.SIGNUPDATE;

I have tried a dateserial but didn't seem to work. Do you have any thoughts?

 

[GingerR]

You might want to re-think having a field called "NAME" in your table. That's a reserved word that might cause you problems.

Is COMPID in table2 really "CompanyID"? you say "joined by COmpanyID", i assume that means the relationship it has with Table2? In your query you join on "NAME" and "COMPID", shoudn't it be on "CompanyID" and "CompID"?

In your query you cite "Table1.CompID" but don't list "CompID" as a field in Table1?

In your query you cite "Table2.Name" but don't list "Name" as a field in Table2?

This is very confusing.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[GSINC]

I just renamed the fields so it would be more simple to understand. Here is the SQL:
SELECT MEDI_GROUPDATA.GRPNO, Sum(MEDI_HSTTRAN!PRVPMT+MEDI_HSTTRAN!EEPMT) AS [Amount Sold], MEDI_GROUPDATA.NAME, MEDI_GROUPDATA.RENEWLDT
FROM MEDI_GROUPDATA INNER JOIN MEDI_HSTTRAN ON MEDI_GROUPDATA.GRPNO = MEDI_HSTTRAN.GRPNO
GROUP BY MEDI_GROUPDATA.GRPNO, MEDI_GROUPDATA.NAME, MEDI_GROUPDATA.RENEWLDT;

grpno=companyid
prvpmt=amount1
eepmt=amount2
name=companyname
renewldt=signupdate

Sorry for the typos.

 

[GingerR]

What are the results of your query? What is wrong with it? What would you like to see? Please provide sample data.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[GoDawgs]

Format([SignUpDate],"q yyyy") will get you the quarter for each of your dates...then you can group by that if that's what you're looking for.