Queries :: Create A Statistics Function On A Database
Jun 15, 2014
I am trying to create a statistics function on a database. The idea is that the user will enter a start and end date and either search for all records during that date range or select a client from a combo box and only view records for that client during the selected dates.
After doing quite a bit of searching, it seems that I should be using a wildcard in the criteria however I cannot get this to work. The code I have been playing with is:
Code:
=Nz(Forms![Statistics]![ClientCombo] ,"*")
I have changed the "*" to a specific client number and if the combo box is left blank, results are shown for that client only and if a client is selected from the combo box then the selected client is shown. The only thing I cannot get it to do is show all entries if the combo box is left blank.
I have a database which has numbers for different statistics and i would like to be able to search, for example, the past 10 weeks and find out how many time a certain number has been recorded.
I am trying to , create a field using the IIf function that will display a $250 bonus for agents every time they sell a car for at least $20,000. Otherwise, the function will return a 0. Name the field Bonus. I put in Bonus: IIf(SalePrice >= "20,000" Then + "250",True, 0) and it is saying that I have an improper syntax.
I am hoping to do a report all my queries in my Database.
I have managed to use some sql code to list the queries which is the following:
SELECT MSYSOBJECTS.Name FROM MSYSOBJECTS WHERE (((MSYSOBJECTS.Type)=5) AND ((Left([NAME],1))<>"~"));
But i cant seem to get any further i would also like to include the creation date, the description if any, the tables associated with each query, as i know i have a good few queries which maybe duplicates i just want to clean it up.
I am trying to do a lookup from a field from one of my databases (DB1) onto another field in (DB2).... I have been using excel to do vlookups but a report that I do weekly I do atleast 7 lookups for the past weeks... So I am trying to find a way in access to do lookups and save those lookups each time i run the report... Do I have to create the query and make the ID the primary key on both the databases and then create a relationship?
I worked yesterday to create a database consisting of a table, multiple querries and a form. Today I went to open it and I got a different database I worked on months ago. The properties say it was created yesterday, but it is not the same database and I can not find the database I created yesterday.
I will try to explain my problem as best i can and would appreciate any thoughts other people have on it, it is surely similar in some degree to someone elses previous work!
I need to produce management information on a monthly basis, one example of this type of work are an employees one to ones.
table121 contains following fields, ID scheduledDate CompletedDate Completed(yes/no)
My report/query needs to group records by the month (which i do through formatting date fields to display mm/yy), count the number of scheduled one2ones, count the number of completed one2ones, display a %.
I have played around and got this to work using querys with grouping and sums.
My problem is if the schedule date and the completed date are in different months then all of the statistics become out of sync, particualy when there are more appraisals taking place than scheduled. Any ideas?
I'm trying to find out the statistics of my dabase:
# of total items # of unique items from 4 different criterias
When I put that into the queries, using the count function, it works well for 1-2, and then if I add in more into the query, it gives ridiculously high numbers for the counts, and freezes. Am I doing something wrong?
I am looking to come up with statistics for my volunteer tracker. I have a table of transactions that records who works each night we are building our haunted house. These transaction records record the Date, Worker and the Time In & Time Out. I would like (probably a cross tab query) that lists all the dates in the left hand column, and count the number of workers in another column, then the number of man-hours put in for that date. Kind of like this:
Sorry if this is a question asked a lot but I need help with statistics in acces.. Im doing a booking system in access with customer and booking/bill tables. I have an IF statement to work out price (in a query) for the booking or which displays error if booking cannot be made. The query runs when a button is pressed in the form. The booking price is worked out by time (morning, afternoon, evening) and special (gold wedding anniversary, extended evening etc) which change the price. The date and booking time fields are set together as primary key (composite or whatever key...). The system works monthly. I need a system to find out how much money has been made, how many bookings have been made (and how many could have been made), how many are regular bookings (there will be a regular booking yes/no field). I have probably given too much information but I need to know how to do this more automatically then copying and pasting info into excel and doin equations in it. Also in excel I would just have to presume month = 30 days or manually type in. Is there any code to copy the data into an excel spreadsheet with predone equations automatically? or is there a better way to do this? that isnt too difficult. I have only just started looking at VB so dont know much. PLEASE HELP! PLEASE!!!
I have a database that makes use of standing orders. That means that if a client has a standing order to receive products during for example 4 time as year (quartely at the end of the month). to automate the new entry by copying an old entry in the database.
Let's say I have a client where we will have to send a product at the end of June, it will look at a field where the next send date is, and when it reaches 2 weeks for that date, to create a new entry in the database based on that entry. This way, it will pop-up in our open cases and we are aware of it and also will be visible in our report.
This is either very simple or very complex, I haven't figured out which yet.
I need to know the number of tables in my database and from each table I need to know how many records are in each table. Ordinarily I would just count the number of tables then open each one up to get the number of records, but I'm working with 100+ tables so that's not very practical.
If it makes a difference, the tables in question are linked tables. I don't imagine that is relevant, but thought I'd mention it.
Is there a way of formulating statistics at the bottom of a report?
Heres what i have.
The report pulls Rank, Last Name, First Name, Assigned weapon, Weapon qualification date. After 6 months i use conditional formatting to highlight the soldiers qualification date red. Im in the military that's why im tracking all this, but I need figures to report to higher, and at the bottom i would like it to show, "#Qualified", "#UnQualified","% Qualified", "% Unqualified", "#Expired","%Expired"
I am building a database to enter staff phone statistics. As an example my fields would be - Name, Date, Staffed time, Available time, Aux time and then calculated fields to show the percentage of time i.e %Aux, %Available etc.
My problem is the formatting of the times entered as they are duration not time. Say staffed time is entered as 08:00:00 for 8 hours and Aux time 03:57:21. The only format I can see to suit is date time but then Access takes these entries as 8am and 3:57am is there a way to change this to work as duration hh:mm:ss?
Details: I have a Profile form that tracks the expiration date for each client's various certifications. These dates are set up in the Short Date format in the table design of Access.
Problem: When a user accidentally presses an alphabetic key while updating an expiration date, an Access error message is triggered. This is confusing to my users as these messages are written in Access lingo. I would rather that nothing occurs at all. I wrote a case statement to disable each letter of the alphabet and applied it to the On Key Down Event for each expiration date control on my Profile form to solve this problem, but this must be applied to 28 separate controls. I would rather call a function that disables alphabetic keys for each date control in my form when called.
Questions: How do I transform my Disable Alphabetic Keys Case Statement into a function that I can call for each expiration date control? I know that when writing a function certain variables have to be declared and/or initialized.
Also, will I need to create a function to re-enable alphabetic keys or is this unnecessary because the disable alpha keys function will only be called for specific controls, not the entire form?
What I Have Tried: I have tried copying and pasting my Disable Alphabetic Keys Case Statement into a module to attempt to create a function, but it needs work.
Below I have included 2 types of code: (1) The original On Key Down code applied to each date control on my form (2) The same code written as an attempt at a function
Original Profile Form Code to Disable Alphabetic Keys in the On Key Down event for each date control
Private Sub txtCert1ExpDate_KeyDown(KeyCode As Integer, Shift As Integer) Select Case KeyCode 'All message box text is for me to test the code, not for the user to see Case vbKeyA MsgBox ("you pressed the A key")
I am testing the security of my DB X on Acccess 2007. I could create a new database Y and linked to the database X. Unfortunately i could change the records on the tables. I don't want other DB that make connections to my DB to change my tables!
I created a database with forms, querys, etc. Now I was thinking of creating a exe so everyone can use this database without having access to all the design functions.
the only problem I see about doing this is if we need to use the same database for different project, we need to always create a new exe for each project.
Is there a way to make a empty database exe with just the layout, querys etc but with no data on the tables and have a save, open and save as option. So we can have several projects using the same database?
I was thinking to create a VB code for the database to delete all data on the tables when the database is open. and before closing the database exporting all the data to a txt file or something.
Then the next time someone opens the database goes to the main form which has a open bottom which imports the txt file into the tables.
This way we can have several txt file for several project and use always the same exe database.
I have a database in the database I have products customers orders etc.
In Products table I have productid price etc. I would like to create another table and this table should consist of all product parts for example.
ProductID x uses metalnumber23 stone45 plasticnumber43
so all metalnumber23, stone45, plasticnumber43 will be in Parts (new table that i will create) when i am entering a productid to Product table for that productid i should be able to choose which parts from parts table that it uses.
so in the order table if i customer ordered productID x I should be able to see all the parts that ProductID needs in the parts Parts table. There will be maybe 2000 parts but ProductID x will use only 3 of them. And if I create a query lets say order from z date to y dates I should be able to see parts needed for those products ordered. hope you guys understand and give me a head start i would really appreciete
I need to create a database for students going on work experience.
Each of the students will have a rating from 1-6 depending on behaviour, effort and attainment. Each of the work placements also has a rating from 1-6. Each student chooses 6 work placements that they would like to attend (one of each rating)
If all the data for the students and the workplacements are entered into the system is it possible to match appropriate students to appropriate workplacements using MS Access 2003?
I am running an autocompact module in Access97 which will created a 'compacted version' of my database. I then want to rename this compacted version to the name of the original database I compacted. I cannot however find a way of doing this in code.
Basically the idea is to compact every 45 minutes or so, but what my autocompact is doing is creating a compacted version under a different name, and so compacting a copy of the database, I then want to change this compacted database name to the original name and re compact every 45 mins
I was wondering if there was a way to log all the modifications made to a database in a table with user name and time of modification. I have been searching this for quite a long time but no use. Please help if you know the solution.
I'm trying to create my own database from queries. the queries retreive data from and ODBC data source. How do I create table from query but I don't want any old data from table to be deleted whenever there's a change of data in ODBC data source. I just want to keep pushing in new information without deleting the old ones, even though those old information have been deleted from the ODBC source.
