Totals Query with Join - Help?

[Jay]

I have a simple table with the following fields:

ModelType text
ModelID number
SaleMonth date (mmm-yy)
SalePrice number

The table contains approximately 150 ModelTypes, each of which is made
up of several dozen ModelIDs. However, the ModelIDs which make up each
ModelType can vary from month-to-month, and this is the cause of my
question.

I want to query, grouping by ModelType and SaleMonth and averaging the
SalePrice. However, I want to query just two months, and for the two
months being queried only use fields where there are ModelID fields in
common between both months.

So it returns the average SalePrice for both months but on a
like-for-like basis (the same ModelIDs in each month).

Can anyone point me in the right direction. I don't really need a
parameter query to select the months as can use criteria to specify the
months. It's figuring how to do the like-for-like ModelIDs I'm a little
stuck with.

Any help greatly apreciated. Many thanks,

Jay

 

[gramelsp]

I have a simple table with the following fields:

ModelType text
ModelID number
SaleMonth date (mmm-yy)
SalePrice number

The table contains approximately 150 ModelTypes, each of which is made
up of several dozen ModelIDs. However, the ModelIDs which make up each
ModelType can vary from month-to-month, and this is the cause of my
question.

I want toquery, grouping by ModelType and SaleMonth and averaging the
SalePrice. However, I want toqueryjust two months, and for the two
months being queried only use fields where there are ModelID fields in
common between both months.

So it returns the average SalePrice for both months but on a
like-for-like basis (the same ModelIDs in each month).

Can anyone point me in the right direction. I don't really need a
parameterqueryto select the months as can use criteria to specify the
months. It's figuring how to do the like-for-like ModelIDs I'm a little
stuck with.

Anyhelpgreatly apreciated. Many thanks,

Jay

OK, this is a little heavy for me, but assuming a table like this,
the following might work. I do not think it is a real solution,
but might hold up for two months.

With CurrentProject.Connection
..Execute _
"CREATE TABLE Sales1" & _
" (ModelType VARCHAR (10) NOT NULL," & _
" ModelID LONG NOT NULL," & _
" SalesDate DATETIME DEFAULT NOW() NOT NULL," & _
" SalesPrice DECIMAL(12,2) NOT NULL," & _
" CHECK (SalesPrice > 0.00)," & _
" PRIMARY KEY (ModelType, ModelID, SalesDate));"

..Execute "INSERT INTO Sales1 VALUES ('101',24,'4/18/2007 08:45:00',
24.95);"
..Execute "INSERT INTO Sales1 VALUES ('101',24,'4/19/2007 11:19:00',
24.95);"
..Execute "INSERT INTO Sales1 VALUES ('103',29,'5/15/2007 14:23:00',
49.99);"
..Execute "INSERT INTO Sales1 VALUES ('105',27,'4/20/2007 09:07:00',
18.49);"
..Execute "INSERT INTO Sales1 VALUES ('105',27,'5/20/2007 16:20:00',
18.49);"
End With

Query1:
--------
SELECT *
FROM Sales
WHERE DATEDIFF("m",[Enter start month:],SalesDate)=0;

Query2:
--------
SELECT *
FROM Sales
WHERE Datediff("m",[Enter start month:],SalesDate) = 1;

Query3:
--------
SELECT Query1.*
FROM Query1 INNER JOIN Query2 ON Query1.ModelID=Query2.ModelID
UNION ALL SELECT Query2.*
FROM Query2 INNER JOIN Query1 ON Query2.ModelID=Query1.ModelID;

Query4:
--------
SELECT c.modeltype,
COUNT(c.salesprice)
AS [sales count],
CCUR(SUM(c.salesprice))
AS [total sales],
CCUR(SUM(c.salesprice) / COUNT(c.salesprice)) AS [monthly
average],
FORMAT(DATEADD("m",DATEDIFF("m",2,c.salesdate),2),
"mmm-yyyy") AS [sales month]
FROM query3 AS c
GROUP BY DATEADD("m",DATEDIFF("m",2,c.salesdate),2),
c.modeltype;

ModelType Sales Count Total Sales Monthly Average Sales Month
105 1 $18.49 $18.49 Apr-2007
105 1 $18.49 $18.49 May-2007

 

[gramelsp]

I have a simple table with the following fields:

