Ok the database that i have contain all the details about customers including thier joining date. I want to make a query that will produce relevant information for use on a membership renewal letter, It should show only members due for renewal on 1st of may
(there are 3 renewal dates per year. 1st jan , 1st may and 1st sept. I need to assume that the next renewal month of membership for those joining between those months will be the next renewal date. Example if you join in June your renewal date would be 1st september)
Could you please explain how i could do this in easy to understand language as i am no expert with access.
I'm designing a database in which the primary key is a combination of the first letter of first name, the first letter of last name and the date of birth.
ok here is what i want.. imagine a search box, just an empty text box that allows the user to type things into it...
there will be a simple table from the database behind it, here is the typical structure of a record
<Country>Poland <Price1>60 <Price2>85 <Price3>95
in this table there will be say a 1000 of these records... my user is going to have to quickly search through these records while on the phone to a customer, and quote one of the relevant prices associated with that country
what i would like is this most convenient system.. as the user types in the letter 'P' just below all the records with countries that begin with 'P' are displayed (with a scroll down arrow if needed)..as well as the 3 relevant prices with that record
if they then type an 'O' into the box (which will now hold 'Po') all the records with countries beginin with 'Po' will be displayed (eg Poland)
if they delete the 'o'. once again the displayed records below the box will return to just the countries begginin with 'p'
all that is needed is for the records to be displayed, thats it.. but the adding and deleting of the letters within the text search box will need to instantly manipulate this list..
i have posted on other forums, and have been told this can be acheived in access, is it hard?
Hi I have been asked by my tennis club if I could set up a small Access database to help with membership registration and fee collection. Ideally, it would go on to record coaching sessions for juniors, etc but that can come later. Does anybody have a template that I could use as a basis for my development? Regards Noel
Hiya... I have a form where new records can be added to a table. I was wandering if it was possible to automatically add 1 to a textbox that is bound to a membership id field within that table.
So when the form loads, the txtmembershipID has the next number displayed.
This is my code for the load event. DoCmd.GoToRecord , , acNewRec
Hi, Im putting together a membership data base. the data base has an already existing client base which all have membership numbers in the form of first letter of there sir name, last to numbers of the year and a 4 digit number. So for example Joe Bloggs membership number would be B06001 his sister Betty Bloggs would be B06002 and the guy next door John Doh would be D06001.
What I would like to do it automate this system so everytime I make a new record when I input the sir name into the 'lastname' field on the form it checks the last Membership number and makes a new one. I wouild also like it to change the 06 to 07 when the year changes.
Ive done searches for things like this with Dmax etc but i really could use help with the code as I have no idea how to code this type of thing?
Any ideas on this oue would be great! Cheers Phill
I have to change a group membership of one user in an multi user access database. As I see its no possibility to change it in the backend (in X.ldb file) nor in frontend.
I am having problems developing a membership database - I have three main tables.
1. A table of 600 members of an organisation
2. A table of the subgroups these members may join, about 80 in total.
3. A table of the members of each group.
The members do not have a unique ID - complicated reason for this so I use a system assigned ID. Group ID does have a unique Id but I chose to use a system assigned ID.
Table 3 records effectively consists of just two fields, memberID and groupID. When I create a form and subform to enter these values all is well. But I cannot expect users to know these values, so I have been trying to create a subform that creates/lists/removes members from groups, using a Group main form with a member tabular subform with a surname search through a combobox. Groups have between 5 and 20 members.
e.g enter 'smit' in the combobox on the subform and a list of smiths is displayed together with the full name, from which the user selects the correct entry. At this point the record showing for instance, Paul Smith belongs to Group 17 is written to table 3. All sorts of issues arise, too many to document.
I've made a membership database for an imaginary leisure centre as part of my A Level coursework - only after more or less finishing my project, I've realised that I haven't provided a way for the end users to calculate fees for members.
I suppose the calculation I would have to do is multiply the Length of Membership (days) field on the Membership Opportunities by Cost per Month on the MembershipTypes table.
These are the relevant tables and I've also attached my database (the password is "password" for any of the users) ...
I have a table with name, club members details ID etc. This is linked by a 1 to many link (ID) to a table containing details of membership subscription payments. One entry/row per membership period. This second table has DatePaid, Paid (Yes/No), Period. Period contains 2013-14, 2014-15 etc.
I can do a query for those that have Paid (Yes) but when I try one for those that have not Paid (No) or <>Yes I get no result. I only enter members when they have paid. I need a query to display those who have not paid for the 2014-15 period.
I can do it a long winded way copying 2 lists into Excel. One all members. One those who have paid. Then remove duplicates and those paid in 2013-14 leaves those not paid in 2014-15.
I am trying to automate a membership status flag based on comparing today's date and a recorded expiry date. The expiry date control is on a sub-form. I have the following code in the OnLoad event of the master form:
Dim DateGap As Integer While Me.CurrentRecord < Me.Recordset.RecordCount If Not Me.NewRecord Then DateGap = DateDiff("y", Date, Forms!PersonalMasterF!MemberSubF.Form.MemberExpire ) Else DoCmd.GoToRecord , , acNext
[Code] .....
With debug pointing to the DateGap = statement I get the error 'Invalid use of Null'. As you can see, I've tried to trap any new records it might run into to avoid nulls in MemberExpire, and there are no null values in the MemberExpire field of the underlying table. I've also tried defining DateGap as Variant, which does not work at all.
I need to create a simple database where I have a list of people, a list of groups and all I want to do is select which people belong to specific groups.
All I need is to create a form where I have a list of my people and a tick box next to the groups to show who belongs to which group.
I have a very simple query to determine the gender ratio of an associations membership. My SQL code neatly calculates the number of females, viz
SELECT [Mail List].[GENDER], Count([Mail List].[GENDER]) AS TOTAL FROM [Mail List] WHERE ((([Mail List].[GENDER])="F")) GROUP BY [Mail List].GENDER;
However, I wish to present this result as a percentage of total membership.
My main Table has a column titled [Member Name] so my requirement is to produce a calculation of the form "Females"/"Member Name Total" all multipliied by 100.
hi, Im creating a letter. It is based on a query. I have some details that I'd like to display , one on it's own line. I cant print them out now, but they dont all fall on their own separate lines. How should I do that? If I put a carriage control at the end of each detail line, will it know to print each of the fields on its own line? I need advice . I tried doing a subreport -type letter, but it looks weird!
having database full of names and info, how to create a letter which feeds from specific database records with one click, so it can be printed and post it?
Hi All:given a table with, let's say, Job Numbers that start with a letter (such as RES2345) or just plain numbers (such as 253180001 or 9817) how can I set the criteria in the query to just give me jobs that start with a number?I first trimmed the field to eliminate blank characters and then I used the Not Like "A - Z" but it still gives me jobs with letters at the beginning of the name.thanks for all your help in advance. :D
Technical Information (for support personnel) Error Type: Server object, ASP 0177 (0x800401F3) Invalid class string /newslettercreate.asp, line 22So, the letter can not be sent.
What is the ERROR of “Server object, ASP 0177 (0x800401F3) Invalid class string” ?
My OS is winXP pro, email program is Outlook Express. Who could help l me solve this problem? Thanks!
i'm trying to work with this database. i want to know how to change a few things we have a command button that generates a letter i need to edit that letter as it contains names and numbers no longer in the office
is there a way i could find that letter
i tried to have sent to a .rtf file which i could modify, but that's tolerable for a dozen of recipient not 2 to 3 hundreds
I need to generate a pre-formatted letter from within MS Access, where the name, address and other information will be pulled from the current record on an Access Form. Basically it is like a welcome letter I could send to any new client I enter into the data base. Since I am doing one letter at the time, mail merge in MS Word is not an option What would be the easiest way to do this? Thanks
I am not sure whether this is a problem with MS Access, Visual Basic or Windows.
I have taken over supporting & developing an MS Access 2000 DB for a small charity & am not an Access expert or a programmer. The Application includes processing to create, amend & store retrieve standard letters based on a Word document called MyMerge.doc. The operating systems is Windows XP for the PCs with a MS Server 2003.
Each letter is allocated a number ‘CallID’ which is used to retrieve the letters later. The letter text is in MessageC.
The VB code to store the letters (Save As) is
Dim strTest As String, db As DAO.Database Dim td As DAO.TableDef Set db = CurrentDb For Each td In db.TableDefs If Len(td.Connect) > 0 Then MessageE = Mid(Left(td.Connect, InStrRev(td.Connect, "") - 1), 11) GoTo jumpout Next jumpout: MessageC = "%fa" & MessageE & "Db Letters" & MessageC & " " & (CStr(Forms!Contacts![PostalCode])) & " " & Trim(DLookup("[TitleType]", "Title Types", "[TitleTypeID] = Forms!Contacts![TitleTypeID]") & " " & Forms!Contacts![FirstName] & " " & Forms!Contacts![LastName]) objWord.Application.Activate SendKeys MessageC
The VB code to retrieve the letters is
Dim MessageE As String, db As DAO.Database Dim td As DAO.TableDef Set db = CurrentDb For Each td In db.TableDefs If Len(td.Connect) > 0 Then MessageE = Mid(Left(td.Connect, InStrRev(td.Connect, "") - 1), 11) GoTo jumpout Next jumpout: Dim WordApp As Word.Application Set WordApp = CreateObject("Word.Application") WordApp.Visible = True WordApp.Application.Activate MessageE = "%fo" & MessageE & "Db Letters*" & CallID & "*.doc" SendKeys MessageE Set WordApp = Nothing GoTo Exit_Command53_Click
The success rate varies from PC to PC and user to user. It will work with one letter and not the next. It is very difficult to identify a pattern. When the Save As does not offer the expected name & path, the Application (or operating system?) offers to save ‘MyMerge’ to the user’s My Documents folder.
In that case I tell the users to correct the path themselves and save the document under the CallID. Theoretically, retrieval should work since this uses the CallID and wild cards. Sometimes it does but often it will instead retrieve a document in the user’s My Documents folder. If it does go to the right folder, you sometimes have to replace the last wild card with ‘.doc’. I have tried replacing the last wild card in the VB code with ‘.doc’ but this does not work!
Finally, I alone get the message ‘Save failed due to out of memory or disk space’, neither of which is true. This makes it very difficult to continue investigating the problem. If I use a copy of the DB on my hard drive rather than the network I can save but not retrieve.
I want to pick everyone's brain on this one, because I'm not sure what I want to do can be done very easily.
I have a database that tracks client information for insurance policies. I'm going to be giving it to other agents, many who are very computer illiterate. One of the requested functions is for them to be able to create form letters from the database.
So, I'd like some ideas on how to do that. Ideally, I'd like for there to be a form that someone could load some letter templates, but also tweak them or even create new ones that can be saved. It would also need to have "merge fields" much like you would in Microsoft Word. Basically since every agent will want to have their own letters, it needs to be easily editable.
Is there an easy way for a computer illiterate person to just select "interest letter," change some wording, and have it merge into a report? Or, is there an easy way for someone to get access to the merge fields if they were to do a Word mail merge? I don't really want people trying to find the right tables/queries amidst all the different tables in the database.
Or, throw out some other ideas. I'm open for most anything. Thanks in advance.
