Condition Data in database

[gjpcrest]

Greetings to all that will be reading this.
I would like to get some help on a matter in obtaining and specifying data according to year differences in an Access db. A few words on the database that I'm using. Well, this db is ben used to calculate the sum of the person's dues according to a predetermined amount, lets say 1€ annually. Thus should this person gets to pay off his dues a recording os the transaction is been entered in to the db and by this his balance as shown in his record (which is also included in a printed report) amounts to 0 €. In case that he hasn't come up with a payment then his amount due is the amount owed. All data including the predetermined amount of dues is set by a form entry. My questions are these: A) I have people listed in the db that have joined years ago ( date joined field back from year 2013) and haven't payed yet their dues. I would like to include a calculation of dues owed in grand total from year 2013 to date minus their payments. Therefore, instead of their individual records showing only this year's dues to display 3 year dues (updated with/minus their payments if any) for year 2016 and so on.


And question B: can I include a search field in the person's form that can be used for searching according to a selection of entry fields instead of using the Access 2007 (that I'm been using) search field ie. should I choose to search within a name or an address or in a phone number fields to choose (in that new field that I'm in a need for) my query to be specifically within that required field.
Thank you in advance for your help on the matter.
Sincerely,
George

[gjpcrest]

I am reposting my questions in lieu of probably not being clear enough to pass the modifications I need to make on an Access 2007 database.

This db records the data of a small peoples’ non-profit club (82 to be exact, with peoples’ name, address, phone #s, DoB, date of join, etc) and also assigns certain membership dues to each one. Although, all member’s information were kept in a book ledger, a need to transfer all these data to a db has arised. The problems that I would appreciate some help on are these:
a) How can I have a search field (apart from the one that is available in Access 2007) in a form of an entry that can be used to find any data within the records, and on a positive outcome to display these information either in a separate window as a list with the member’s number and name, or have a separate window that would display the member’s card/info with the ability, via arrows, to move back and forth to next possible record that contains the data that were searched for.
b) Membership dues have been lowered since 2013 to € 15/year and I have a difficult time calculating and displaying in each member’s card the remaining total. To be more specific, each member-card ends up with the following fields: membership charges, paid dues and remaining totals. The membership dues are fixed to € 15 per year, the paid dues field is getting updated by another entry form were each payment is recorded, and finally the remaining (if any) total appears on the member’s card which is also can be printed in a list. I need this to be modified so the remaining total is to be calculated since the year 2013 and once the payments are entered to display the overdue totals regardless of the date of each members’ entry to this club.
It would be extremely helpful should the steps to be followed in doing these modifications were presented in detail (creating queries, forms, tables, reports etc.)
I know that I'm not providing you with the stracture of this database and that it might rather be somewhat difficult to structure a flowchart of all the paths that are been followed, but I guess an answer of how to get there and the sort of the calculations to be followed would be helpful for me to try it.

I really hope that my description of my problems were explicit enough to understand.

Thank you again in advance,

George

[ssanfu]

a) How can I have a search field (apart from the one that is available in Access 2007) in a form of an entry that can be used to find any data within the records,

I build a search form loosely based on an article by Allen Browne (http://www.allenbrowne.com/ser-62.html)
He provides an example dB and code examples.

In my search form I have unbound text boxes/combo boxes to enter the search terms for the related field and a "Search" button. If records meet the criteria, a different form opens. Double clicking a record opens a specific form for that displays the record, closing the previous form.

b) Membership dues have been lowered since 2013 to € 15/year and I have a difficult time calculating and displaying in each member’s card the remaining total

This shouldn't be difficult, depending on the table structures. It's just math and computers (in my experience ) are very, very good at math.
It would help if you could post a pic of the relationship window.
Or the dB (without data).

[gjpcrest]

Dear ssanfu,
I'm enclosing a copy of the prototype of that db. Took the liberty though in translating most of the fields used. Hope this can assist you in coming up with a solution to my endeavours as were explained in my message. Thank you in advance.

[rpeare]

How many of these fields you do want to be able to search on, if it's just first name or last name that's pretty easy.

Just put a text box in your form header and name it txtSearch

