OK I'll start by giving some background detail. This is my major project and I am creating a system for the school library. I am using Microsoft Access 2003. I have made a query and my end user will type in the date that a book is loaned out. My database then automatically fills in the duration it is loaned for, the date is due back. What I hope someone on here can tell me is:
Can I get the current date on my database. So that it updates all the time to always keep the current date. Then I can make it automatically work out how many days late the book is (if it is).
Anyone who can answer this question or point me in the right direction is a hero!
Thanks, Michael.
EDIT: OK I have found how to make the late field update automatically. Late: Date()-[DateDue]. But surely if it can do this and know the current date for use in an equation it can display the current date? Anyone know how to do this?
Hi there, I am having a couple of problems with access.
I have a bit of a problem trying to get access to open microsoft word with a mail merge already set, i really want to have it so i can click a button in access, and it will address a letter in MS word ready to type in, without having to go into word every time and set up a mailmerge. Can you help me with this?
My project is for a financial advisor. There are 3 tables, client data, link table and a policies table. They are linked with relationships.
Also...
Each client has a Total renewal commission earnt. Each Policy has a renewal commission earnt. I want to be able to add the data each month from the policy renewal commision earnt, to the clients total renewal commission earnt, and then reset the policy renewal commission at the end of the month.
At the end of each month a letter comes through with each policy number, and the amount earnt. I want the user to be able to type this information in for each client and then be able to print a report at the end of the month( it has to be claimed ). At the end of the month this all wants resetting, but the amount wants to be added to the clients details. Each client may have more than one policy, each policy may have more than one client.. which is why there is a link table. Is this possible?
I also have a field called Total initial income on the clients record. I want this to be a calculated field from all the policies they hold. The policies all have a "Initial Income", but each client often has more than one policy... how do i get it to add up all the initial incomes from the policies they hold then put it in the clients records? I think this needs an update query but i am not sure how.
If you can help me with any of this please do! Even if its just a bit you might know,.... Thanks Steve
Hi there, thanks guys for your help with my last problem. I am so close to finishing my coursework now, once I have this last bit done all I have to do is make a few simple queries to search the data and write up about it. I am having a problem with my database ( yes, I know the database is a bit all over the place but i just need to finish it now... besides, the exam board don't even ask to see the working model, just screenshots! )
In my database, there are a few tables. The main ones are Policies, Client data, Link and commission.
Policy table - Holds all info about the policy Client data - Holds all data about the client Link table - Links the client to a policy using the client reference and policy number. One client may have many policies, and one policy may be held by many clients. Commission table - This i think is the problem table. needs to store a policy number, the client it is linked to, and the commission earnt by that client/customer, along with the date ( it is renewal commission ). The reason this is not with the link table is because one client may earn lots of commissions from all the different policies and this needs to be able to be stored along with the date.
Sooo... I have a form based on a query, the query asks the name of provider for a policy. The form then displays all policies held with that provider, and using the policy number, through the link table... it retrieves the client data from that. So, the same client may appear twice is he/she holds two policies with that provider, and the same policy number may appear twice, but never the same policy number and client reference twice. The query does find the data I want it to. It then shows it in the form. The problem is, two fields I want to edit ( linked to the commission table to type in a commission, and a date... as I want it to then store the commission, date, reference number and client number in it in the commission table)... it won't let me edit it.
I have no idea why, I have a feeling it may be to do with the relationships. I find it hard to explain exactly what it is I need it to do but I hope I have explained well enough for someone to help me... in short..
The form should make it possible to search for a policy provider, show all policies by them and clients with the policies, then store the commission data ( typed in ) along with the date ( typed in ), client number and policy number. It gets so far,.. then fails. Another problem is that sometimes the form just comes up blank!
I'm sure this would be very easy for someone to show me where i'm going wrong and some help would be so greatly appreciated!
To see what i mean, please open the database, on the main switchboard there is a button saying "add data from commission sheet"... please open this, select a provider ( I left one in (( I know spelt it wrong )) and it will display all policies with them as the provider ( allience and lester ), you will find two are held by a person named Stephen Martins ( not real ). the commission and date part can't be edited.
If it helps: the first form you will see ( to select provider ) is called frm_select_commission. This opens the next form called frm_add_commission, which is based on a query called qry_select_name_of_provider. The query gets its criteria value from the box on the frm_select_commission form. My teacher said he didn't know what it could be but it could be something to do with how the relationships are. Please help me Thankyou so much! Steve
P.S THE LINK TO THE FILE IS: http://www.nutty.tripod.com/Financial_advising_database.mdb
I am currently doing my A Level ICT Coursework, my exam board is AQA.
The problem i have encountered is as follows:
I have a form, containing a sub form, within this subform i have a macro which i want to take me to another created form but when doing that copy a certain value in a record across to the new form.
For example.
Subform - Orders Record to be copied - Order Quantity (within the sub form) Macro button pressed within the sub form New form opened Record in the new form - Adjust Amount (this is where i want the copied data to be input.
I am new to this forum and to Access. I have expierience with visual basic.
I am making a program in access for saving data when someone borrows something from you, to keep track of all your borrowed stuff.
Here are my questions.
1) How can I write code for buttons to change the records (movenext, movelast,...) + how can I change the current record? + how can I control a scrollbar via code?
2) How can I change the current recordsource via code and how can I run previously made query statements via code?
3) How can I change the ShowDropButtonWhen property from a combobox via code?
4) How can I control a FlexGrid via code?
5) How can I open another Form with a press on a button via code?
6) How can I save and load an OLE/object image and how can I show them via an ActiveX control?
Thats about it folks
If anyone can point me in the right direction or can give me an example database, I would be very happy:)
I am responsible for creating a database for a school project, and am having some problems relating the two tables of the database together. Please keep in mind that I have no prior Access experience, and know very little about the program. Here is my situation, any help would be greatly appreciated:
Taking the perspective of a product vendor in a store (i.e. selling soda to a grocery store), I am trying to create a database which allows me to run a query for authorized products in certain stores. I have a products table which has all available products the company sells, and then I have a Stores Table for the different stores which carry the company's product. I want to know how to set up and join each table so that if a certain product is not authorized in a certain store, it will not show up in a query.
Again sorry for the shallow description, any help would be greatly appreciated.
Help! I am trying to think of a way I can use an excel worksheet as a data form in Access. A little background to the problem. A pre-school that I have been helping have a excel spreadsheet in place that they use as a kind of booking register which also works out the termly costs for each child based on the number of sessions attended. Is there any way I can inport or link this to a database so monthy invoice's and goverment figures can be printed automatically.
I am trying to create a access database system which will show classes, students, attendance, progress report, teachers, departments etc...
i have repeatedly failed in accomplishing this database as i dont have the knowledge.
the place that i get messed up is for example the attendance, i want it so that i can select a date and then all the students associated with that class showup. so 1 date and a list of student only on that class.
another problem was adding a progress report for every class and individual students.
this is for a community based school which teachings on saturdays, there are over 15 volunters who do not get paid to teach but are still willing to give something back to the society.
i beg you guys out there, you all are geniuses!!! please help us design this simple system. please if somebody already knows where i can get a system like this free of charge then let me know ASAP!
I would like to thankyou in advance for reading thins messge.
I am designing a library system for an A2 project and i was just wondering if i could have a validation rule to prevent a copy from bieng borrowed if there was allready a record for a book in the database with no value for returned field. (meaning it hasnt been returned and its currently out) Im pretty noobie at access
LOAN__PUPIL_COPY___BORROWED_____Returned 100001 1001 10001 17 November 2004 100002 1002 10002 20 November 2004 100003 1003 10003 17 November 2004 100004 1004 10004 18 November 2004 100005 1005 10005 20 November 2004 100006 1006 10006 20 November 2004 100007 1007 10007 20 November 2004 100008 1008 10008 20 November 2004 100009 1009 10009 20 November 2004 100010 1010 10010 20 November 2004
This is how it looks at the moment. Im not sure where you would add a validation rule. The records in this table will be kept, and when a book is returned a date is added to the returned field. MANY thanks for anyones input, ive been brain storming it for weeks but i dont think i can do it without any code of some some sort.
I am trying to create a access database system which will show classes, students, attendance, progress report, teachers, departments etc...
i have repeatedly failed in accomplishing this database as i dont have the knowledge.
the place that i get messed up is for example the attendance, i want it so that i can select a date and then all the students associated with that class showup. so 1 date and a list of student only on that class.
another problem was adding a progress report for every class and individual students.
this is for a community based school which teachings on saturdays, there are over 15 volunters who do not get paid to teach but are still willing to give something back to the society.
i beg you guys out there, you all are geniuses!!! please help us design this simple system. please if somebody already knows where i can get a system like this free of charge then let me know ASAP!
I would like to thankyou in advance for reading thins messge.
I have a report that needs to be set up in 2 columns using the school year, 2012-2013, 2013-2014, etc. My issue is this. The columns go down the page and continue on the right side for column 2 in the correct order. Is there a way to have the columns break for the school year?
I currently have this: 2012-2013 2014-2015 2012-2013 2014-2015 2012-2013 2014-2015 2013-2014 2015-2016 2013-2014 2015-2016
I am trying to create a report which is basically a School Certificate. The requirement is that I need to print the Issue Date, means the current system date be printed in ENGLISH WORDS rather Alpha-numeric format, i.e.: instead of 11 Mar 2012, I need some thing like this: Eleven March Two Thousand and Twelve.
Most of the project is done...I am stumped on calculating a field for the report...any help is greatly appreciated..thanks so much.
Use a Microsoft Access database to create the tables and column headings show in Figures 1-3 and discussed in the Background section. Create the tables in the design view. Populate the tables with the data as shown. Make sure you create a composite primary key field for the ORDERS table. The key should consist of the fields Invoice Number and Product Number to allow customers to order more than one item.
Customer Customer NumberCustomer SchoolCustomer StreetCustomer CityCustomer StateCustomer ZipContact Name 105Central High123 Main StreetDenverCO80208Mary 107Central Middle35 South ParkDenverCO80278Sue 423JKF Elementary69 5th AvenueCheyenneWY90200Pat 516Toddler Time8000 ParkwayFt. CollinsCO80487Jan 799Georgetown High819 Rt. 909BoulderCO80303Jack
The owner wants to find out how much of each product is selling for the date ll/15/2005. Develop a query in the design view to give that information using the headings Date, Product Name, and Quantity. Save the query as Today’s Sales.
Creating another Select Query
Tomorrow, the owner is going to be in Denver, CO, and she would like to call on the customers there personally. Make a query to display the contact name, school, and street address ONLY for those schools in Denver. Save the query as Denver Schools.
Generating a Sales Report
Finally, make a report to show the dollar amount sold to each customer for each product. To create the report, you will need to:
•Make a query first and then bring that query into the Report Wizard. •The query will contain a calculated field for Total Owed. •The query should show the fields Customer School, Customer City, Date, Product Name, Quantity, and the calculated field, Total Owed. •Name this query “Report Data” •The report will be grouped by Customer School and Customer City, with subtotals for each school’s sales and a grand total. •Make sure you delete any bogus total lines, and make all appropriate fields currency.
Deliverables
Three tables Query output: Today’s Sales Query output: Denver Schools Query output: Report data Report: Current School Sales Defined relationships (I can press the relationships button and will see that the relationships between tables are defined)
I have inherited an Access project from a former employee. My school currently keeps all grades in an Access program. Right now we just assign numerical values and those are turned into grades (I cannot actually find this table). Overall it works well, however, we now have classes where we need to input Pass/Fail and Audit.
I am developing database for my school. I am done with everything except the examination aspect where i need to indicate students position in every subject beside their overall position according to the sum of all the subject scores. How to indicate student position in subjects.
