I am trying to create a Membership report that shows member activity over several months.
My problem is that the software that gives me the Member Activity does not report on accounts that were not used during that month
and some members do not come in every month.
I have a Member's Details table as well as individual months activity and have queries set to show the results of each month individually (these work, obviously)
The Reports I am trying to get from Access only show the activity of Members that have some movement every month, but totally skips members that are not present for 1 or more.
The report uses these queries but fails to report any member that is not in the tables for all months.
Example:
September
Rank Member FirstName LastName Activity
1 100 Andy Todd 200
October
Rank Member FirstName LastName Activity
November
Rank Member FirstName LastName Activity
5 100 Andy Todd 50
The report needs to show: Member FirstName LastName Sept.Rank Sept.Activity Oct.Rank Oct.Activity Nov.Rank Nov.Activity
but will not because there are no values for that member in October.
I am teaching myself Access as I go, so I am sure there is an easy solution.
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
I have a query that i run and now I am adding another column to provide a ETA for an order, when i specify a date I only get results that have that date and when I leave out the date I get too many results. Can someone please let me know how I can modify my query so that I get all the results with an ETA and also all the accounts that do not have an order placed.
I can get the number of records to be returned but I want to run a condition if the records returned is zero. To do this, I need to put the number returned into a variable but I haven't been find a way.
For instance, this code does work:
SQL = "SELECT Count(*) AS Records FROM Table1 GROUP BY Table1.Quantity HAVING (((Table1.Quantity)=4));" conDatabase.Execute SQL
But I want to store the count as a variable. What I want is something that look this (it doesn't work but I just want to give an idea what I'm looking for)
SQL = "SELECT Count(*) AS Records FROM Table1 GROUP BY Table1.Quantity HAVING (((Table1.Quantity)=4));" Dim hold as Integer hold = conDatabase.Execute SQL
If(hold = 0) Then 'tell them nothing returned End If
So how can I go about this?
Update: I see that someone posted a solution a few threads down using queries which did work. Can I still still do the same thing with SQL strings?
Okay, I have a unbound form with three combo boxes the selection of these three & then clicking of a filter button opens the relevant form showing records based on the selection. Great that bit working, no major feat.
Trouble is when I select three criterea in the combo boxes that no record(s)exist for and hit the filter button. A blank form appears not very friendly or appealing.
what I'm after is a simple, thats me all over by the way, of a message box appearing to tell the user no records where found.
Hello all. I need some help calculating the total number of records returned in a query anytime i run the query.
I have a form that creates an SQL statement and changes the querydef at runtime and displays the results in a listbox. I want to include on my form a percentage of the total records from the table based on the records returned in the query Ex. (5/10) so I can use this information on a report.
What I am trying to ultimately acheive is to calculate an attrition rate based on the number of (clients that leave in a given period / total new clients added in the given period )
I am trying to use Dcount to find the number of records in a query and put it on a text box in the header of a report. The query is a parameter query. The button opens a form which asks for start and end dates, the query reads those dates from the form and the report displays the correct number of records that fall within the date range. The query is based on another query, which is based on the table. I tried =dcount("*","qryName") but I get #error. Maybe becauase of dates? or parameters?
All; using 2010. How can I get a subform to grow or shrink based on num of records returned? Its a continuous subform. Tried changing Vertical and Horizontal Anchors as well as some VBA suggestions but havent been able to make any of them work.
A bit of a weird one, I've got a query and the criteria for showing records is that one particular field is null. However the query is showing records with the values in the field chosen for the Is Null.
Not sure why this is happening, has anyone come across this problem before?
Been taking abreak from Access for a few years now and cant get my head round my problem.
I have been asked to manipulate data from a Training Recods database.
Basically, training consists of 26 modules, there are currently 180 people who need training. Each person has been assigned a unique number, once a module has been completed, the date it was completed is entered into the database. There are currently lots of modules which havent been completed, what I need is a way of pulling that data from the DB.
I need to know how many people need to complete each module. For example, Module 1 might need to be completed by 15 people, Module 2 might need to be completed by 27 people etc.
I have enclosed the DB with the table and the form that I am looking to populate with the data.
Anyone have any ideas, I just can't get my head round it!!!!
I know how to count records so that null records would be counted but I do not know how to do this:
I have customers and works. I want to create a query that will show ALL customers and number of works done for that customer (sometimes there is no work done for customer).
I have just joined the forum and spent the last day or so looking around for an answer that I wasn't able to find.
I have been asked by my employer to capture details of who has signed our confidentiality policy. I have made a very simple database which only has three tables, EMPLOYEE, POLICY and VERSION.
EMPLOYEE has three fields, NAME, NUMBER and BRANCH. POLICY has three fields, NUMBER, DATE and VERSION. VERSION has two fields, VERSION NUMBER and DATE. I won't worry about the VERSION table any further in this post as it doesn't figure in the problem query.
Primary key for EMPLOYEE is NUMBER. Compound key for POLICY is NUMBER and DATE (ie. the employee can sign the policy more than once and we want to record when this was, and whether the policy version changed).
I have an Access "type 3" join defined in table relationships from EMPLOYEE.NUMBER to POLICY.NUMBER.
I am trying to write a query that returns the employees that haven't signed the policy. I have no problem returning the employees that have (ie. employees whose numbers match the numbers in the POLICY.NUMBER field), but it is the EMPLOYEE.NUMBER entries that are not present in POLICY.NUMBER that are causing me grief.
I have tried using the expression builder in the query builder window (it gave me <>[tblPolicy]![Number]) which doesn't return any records (I have over 490 in the database and should be getting back at least 50 or so).
I have a form for booking employee holidays and in that I have a subform that displays their current holiday entitlment, which work great until I enter in a new record on the main form to book some holidays and I'm ask for a Name from a parameter box?? which I press cancel or ok at which point the subform goes blank (which is whatI want) but when I enter the employees name from the combo list it doesn't update the subform?
my question is; how do i remove the parameter box and update the subform once I have selected the employee name??
I recently acquired a database that is already established. When entering new date the primary key is null. I've looked at the properties and the best I can figure is it has something to do with a missing property called New Values.
Ok this null value thing is killing me. I have a parameter query that works great. I know if I use Is Null in the criteria field it will show me this. The problem is I made a search form which the users type the value in and it opens a form based on the parameter query, the parameter on the query is [Forms]![Search_frm]![txtClosed]. If they type a date in here it opens the form and shows the user all the closed records. The problem is they want to see all the records that have no value or Null. I've tried eveything to make this work. I'm ready to jump!! Just kidding. Is there a way to do this with out creating another query. Thanks a bunch!!!
Hi, I have a query which works perfectly and deletes the unnecessary records according to the criteria i have set , but i dunt want this null values in all column to display in the exported output file. how do i delte the records which are already null . i tried delete query but it did not work
Hi, I have a query which works perfectly and deletes the unnecessary records according to the criteria i have set , but i dunt want this null values in all column to display in the exported output file. how do i delete the records which are already null . i tried delete query but it did not work . This is in microsoft Access.
Depending on the answer of a question, i would like to get the records that have no data in that specific field, otherwise i want the query to give me all records. I've tried this in "criteria" but it doesn't work: IIf([Question? J/N]="N";"Is Null";"")
I have a form that using a combo box to select specific record, sometimes we need to see all records, i would like if the combo box if left empty to return all records. I typed this expression but it returns no records when combo box is empty.
If i use this expression i get the wrong results when a Loan Officer ID is enterd; LIke([Forms]![FrmReports]![LoanOfficer] & * Example of wrong results, if Loan officer Id is 1, then it returns loan officers 1,11, 111
I am very much an Access novice, and use the expression builders for my queries. I have a query based on a table and another query. The table lists station names and station numbers. The "base query" includes station numbers plus other data, so I join the two in order to display the station name.
I want to run a query that lists every station by name, then for each station tells how many occurrences of a condition are met in another field for that station. Problem is, if the condition is not met, the station is not listed in the query output, and I need to see the complete list of stations, even those that do not meet the condition.
Here's what i would like to get if there are a total of 3 stations in the population:
Station Name #of Blue Items Station 1 100 Station 2 1 Station 3 0
Instead, I get:
Station Name #of Blue Items Station 1 100 Station 2 1
I'm working on a db logging replies to a questionnaire. Judging by the responses I am receiving it appears that some sections are not completed at all. I need to be able to include these blank responses when it comes to analysis. My design splits the questionnaire into sections, each section has a data entry form with its own underlying table. Each table has a primary key (autonumber field) which relates to each organisation that has replied. If an organisation has failed to complete a section, I still need to create a new record in that section(table), triggering the autonumber field, hence referring back to the organisation. I know I haven't explained this very well, but if anyone can make sense of what I'm saying and can give me any suggestions on how to make this happen, I would be most grateful.
