If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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(). "Becks" wrote: 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. "Becks" wrote: 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? "Becks" wrote: 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. "KARL DEWEY" wrote: 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"; "Becks" wrote: 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. "KARL DEWEY" wrote: 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. "Becks" wrote: 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). "KARL DEWEY" wrote: 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. "Becks" wrote: 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! |
#12
|
|||
|
|||
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? "KARL DEWEY" wrote: 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(). "Becks" wrote: 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. "Becks" wrote: 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? "Becks" wrote: 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. "KARL DEWEY" wrote: 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"; "Becks" wrote: 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. "KARL DEWEY" wrote: 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. "Becks" wrote: 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). "KARL DEWEY" wrote: 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. "Becks" wrote: 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! |
#13
|
|||
|
|||
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. "Becks" wrote: 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? "KARL DEWEY" wrote: 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(). "Becks" wrote: 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. "Becks" wrote: 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? "Becks" wrote: 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. "KARL DEWEY" wrote: 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"; "Becks" wrote: 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. "KARL DEWEY" wrote: 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. "Becks" wrote: 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). "KARL DEWEY" wrote: 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. "Becks" wrote: 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! |
#14
|
|||
|
|||
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! "KARL DEWEY" wrote: 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. "Becks" wrote: 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? "KARL DEWEY" wrote: 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(). "Becks" wrote: 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. "Becks" wrote: 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? "Becks" wrote: 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. "KARL DEWEY" wrote: 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"; "Becks" wrote: 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. "KARL DEWEY" wrote: 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. "Becks" wrote: 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). "KARL DEWEY" wrote: 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. "Becks" wrote: 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! |
#15
|
|||
|
|||
Try taking the query apart a piece at a time until it works. Remove the
daily and try. remove the monthly and try. "Becks" wrote: 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! "KARL DEWEY" wrote: 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. "Becks" wrote: 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? "KARL DEWEY" wrote: 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(). "Becks" wrote: 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. "Becks" wrote: 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? "Becks" wrote: 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. "KARL DEWEY" wrote: 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"; "Becks" wrote: 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. "KARL DEWEY" wrote: 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. "Becks" wrote: 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). "KARL DEWEY" wrote: 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. "Becks" wrote: 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! |
#16
|
|||
|
|||
The totals stop working when I try to left join the yearly to monthly to
daily. If I just have the query run showing daily, monthly and yearly totals its fine. Can I work the crosstab, without left joining those properties or do I have to work something out to get to left joins to function in the right manner? Thanks again for all the help. "KARL DEWEY" wrote: Try taking the query apart a piece at a time until it works. Remove the daily and try. remove the monthly and try. "Becks" wrote: 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! "KARL DEWEY" wrote: 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. "Becks" wrote: 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? "KARL DEWEY" wrote: 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(). "Becks" wrote: 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. "Becks" wrote: 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? "Becks" wrote: 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. "KARL DEWEY" wrote: 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"; "Becks" wrote: 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. "KARL DEWEY" wrote: 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. "Becks" wrote: 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). "KARL DEWEY" wrote: 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. "Becks" wrote: 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! |
#17
|
|||
|
|||
Can I work the crosstab, without left joining those properties
Try it! Maybe a union query - I do not know how to do union query so start a new post. "Becks" wrote: The totals stop working when I try to left join the yearly to monthly to daily. If I just have the query run showing daily, monthly and yearly totals its fine. Can I work the crosstab, without left joining those properties or do I have to work something out to get to left joins to function in the right manner? Thanks again for all the help. "KARL DEWEY" wrote: Try taking the query apart a piece at a time until it works. Remove the daily and try. remove the monthly and try. "Becks" wrote: 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! "KARL DEWEY" wrote: 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. "Becks" wrote: 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? "KARL DEWEY" wrote: 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(). "Becks" wrote: 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. "Becks" wrote: 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? "Becks" wrote: 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. "KARL DEWEY" wrote: 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"; "Becks" wrote: 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. "KARL DEWEY" wrote: 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. "Becks" wrote: 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). "KARL DEWEY" wrote: 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. "Becks" wrote: 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! |
#18
|
|||
|
|||
Hi Karl!
I managed to get the left joins to work and have each of the totals to calculate correctly as well. Yet, when using the totals query as the basis of the crosstab query I don't have the information I wanted to create the columns and such. I thought that what would be best for our purposes is one in which the daily totals would each be displayed under its corresponding month. Then add a calculated field to calculate montly totals and then the yearly total. It has been mentioned that this is possible to do through a crosstab query, but through trying it out I am not sure of that possibility. How else could I go about create a totals sheets like that? Thanks for all the help. "KARL DEWEY" wrote: Can I work the crosstab, without left joining those properties Try it! Maybe a union query - I do not know how to do union query so start a new post. "Becks" wrote: The totals stop working when I try to left join the yearly to monthly to daily. If I just have the query run showing daily, monthly and yearly totals its fine. Can I work the crosstab, without left joining those properties or do I have to work something out to get to left joins to function in the right manner? Thanks again for all the help. "KARL DEWEY" wrote: Try taking the query apart a piece at a time until it works. Remove the daily and try. remove the monthly and try. "Becks" wrote: 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! "KARL DEWEY" wrote: 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. "Becks" wrote: 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? "KARL DEWEY" wrote: 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(). "Becks" wrote: 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. "Becks" wrote: 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? "Becks" wrote: 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. "KARL DEWEY" wrote: 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"; "Becks" wrote: 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. "KARL DEWEY" wrote: 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. "Becks" wrote: 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). "KARL DEWEY" wrote: 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. "Becks" wrote: 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! |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SQL query showing diff between actual and budget | Bon | Running & Setting Up Queries | 3 | August 25th, 2005 12:07 PM |
Crosstab query with irregularly-spaced dates | Carl Rapson | Running & Setting Up Queries | 2 | March 17th, 2005 10:42 PM |
Report based on crosstab query problem | [email protected] | Setting Up & Running Reports | 1 | February 18th, 2005 10:26 PM |
Crosstab query has more problems | Robin | Running & Setting Up Queries | 2 | January 20th, 2005 01:23 PM |
Showing all subrows in crosstab query | Ragnar Midtskogen | Running & Setting Up Queries | 3 | May 26th, 2004 08:16 PM |