I use MS Graph and need show 2 data types:
1. Invoice Value, EUR
2. Date
Date axis group records by month, for Example Period from 2005.01.01 And 2006.01.01 has 12 positions in Date Axis.
Her I have problem: I need show sum of Values for every month.
For example in Period from 2005.01.01 till 2005.02.01 I have 3 Values: 200, 500, 600EUR. Her I need to show 1300EUR, and so every month. Her is my strandart code:
Dim strSQL1 As String
strSQL1 = "SELECT Date As Date, Value FROM Table WHERE (Date BETWEEN 2005.01.01 And 2006.01.01)"
Me!chrStatistik.RowSource = strSQL1
I have a form with a chart , 2 textboxes for start date and end date and a button to filter the data( filter the data by date range) How do i continue from that? The chart isnt updating.
I have a database that tracks students and their test scores. I am trying to figure out a way to where it will pull the student's latest test score and compare it to see if they fall within standards. So far I have 3 tables.
The query I have written looks like this: SELECT tblStudents.studentID, Max(tblResults.resultDate) FROM tblStudents LEFT JOIN tblResults ON tblStudents.studentID = tblResults.resultStudent GROUP BY tblStudents.studentID." The data pulls just fine at this point. What I'm trying to figure out is how to then add more columns to this query to start doing comparisons. When I try to add more columns it tries to use them for additional grouping and adds many more records.
I have a rates table with 3 fields : [RateDate], [CurrencyID], [FXRate]
I also have a currencies table with 2 fields : [CurrencyID], [CurrencyCode]
Where [CurrencyCode] is just the 3-character currency code (i.e. EUR, GBP, USD etc.)
The rates table uses a composite primary key across [RateDate] and [CurrencyID] - i.e., there should only be one unique record for each combination of date and currency.
Not every date will be present in the table and, within each of those dates, not every currency will be present (but at least one, obviously)
I want to write a query which will return two fields. The first field will contain all of the unique dates in the table for which the [FXRate] field (for any currency) is blank / null. Straightforward enough :
Code: SELECT [tblRates].[RateDate] FROM [tblRates] INNER JOIN [tblCurrencies]
[Code]....
But this gives me a separate record for each date / currency combination in the returned dataset. I just want one record per date and all of the currency codes in a single string (separated by some delimiter) as a text field.
I'm aiming to create a stacked area chart to display the progress of multiple projects over time, so we can review and forecast project load over time.
A project passes through the statuses Prospect, Confirmed, Started, Complete. The current status of each is calculated in qryStatus from the dates in tblProject. The past and forecast statuses for each project will probably be calculated each time the chart is created (or maybe not necessarily so?)
To create the stacked area chart, the x-axis will be months (e.g. Sept 2013 - July 2015). On the y-axis will be the count of ProjectID, and the series will be the different project statuses.
The sticking point is getting from the Date* fields to the past and forecast statuses in each month. After that I imagine it'll be reasonably straightforward to put into a pivot/chart.
I've created an access chart to show total sales ordered by customers.
I'm using a form with 5 comboboxes to select 5 particular customers from a customer table and pass this information into a query.
This query is then used on a report to create the graph.
Finally there is a button on my form that prints this report.
My problem is that the chart displays the customers in alphabetical order and I would like to order the customers numerically based on total sales value.
If I change my query so that the total sales order by is ascending then when I run the query from the report I am asked to enter a parameter value for the total sales field. Entering nothing and pressing ok simply returns the same graph I would have got had I not changed the order option at all.
*The customer filed in the report is set to group by and my totals field is set to sum - but I need both of these set to produce the graph.
I would like the user to be able to select the months he wishes in case they want to look at calender year, financial year or just a custom group of months. If I use the wizard and pick dates say the start and end of the year I get the following code in the row source of the chart control:
Code: SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] WHERE ([DatePaid] BETWEEN #01/01/12# AND #31/12/13#) GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));
So I decided all I needed to do was replace the dates in the above code with my own global varible which i would pass custom dates into via a form. Which I called getds() and getde()
Code: SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] WHERE ([DatePaid] BETWEEN >=#getds()# And <=#getde()# ) GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));
I'm trying to create a chart in a form, this is not a problem, however, the chart types available are a bit limited compared to if i inserted a Microsoft Excel Chart object.
I'd do that except I'm trying to create a chart based on a query.
Is there a way to make the chart wizard use the Microsoft Excel Chart object as its chart creator so I have access to the chart types available in that object?
I am using Access 2007 and I have had a curious issue arise.
I like to use the Date Picker feature on all my forms to make it easier to enter dates. I have set up each table's date field property to "show date picker for dates", as per attached (.jpg)
When I use that particular field in a form, I also set up the field to allow for the Date Picker option (date picker form property sheet.jpg)
The curious part is that not every form that I set up actually shows the date picker when I select the date field. Some forms it works perfectly, others it doesn't.
I want to make a church database grouping families together and identifying each persons position in the family while still having each family member with an individual profile, i.e. birthdays, anniversaries, position, classes, memos, possibly donor info.
So i'm not sure where this post belongs as it may encompass several items. I have an excel sheet where every row has an email address and a product purchased. I've imported it all into access but would like to run a query to find out how many people that bought x purchased y. Or tell me how many people that x did not buy a,b, or c.
Part of the problem is that each record is one product. My data looks like this:
I was able to group some of the data by email address in access but can't figure out a good way to query it. I'm thinking I probably need to merge the data where each unique email address is a record and the products have all the products purchased instead of just one but I really have no clue how to do that.
Is this easily do-able? how would I go about this? Thanks alot!
Ok, I'm missing something simple here I'm sure, but I can't see the wood for the trees at the moment.
background guff What I have is a system tracking actions being undertaken. There's an SLA for these actions which means they should be completed within 10 days. At the moment we have no reporting on whether or not we're meeting this SLA.
Now obtaining the information for all the data this year is fine and dandy and works ok. Where I'm having problems is doing a monthly breakdown for the ytd.
I'm using the following query to give me my raw data: SELECT [Parent Table].ID, [Parent Table].[Date entered into database], [Parent Table].[Leave Date], [Parent Table].DateCompleted, DateDiff("d",[leave date],[datecompleted]) AS DaysToCompleteFromLeaving, DateDiff("d",[date entered into database],[Datecompleted]) AS DaysToCompleteFromEntered, DatePart("m",[leave date]) AS [month] FROM [Parent Table] WHERE ((([Parent Table].[Date entered into database])>#1/1/2007#) AND (([Parent Table].[Leave Date])>#1/1/2007#) AND (([Parent Table].DateCompleted) Is Not Null));
Which gives me the various dates, the number of days it took to complete the record from when a person left the company ,from when their information was loaded into the database and finally a number for the month.
I have a query that happily gives me the average completion times on a monthly basis:
SELECT Avg(CInt([DaysToCompleteFromLeaving])) AS AvDaysFromLeaving, Avg(CInt([DaysToCompleteFromEntered])) AS AVDaysFromEntered, [Completed Leaver Dates].month FROM [Completed Leaver Dates] GROUP BY [Completed Leaver Dates].month;
but I seem to be having a great deal of difficulty specifying criteria on a Count to show me the same breakdown.
I can get a total count of records per month: SELECT [Completed Leaver Dates].month, Count([Completed Leaver Dates].DaysToCompleteFromLeaving) AS CountOfDaysToCompleteFromLeaving FROM [Completed Leaver Dates] GROUP BY [Completed Leaver Dates].month;
But what I want to do is split that number into two columns, records where the completion date was >10 days and records where the completion date was <= 10 days which is where I'm having some problems.
Putting a critera in design view for the count field still returns the total number of records per month and returns the following SQL query: SELECT [Completed Leaver Dates].month, Count([Completed Leaver Dates].DaysToCompleteFromLeaving) AS CountOfDaysToCompleteFromLeaving FROM [Completed Leaver Dates] GROUP BY [Completed Leaver Dates].month HAVING (((Count([Completed Leaver Dates].DaysToCompleteFromLeaving))>10)); I'm fairly sure it's in the HAVING clause, but I'm not sure what I'm missing.
name, project, total_Hours_worked (only if total is >45) for a week by each employees. the query should contain total hours for all the weeks. The table contains week_ID column and dyas worked column. i can create a query which gives me sum of day_hours worked using selecct sum(day_hours) as Total.
but i badly need total sum of week hours worked by each employees. so that i can generate overtime report grouped on weekly who has woked more than 45 hours per week.
the table is attached. please see and help me if you can.
Hi again, my problem is that on a report I have a row of dates i.e. 10/10/2006, 11/10/2006 etc. However, people would prefer to see Monday, Tuesday etc. Is there a way to show the date as the day name. The date fields are all based on the Date() expression then +1, +2 etc so are always changing.
I have a report in ms access which looks at a table. In the table i could have the same person 2 or 3 times BUT with different dates. How can i get only the first date(earliest) to show in a query.
OK, first time posting so I'll try to be clear here. I work for a special education agency and have created a database to track student/therapist information. Now, I have reports showing which kids are in which schools and who their therapists are. What I can't figure out is how to print a report, grouped by school, that will display which therapists are active in that school.... any ideas?
I am having trouble writing what I think should be a fairly straight forward query... I am have a table with 2 fields, the first contains a list of subjects, the second contains a list of scores that students got in their tests in that subject.
So the data looks like this:
Maths 54 Maths 69 Maths 41 English 71 English 55 Art 44 Art 43 Art 66
I would like to write a query that selects only the highest score for each subject and presents it like this:
Invoice Number | Customer Name | Item | Item Cost | Invoice Date | Paid | Date Paid
Example of data:
AK001 | A Brown | Blue Car |1000 | 1/4/2013 | Yes | 20/4/2013 AK001 | A Brown | Red Car |2000 | 1/4/2013 | Yes | 20/4/2013 AK001 | A Brown | Yellow Car |500 | 1/4/2013 | Yes | 20/4/2013 AK002 | A Brown | Black Car |1000 | 7/4/2013 | Yes | 20/4/2013 AK003 | B Smith | Blue Car |1000 | 12/4/2013 | Yes | 25/4/2013
I want to create a report from this table that outputs as:
Invoice Number | Customer Name | Total Price | Invoice Date | Paid | Date Paid
Example of report from Example Data:
AK001 | A Brown |3500 | 1/4/2013 | Yes | 20/4/2013 AK002 | A Brown |1000 | 7/4/2013 | Yes | 20/4/2013 AK003 | B Smith |1000 | 12/4/2013 | Yes | 25/4/2013
Is there an easy way to do this.. or will I need to make a new linked table with the invoice number as a lookup?
I'm creating a database of companies. Every company has at least one field of services they provide. There are three main groups which contain all the services (Let's say I have three tables).
What I need is to have a form/table (query?) where I can assign to a company any number of services from any group. Is it possible to do it in a form where I could simply add a service by choosing a group first and then a proper service, and then add more in the same way?
This probably is a very stupid question but I am a new user and have been pulling my hair out for a week over this. I have created a report and linked it to my table but it generates a list of all of the values in my table on the report. What I want is a way to generate a report page for every row in my database table grouped on a key record that is unique to each data row. If anyone has any advice it will be much appreciated, thanks.
I have simple table data structured as follows;[Origin], [Code], [Weight]. The Code field is a lookup field that will contain one of 8 choices; 10, 13, 13c, 23, 25, 27, 27a, & other. The other fields are pretty much self explanatory. Sample data would look like this:
What I need to create is a totals query where I first group by origin value, then a column for each "code" value which totals the weight for that "code". A sample output would look something like this:
The only way I can think to accomplish this is to restructure the original table to include a field for each code and enter the weight in the appropriate 'code' field. If this is the only solution then Ill have to live with it, but is there any way to create this output using my original structure?