Instead of basing your form on the table MEMBERS

base it on the query

SELECT Members.MemberID, Members.FirstName, Members.LastName, Members.MemberTypeID, Members.WorkCompany, Members.Title, Members.WorkAddress, Members.WorkCity, Members.WorkStateOrProvince, Members.WorkPostalCode, Members.WorkCountry, Members.WorkPhone, Members.Extension, Members.HomeAddress, Members.HomeCity, Members.HomeStateOrProvince, Members.HomePostalCode, Members.HomeCountry, Members.HomePhone, Members.EmailName, Members.Birthdate, Members.DateJoined, Members.SpouseName, Members.FaxNumber, Members.MobilePhone, Members.InvoiceWorkAddress, Members.MemberDues, Members.Field1, Members.[End Date] FROM Members WHERE (((Members.FirstName) Like [forms]![members]![txtsearch] & "*")) OR (((Members.LastName) Like [forms]![members]![txtsearch] & "*"));

Create a button in your form header and in the code attached to that button put

me.requery

That's the simplest way to add a search function to a bound form.

Then all you have to do is have the first character of any first name or last name and it should find who you're looking for.

[gjpcrest]

Dear rpeare,
Although I have posted a question in the forum regarding the things that i need to modify in this db, I will state them in detail, hoping that you could help me out in my endeavours.
1) I'm in the need to be quided (step by step) on how to create a seperate query form that is recalled everytime I select a certain button from the switchboard screen. This form should be able to select through a dropdown menu a field in which the search proccess would commence and then display in a report the records that meet the search criteria. The design form will be arranged afterwards according to suit, but certainly I need help on how to condition the record data compare to the searching proccess.
2) I'm also in the need to have (again step by step) a calculation appear to the last of the fields (Remaining Total) on each record that would display a charge of membership dues times the years 2013 and forth (in conjunction with the DateJoined for all records), so should someone has joined in the year 2009 then his membership dues would be
(this year - 2013) x MemberDues = amount (provided that the datejoined has been taken under consideration)
where as for someone that has joined the club in the year 2014 his memebrship dues would be
(this year - 2014) x MemberDues = amount

I hope that with your expertize it wouldn't be that difficult to do so, but I would really be greatful should you quide me through these procedures step by step on how to accomplish in modifying this db.
Thank you for all your trouble and effort on this matter.
Best regards,
George

[ssanfu]

I looked at your dB and have concerns about your tables - specifically "Payments". You record the payments but nowhere are you recording the yearly dues. This is like a check book or a credit card. You put money in and you take money out.

I added a couple of tables. Look at the relationship window.
I added a couple of forms. I couldn't add the forms to the switchboard because it looks like yo have some custom programming. Plus.... I really dislike the switchboard and didn't want to spend much time on it. I tried adding the forms once and the db blew up!
So on the switchboard, I added two buttons for the forms I created..

What I have done is only a suggestion. but you need some way to keep track of both the dues and payments.


And I modified a couple of your forms - "Member Types" and "Payment Methods"......




Other things:
I NEVER use look up FIELDS in tables. See http://access.mvps.org/access/lookupfields.htm

Object names should be only letters and/or numbers (exception is the underscore).
NO spaces, punctuation of special characters. (you have spaces and an apostrophe - this also means in form names, report names, everything)
Also, do not start an object name with a number.


I almost never bind a form to a table. IMO, it is better to use a query. Easier for sorting, filtering, limiting records by using criteria.

[gjpcrest]

Dear Steve,
Thank you ever so much for the mods you've done.....tested them and they seem an excellent add-on to the base. I've created another form of the members added to the switchboard items for the searching proccess (field dependent) and adjusted it that can't be edited but can be receive the updates. The only remaining thing is to findout a way to personalize the right-click button whenever it is pressed only when in searching mode, so I can condition the items in it. Once again, thank you for all your trouble, efford and time on this db.
Best regards,
George

[rpeare]

The right click menu is called a context menu, here's a post I had a while ago touching on it you can use it as an example:

https://www.accessforums.net/showthr...=click+context

[gjpcrest]

Thank you rpeare.. I'll give it a go and try to customize it to suit...