Try taking the query apart a piece at a time until it works. Remove the
daily and try. remove the monthly and try.
:
Good news! Fixing the Date Of Sale from Text to date/time worked. So now
each of the queries, daily/monthly/yearly, all have the correct amounts
showing. Thanks for the help. I had thought that I had the Date of Sale as
being date/time, so hadn't even thought that it could have been an issue. My
only other question is that now when I try to run the totals query, which I
was going to base the cross-tab query on the daily and monthly totals don't
appear. I checked and I have the three queries left joined yearly to monthly
to daily. Any thoughts on why that is happening? Thanks again!
:
It will not work with the field as a text field.
You will need to add a temporary field - datetime.
Run an update query to update the temp field from the orignal date field.
If everything update correctly then change the orignal field to datetime.
Then run update from temp to the orignal that will now be datetime field.
:
Even with switching Date to Now I have the same results. Monthly gives a
total under both Date and Now. Daily is still blank. One the totals query
it also won't show amounts for daily or monthly amounts. I went back to look
in my tables and in the Date of Sale field I have it set to text, could not
having it set to date/time be causing this?
:
There is one reason your daily might not be working.
The query is using Date() in the criteria. If your records have a time
component then that would be the reason as Date() is for midnight.
Change the query to use Now() instead of Date().
:
I added some new records into the database for this month and today as well.
The daily totals still doesn't come up. The monthly totals comes up in the
monthly querty, but not within the totals query that I was using to base the
crosstab query on. Now on the daily total I was wondering if I should have
it set up with some sort of parameter to enter the date in which I am looking
for the total?
I just double check the queries and they work for me.
The only thing I can think of is to check and make sure you have a record
for the current date when you run the daily.
:
I have gone over the SQL statements for the daily and monthly totals and they
are exactly as they were written out, no spelling errors or anything. Is
there any other reason as to why the two queries are not working? Should I
be looking elsewhere in my database where things could be holding up the
queries?
:
I entered the SQL Statements for the individual queries, but only the year to
date query actually ran and gave me an amount. Have gone over mine to make
sure spelling and everything is correct and don't see anything out of the
ordinary. Doesn't make sense that one works and the others don't. Will look
again. Thanks for all the help.
:
You included dates in the output of the individual queries. Use these. I
gave you two yearly - to date and past year.
Daily sales --
SELECT Sum(TblSale.Price) AS [Daily Sales], "X" AS X
FROM TblSale
WHERE (((([DateOfSale]))=(Date())))
GROUP BY "X";
Monthly sales query --
SELECT Sum(TblSale.Price) AS [Monthly Sales], "X" AS X
FROM TblSale
WHERE (((Year([DateOfSale]) & Month([DateOfSale]))=Year(Date()) &
Month(Date())))
GROUP BY "X";
Year to date sales --
SELECT Sum(TblSale.Price) AS [Year to date Sales], "X" AS X
FROM TblSale
WHERE (((Year([DateOfSale]))=Year(Date())))
GROUP BY "X";
Past year sales—
SELECT Sum(TblSale.Price) AS [Year to date Sales], "X" AS X
FROM TblSale
WHERE (((([DateOfSale])) Between DateAdd("yyyy",-1,Date()) And Date()))
GROUP BY "X";
:
Okay I took out the dates from that query I mentioned below, but still had
the three copies of each.
The SQL statment is the following.
SELECT QryDailyTotal.DailyTotal, QryMonthlyTotal.SumOfPrice,
QryYearlyTotal.SumOfPrice
FROM (QryYearlyTotal LEFT JOIN QryMonthlyTotal ON QryYearlyTotal.Expr1 =
QryMonthlyTotal.Expr1002) LEFT JOIN QryDailyTotal ON QryMonthlyTotal.Expr1002
= QryDailyTotal.Expr1002
GROUP BY QryDailyTotal.DailyTotal, QryMonthlyTotal.SumOfPrice,
QryYearlyTotal.SumOfPrice;
For the daily total query the SQL is:
SELECT TblSale.DateOfSale, Sum(TblSale.Price) AS DailyTotal, "X" AS Expr1
FROM TblSale
GROUP BY TblSale.DateOfSale, "X";
Monthly Total:
SELECT Month(TblSale!DateOfSale) AS SaleByMonth, Sum(TblSale.Price) AS
SumOfPrice, "X" AS Expr1
FROM TblSale
GROUP BY Month(TblSale!DateOfSale), "X";
And Yearly:
SELECT Year(TblSale!DateOfSale) AS SalesByYear, Sum(TblSale.Price) AS
SumOfPrice, "X" AS Expr1
FROM TblSale
GROUP BY Year(TblSale!DateOfSale), "X";
Thanks for all the help.
:
Do not include the date in the output - just use it when setting criteria for
the totals.
If you still have a problem then post the SQL statements for each query.
Open the query in design view and on the menu click VIEW - SQL View, copy
and paste.
:
I add field X to all of my queries and that was fine. I have also built that
other query our of the daily/monthly/yearly totals and left joined them. The
problem that I ran into was that it has runs each date three times. I'll try
to show you what it is doing.
Date of Sale DailyTotal Month MontlyTotal Year Yearly Total
1/2/2005 $4,200.00 1 $4,200.00 2005 $13,600.00
1/2/2005 $4,200.00 2 $2,700.00 2005 $13,600.00
1/2/2005 $4,200.00 3 $6,700.00 2005 $13,600.00
It does this for every entry that I have in the system, which isn't much,
but just enough to make sure everything is working correctly. I am new to
access so I realize that I could have things set up wrong or something like
that. Thanks for all of the help and any continued help would be great. I
was also looking at the crosstab query and am not sure what I should use as
the value because if I use a total and then use the sum funtion with it I
have the wrong amount for totals (doubled).
:
In each of your Totals queries add a field X:"X" and then all will have a
common field with the same data. To do this open the query in design view
and type it in the field row in a blank column.
Build another query with all of them and left join the yearly to the monthly
and daily. Save.
Build your crostab off this last query.
:
I have been trying to create a crosstab query which will display a daily
total, monthly total and the yearly totals of our ordered sales. I have a
daily total query and another query to determine monthly totals for the year.
Both of these work fine, but when I try to add them into the crosstab query
format I can't get it to work out right. I was given a suggestion on how to
set it up with the months for the column header and days for rows. I have
tried to use a query to create a table to base the crosstab query on, but the
information just doesn't come out right. I was using the daily totals as the
value, but since it asks you to choose a function to use with it, once again
the information comes out incorrectly. In reading through some of the help
feature I have found mostly the same information and it really didn't help me
out. Any help would be appreciated. Thanks!