ModelType text
ModelID number
SaleMonth date (mmm-yy)
SalePrice number

The table contains approximately 150 ModelTypes, each of which is made
up of several dozen ModelIDs. However, the ModelIDs which make up each
ModelType can vary from month-to-month, and this is the cause of my
question.

I want toquery, grouping by ModelType and SaleMonth and averaging the
SalePrice. However, I want toqueryjust two months, and for the two
months being queried only use fields where there are ModelID fields in
common between both months.

So it returns the average SalePrice for both months but on a
like-for-like basis (the same ModelIDs in each month).

Can anyone point me in the right direction. I don't really need a
parameterqueryto select the months as can use criteria to specify the
months. It's figuring how to do the like-for-like ModelIDs I'm a little
stuck with.

Anyhelpgreatly apreciated. Many thanks,

Jay

OK, this is a little heavy for me, but assuming a table like this,
the following might work. I do not think it is a real solution,
but might hold up for two months.

With CurrentProject.Connection
.Execute _
"CREATE TABLE Sales1" & _
" (ModelType VARCHAR (10) NOT NULL," & _
" ModelID LONG NOT NULL," & _
" SalesDate DATETIME DEFAULT NOW() NOT NULL," & _
" SalesPrice DECIMAL(12,2) NOT NULL," & _
" CHECK (SalesPrice > 0.00)," & _
" PRIMARY KEY (ModelType, ModelID, SalesDate));"

.Execute "INSERT INTO Sales1 VALUES ('101',24,'4/18/2007 08:45:00',
24.95);"
.Execute "INSERT INTO Sales1 VALUES ('101',24,'4/19/2007 11:19:00',
24.95);"
.Execute "INSERT INTO Sales1 VALUES ('103',29,'5/15/2007 14:23:00',
49.99);"
.Execute "INSERT INTO Sales1 VALUES ('105',27,'4/20/2007 09:07:00',
18.49);"
.Execute "INSERT INTO Sales1 VALUES ('105',27,'5/20/2007 16:20:00',
18.49);"
End With

Query1:
--------
SELECT *
FROM Sales
WHERE DATEDIFF("m",[Enter start month:],SalesDate)=0;

Query2:
--------
SELECT *
FROM Sales
WHERE Datediff("m",[Enter start month:],SalesDate) = 1;

Query3:
--------
SELECT Query1.*
FROM Query1 INNER JOIN Query2 ON Query1.ModelID=Query2.ModelID
UNION ALL SELECT Query2.*
FROM Query2 INNER JOIN Query1 ON Query2.ModelID=Query1.ModelID;

Query4:
--------
SELECT c.modeltype,
COUNT(c.salesprice)
AS [sales count],
CCUR(SUM(c.salesprice))
AS [total sales],
CCUR(SUM(c.salesprice) / COUNT(c.salesprice)) AS [monthly
average],
FORMAT(DATEADD("m",DATEDIFF("m",2,c.salesdate),2),
"mmm-yyyy") AS [sales month]
FROM query3 AS c
GROUP BY DATEADD("m",DATEDIFF("m",2,c.salesdate),2),
c.modeltype;

ModelType Sales Count Total Sales Monthly Average Sales Month
105 1 $18.49 $18.49 Apr-2007
105 1 $18.49 $18.49 May-2007- Hide quoted text -

- Show quoted text -

Oops. Obviously the table name in the queries has to agree with the
actual table name, so FROM Sales need to be FROM Sales1.

 

[Jay]

Wow. Thanks for the reply, but I must admit to being a bit confused by
quite a bit of it. What is the "With CurrentProject.Connection.Execute"?

And I'f be really grateful if you could you possibly explain each step?

MANY thanks,

Regards,

Jason

I have a simple table with the following fields:
ModelType text
ModelID number
SaleMonth date (mmm-yy)
SalePrice number
The table contains approximately 150 ModelTypes, each of which is made
up of several dozen ModelIDs. However, the ModelIDs which make up each
ModelType can vary from month-to-month, and this is the cause of my
question.
I want toquery, grouping by ModelType and SaleMonth and averaging the
SalePrice. However, I want toqueryjust two months, and for the two
months being queried only use fields where there are ModelID fields in
common between both months.
So it returns the average SalePrice for both months but on a
like-for-like basis (the same ModelIDs in each month).
Can anyone point me in the right direction. I don't really need a
parameterqueryto select the months as can use criteria to specify the
months. It's figuring how to do the like-for-like ModelIDs I'm a little
stuck with.
Anyhelpgreatly apreciated. Many thanks,
Jay

