Hello,
I have a single membership table that contains two separate date fields,a "StartDate" when a member joins and an "EndDate" when an individual cancels their membership.
I would like to query this table to provide a list of active members between a given range of dates.
For example if the date is set to 1/1/06 through 1/1/06, I will get a list of all active members on that particular day.
Any help is greatly appreciated. Cheers.
I am trying to get active member count by month in MS Access.
Table looks like this:
admin ID firstName sDate eDate 1 Adam 01/01/2015 01/04/2015 2 Ben 01/02/2015 15/04/2015 3 Carl 01/02/2015 13/03/2015 4 Don 13/01/2015
Required output would be like this
2015-Jan: 2
2015-Feb: 4
2015-Mar: 3
2015-Apr: 3
2015-May:1
etc..
I got this far:
SELECT DISTINCT count(a.ID) as mCount, year(dimDate.Date) as [Year], month(dimDate.Date) as [Month]
FROM admin AS a INNER JOIN dimDate ON (dimDate.Date >= a.sDate AND dimDate.Date <= a.eDate) OR (dimDate.Date >= a.sDate AND dimDate.Date = null)
GROUP BY year(dimDate.Date), month(dimDate.Date)
ORDER BY year(dimDate.Date), month(dimDate.Date)
Query4 mCount Year Month 31 2015 1 71 2015 2 44 2015 3 13 2015 4
I have a query where I am trying to count the number of members who have joined during a period of 12 months from a start date sept 01 to following August 31. Instead of getting the total number of members I am getting totals for each member type. I have 12 member categories identified by [TypeID] but each member has a MemberID (Autonumber). I use Sum in the MemberID Field in my query by pressing the Sigma Button. When I run the query I get totals for each TypeID instead where I wanted one final Total. I suppose I need to count the records bearing in mind some numbers have been deleted so numbers jump in the Auto Number field. This is quite asic but I cant get it right. Help appreciated!
View 7 Replies View RelatedFirst I'd like to say I've learned a great deal from all of your posts. I registered today because I'm having the strangest problem.
Let me tell you that I am in no way a programmer, I don't know any code except some basic html. I was asked to take a 2 day class to learn Access (which was a joke) and then build a database for my employer. I think I've accomplished what they need by using every available book I could get my hands on, forums like this, and the help menus.
Our database is very simple. We only have 3 tables, all of which are related by a field we call our "referrer code".
My problem is this -- we've noticed that some (6)of our records are gone. I have no idea why, or when. We run a roadside assistance company. The database keeps track of members. I can't afford for Access to just drop people. The tables are very small, we only have 1,841 members. There isn't any specialized coding, because like I said, I don't know any. I've mostly used wizards to design.
Does anyone have any idea what, if anything, I should look for. I can send or answer any questions that might help you help me.
I would appreciate anything you have to offer -- even if its just a place to read something that might help.
I am constructing an Access database and wish to get all the members from a particular OU into a table.
Our Domain name is CORP. The Active Directory Users and Computers shows our domain as "corp.
Under this, there is a folder called "Accounts_User" and under that a folder called "Site-SYD" - This is the OU I want get get all the members from?
I have registered members and groups in my access database and i want to create a search form that enable me find and edit registered members of groups.
View 3 Replies View RelatedHi all,
My database is a simple membership database that adds members and by clicking of a button it tells me the memebers birthdays for the month and if i chose to select the send card option then it remembers who has been send the card.
All i want now is a query that will take a member who's memebrship has been expired (since membership is year long only) and move them to another table by looking at the membership dates (like 2004 and 2003).
And may be later on if those members wants to join the membership i can then add them again from that table back to the main table.
I hope i made my self clear enough.
Thanks alot
i have a table calle "tstaff" and one of the fields in it is named "picture" which hold a link to the picture of the staff membe, the picture is stored on my pc, not in the database.
so in the picture field would be something like this...
Picture
c:documents and settingsall usersdocumentsmy picturessample pictureslue hills.jpg
what i am trying to do now is to create a report that displays all the patients data, including the actual picture that the link refers to...
how should i do this please
cheers
Andy
I have created a database containing customer records and i wanted to somehow add a part where an individuals records will automatically be deleted if they are not active for 3 years+
View 6 Replies View RelatedI am putting together a "Clients" table, what is the best way to handle Clients with the same name.
For example:
Here are a few fields from my "Clients table":
ClientID
Fname
Mname
Lname
DateOfBirth
I was thinking of making a unique index for first and last, but then i thought, what if I come across 2 John Smith's?
I am sure there is a professional way of handling this, but Im not sure what the norm is.
-SHould I make a unique index for Fname, Mname, & Lname?
-SHould I make a unique index for Fname, Lname, & DateOfBirth?
I would like to know what is the norm when storing people in a table and the best way to prevent duplicate clients from being entered.
Hello,
i am creating a database for a membership of my martial arts academy, so far i can add and search members.
i would like to have a mail merge feature on this, so that i have a list of all the people with a check box next to their name, if the box is ticked their name is added to the mail merge. if its not ticked its not added.
All help is much appreciated.
Aaron.
Hello,
I have a query that lists members who have outstanding payments. The results of this query are being used to populate a winword mail merge document.
The problem with this query is that if there are multiple members from the same family who owe then I will get an entry for each of them in my query results. What I would like to do is add the amounts due so that I only have 1 entry for that family. Additionally, since it is possible that more than 1 person can have the same last name and not be related I would also like to have the query check the address field and if it's the same than add the amounts due. The following is the sql statement for the current query.
SELECT Members.FirstName, Members.LastName, Members.ParentsNames, Members.HomePhone, Members.PaymentDue, Members.Active, Members.MemberDues, Members.FamilyAmount
FROM Members INNER JOIN MemberTypes ON Members.MemberTypeID = MemberTypes.MemberTypeID
WHERE (((Members.PaymentDue)=Yes) AND ((Members.Active)=Yes))
ORDER BY Members.LastName;
Any assistance would be greatly appreciated.
Regards,
Chris
anybody help please
I have list of member in the member table, every month each member makes a payment (monthly fee) as they do they get entered into the fee table how do I find out the members who have not been entered into the fee table (which will show they have not paid )
thanks
I have a kids club which do lots of functions for kids, to monitor the members and who is in and out I did a small access data base with details of Start date of the function and end date for each member.
I want to make a daily report showing only the active members in today date only for example:
Jack have swimming every Saturday, Monday and Wednesday Between 14 July 2015 till 14 August 2015
Jill have swimming every Sunday, Tuesday and Thursday Between 14 July 2015 till 14 August 2015
today is 11 August 2015 and I want to report to mention just who is active today only.
I have a kids club which do lots of functions for kids, to monitor the members and who is in and out I did a small access data base with details of Start date of the function and end date for each member.
I want to make a daily report showing only the active members in today date only for example:
Jack have swimming every Saturday, Monday and Wednesday Between 14 July 2015 till 14 August 2015
Jill have swimming every Sunday, Tuesday and Thursday Between 14 July 2015 till 14 August 2015
today is 11 August 2015 and I want to report to mention just who is active today only.
I have a members DB that apart from full details also show payments of yearly subscriptions. I am trying to print a report that shows a list of all the lapsed members. sounds like a simple simple report, BUT...I have a table that includes male and female members on one club number, idealy man and wife, but if for some reason the part company one may not pay the others subs those making the other a lapsed member. is there anyway i can print one part without the other?
E.g. this is haw it prints now
Male Name Paid Female Name Paid
J Smith No M Smith Yes
and this is what i am after
Male Name Paid Female Name Paid
M Smith Yes
Using MS Access 2000
I'm building a customer management database. Part of which is a table to record details of all members of the main customers family or the household compliment.
I'm currently using a DCount to work out how many people on the table are related to my client, to work out the size of the family.....
=DCount("MemberID","tblHHMembers","CustID = [CustID]")+1
Could I also then count the members of the household that are under 16 years old by looking at the DoB from the same household members table?
I realise that I'd need to ask access to calculate age at the same time as working out if they're under 16 and then count them if they have the same Customer ID - which might need a little more than this single function!
I have a query to count the number of society members in a particular UK Postcode area. It works well for a single Postcode reference but adding further references to the Query Design View generates an 'or' or 'and' statement, rather than the additional counts that I need.
The SQL code I use for a single reference is :
SELECT Count([Mail List].[PostCode]) AS CountofPostCodeH
FROM [Mail List]
WHERE ((([Mail List].PostCode) Like "BN5 ???"));
How do I amend this code to produce counts for multiple references in one query.
I have a pop up window that maximizes and the need to format a Memo Field to users specification.
Unfortunately the Mini Popup Bar that comes up when you select text is very shy and annoying when it doesn't show up. It's Completely Unreliable.
So I thought I'll make the Controls on the form for Bold,Italic... etc but I can't seem to get the code to work.
My TextField is a Memo and I have changed the TextFormat to RichText, but I cannot use any of the commands like .SelBold or .SelColor etc.. It gives me a Compile Error: Member or Data Member Not found.
I don't understand why? Is there a Add In/Reference that I need to include?
Is there any way to add a dropdown box or something that determines that a subrecord is active/completed, and then have a way to jump straight to the one that is active by clicking a button on the record? In the example I attached, I want to be able to click on the button above the tab and have it switch to that tab and open the first record it finds that has "Active" selected.
View 1 Replies View RelatedHi Again guys,
I have a database application, front end on the local machines, back end on a server (it happens to be a mac server, but still works). Recently we upgraded to Access2007 on all systems, but left the backend in 2000 format. Ever since then we have been having issues with our users getting told "Too Many Active Users" when they try and access the backend. If all users disconnect and the reconnect it seems to be fine for a while, then after sometime it does it again. I assume it has something to do with the ldb file on the backend. It worked fine in 2003, now I am stumped. It possibly has something to do with permissions but I have no idea what.
Thanks for any help.
I have application made in MS Access 2000
I got crazy with one Active X Control on one PC. On every other PC works fine but on this one I have error "Object is missing or does not exist".
I tried delete - copy - paste couple times, unregister and register control couple time, but error is still there.
Reference looks fine, I put control on the form and everything looks great.
I tried delete control from the form and put again.
When I try to assign some data to the control I have error "Object is missing or does not exists".
It's look like my Access programm see just as read only or something like that.
Does anybody have idea why my Active X Control does not work on this PC?
I am trying to get the name of the active subform in an Access database from VBA code.
If i use screen.activeform i get the name of the parent form and not the subform.
Any ideas how to get this ?
Many thanks in advance.
Andrew
Hi All
I have a small Help Desk db with tables that contain computer spec and user information. But I already have all this information in Active Directory and it seems pointless entering the information twice.
Is there any way i can get the database to lookup the information from Active directory i have no idea where to start.
Thanks
I have built an automated procedure in VBA Access 2000 that is now great (thanks to a lot of downloaded help from this forum). I use the MSCOMM active-x control to talk to an instrument via the serial port.
Question: Is that control only working because I also have VB6 installed on this same computer? It seems to me that I tried something like this a year ago and could not get MSCOMM to work in Access, but at the same time, I didn't have great MSCOMM examples to go by. I do now as I could rob them from other programs my company has written in VB6.
I have both VB6 and Access 2000, but chose to develope this procedure in Accesss because of the heavy amount of data to store (and I know VBA much better than I do VB6). I thought Access is a natural. But now I am wondering if I will have problems distriubuting this database to other service offices that may not have a machine with VB6 on it?
Do they need just plot MSCOMM32.OCX in to their system32 directory?? :confused:
I have added an Active X control to my form. The active X control is Inventor View which is for viewing 3D drawings. At the moment I can get it to work and show one particular drawing using a filepath put in the filename property.
However, I want to save the filepath for each record in the table and then change the filename property of the Inventor View to this according to the record that is open.
I have tried searching for similar threads but still can't quite get round it.
What data type should I use to store the filepath and how can I get it to change the filename property of the Inventor View control.
Thankyou in advance!