OK, this is a little heavy for me, but assuming a table like this,
the following might work. I do not think it is a real solution,
but might hold up for two months.

With CurrentProject.Connection
.Execute _
"CREATE TABLE Sales1" & _
" (ModelType VARCHAR (10) NOT NULL," & _
" ModelID LONG NOT NULL," & _
" SalesDate DATETIME DEFAULT NOW() NOT NULL," & _
" SalesPrice DECIMAL(12,2) NOT NULL," & _
" CHECK (SalesPrice > 0.00)," & _
" PRIMARY KEY (ModelType, ModelID, SalesDate));"

.Execute "INSERT INTO Sales1 VALUES ('101',24,'4/18/2007 08:45:00',
24.95);"
.Execute "INSERT INTO Sales1 VALUES ('101',24,'4/19/2007 11:19:00',
24.95);"
.Execute "INSERT INTO Sales1 VALUES ('103',29,'5/15/2007 14:23:00',
49.99);"
.Execute "INSERT INTO Sales1 VALUES ('105',27,'4/20/2007 09:07:00',
18.49);"
.Execute "INSERT INTO Sales1 VALUES ('105',27,'5/20/2007 16:20:00',
18.49);"
End With

Query1:
--------
SELECT *
FROM Sales
WHERE DATEDIFF("m",[Enter start month:],SalesDate)=0;

Query2:
--------
SELECT *
FROM Sales
WHERE Datediff("m",[Enter start month:],SalesDate) = 1;

Query3:
--------
SELECT Query1.*
FROM Query1 INNER JOIN Query2 ON Query1.ModelID=Query2.ModelID
UNION ALL SELECT Query2.*
FROM Query2 INNER JOIN Query1 ON Query2.ModelID=Query1.ModelID;

Query4:
--------
SELECT c.modeltype,
COUNT(c.salesprice)
AS [sales count],
CCUR(SUM(c.salesprice))
AS [total sales],
CCUR(SUM(c.salesprice) / COUNT(c.salesprice)) AS [monthly
average],
FORMAT(DATEADD("m",DATEDIFF("m",2,c.salesdate),2),
"mmm-yyyy") AS [sales month]
FROM query3 AS c
GROUP BY DATEADD("m",DATEDIFF("m",2,c.salesdate),2),
c.modeltype;

ModelType Sales Count Total Sales Monthly Average Sales Month
105 1 $18.49 $18.49 Apr-2007
105 1 $18.49 $18.49 May-2007- Hide quoted text -

- Show quoted text -

Oops. Obviously the table name in the queries has to agree with the
actual table name, so FROM Sales need to be FROM Sales1.

 

[Michael Gramelspacher]

Wow. Thanks for the reply, but I must admit to being a bit confused by
quite a bit of it. What is the "With CurrentProject.Connection.Execute"?

And I'f be really grateful if you could you possibly explain each step?

MANY thanks,

Regards,

Jason

The With .. End With block just makes it easy to copy it into a module sub
and create and fill the test table.

What this problem involves is an inner join between two sets. Each month
is a set. Query 1 and query 2 establish the two sets.

The Query 3 unions the matching values for each month to create a single
set of values where both months have the same ModelIDs.

Query 4 just summarizes the values by month.

Incidently, by changing Query 2 to = -12, you could compare like months
from different years. This assumes the table has that much data.

 

[Jay]

The With .. End With block just makes it easy to copy it into a module sub
and create and fill the test table.

What this problem involves is an inner join between two sets. Each month
is a set. Query 1 and query 2 establish the two sets.

The Query 3 unions the matching values for each month to create a single
set of values where both months have the same ModelIDs.

Query 4 just summarizes the values by month.

Incidently, by changing Query 2 to = -12, you could compare like months
from different years. This assumes the table has that much data.

Thanks Michael. I think this is slightly beyonf my level of
understanding at the moment but I'm going to have a go. And if I can
figure this out then I'll have certainly learned a lot in the process.

Can I ask what the "('101',24,'4/18/2007 08:45:00',
24.95" VALUES signify.

Regards,

Jason