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
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
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.
Ok I am right now making a simple Vendor/Product database to create a line sheet for some sales folks. I have 3 tables: Vendors, Products, and an associate entity Vendors_Products to relate the two. I have a form currently that draws the Vendor Name (primary key) from the Vendor table and the Product Name from the associate entity. This allows me to create new vendors and select current product types from a drop down box. The problem is that the drop down box is too long and it is tiresome when 1 vendor has 10 product types.
Can anyone tell me how to resolve this? I thought it would be better to have option buttons and display all available products. Then you could just click all of the option buttons that apply to that Vendor and it would create the relationships...is this possible?
Hi, I have looked at some of the threads here and it is clear that many of you are working on a much higher level than me and with a high degree of familiarity with the programme. I am hoping that someone here is able to give me some advice as I don't find the MS help files digestible. The task I have is to join 2 databases and produce a table from which I can run a mailmerge. I have managed to join the 2 databases and I used a customer ID as a common link. (my apologies if the terminology is incorrect) I now have all the data I require in one table. THE PROBLEMs I have multiple entries for some of my customers and would like to reduce this to single entries (which is understandable). Please tell me how to do this if you can, and keep it as simple as you can please.
I'm making a report detailing competitors final finishing positions in a competition.
The columns are (1) Place, (2) Name and (3) Points
I have the data for their names and the points they have scored, but how do I make the table enter the record number (starting at 1) in the first field???
Not sure what the issue is here but i am trying to append a Job number from a main database into two connected databases. The number is a sequential number i have created and the primary key. It is an autonumber field(obviously). The format is J0000 and the numbers start from J0001
I am using two different append queries to append them into the external databases tables. However (and here i think lies the problem) the fields i am inserting them into are text fields with the same format J0000. When i view the numbers in these tables, they are displayed as J1 or J9. The 0's are missing. Any ideas.
Hey guys, I'm just wondering how I could limit a field to using from 2 - 20 letters. If I placed the letter "a", I'd get an error saying how at least 2 letters must be there, and a maximum of 20. How would I do this?
Lastly, how can I make it so when I enter data into a form, I can click a button which adds it all into the table? When I do it normally, it's automatically transferred into the table as I type it into the form.
This is incredibly simple I know, but I am a n00b at Access. ^_^
OK, I have a strange problem... This doesn't seem to work..... It did... then I made some changes.... Can someone clue me in on what I am doing wrong? A query, simply summing some values in fields... no biggy...... BUT... The field TotalHours: ([SundayHours]+[MondayHours]+[TuesdayHours]+[WednesdayHours]+[ThursdayHours]+[FridayHours]+[SaturdayHours])
So why isn't this working? Can I use a simple VBA statement to sum these fields?
I am trying to create an IN query (ACCESS) which will firstly show the customers table (specific details) then the orders details should be in the IN query, I formulated this but it won't work:
SELECT s.CustomerID, Address, City, PostalCode FROM Customers s WHERE s.OrderID IN (SELECT s.OrderID FROM Orders p WHERE s.OrderDate = '1996') AND s.OrderID IN (SELECT s.OrderID FROM Orders p WHERE s.OrderDate = '1997');
Here is the one I did to show the customers and orders who made orders in 1996 and 97:
SELECT Customers.CustomerID AS Customers_CustomerID, Orders.CustomerID AS Orders_CustomerID, Customers.Address, Customers.City, Customers.PostalCode, Orders.OrderID, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE (((Orders.OrderDate) Between #1/1/1996# And #12/31/1997#));
it is probably something simple I am missing here! Sorry to keep asking :(
but I can't seem to figure it out. All I get is Null values when I run the query.
I have a form with a start text box and a end text box. I use the form name sucessfully in the criteria row so I know there is no problem with the form name. I also know there is no problem with the qry name. Does anyone see anything I am doing wrong?!?!
The query I am working in is labeled [qry_old_summary_date_range]. Just wanted to clarify that is not a problem.
I have a table with dates of deadlines and another with dates of submissions. For each deadline I want to know who submitted first. I figured that all I had to do was compute the difference between the dates and the select the minimum. However, Access rejects the Min() function in the WHERE clause. How do I avoid this?
I have a table (imported from excel) that is a pricing grid for blinds. The header row at the top is widths(mm) and the header row down the left is heights(mm). The data is the price. The import process makes the widths the column names.
height 600 800 1200 ... 300 $60 $80 $100 600 $80 $100 $120 900 $120 $140 $160 . . all I want to do is to take that table and make it flattened, like this:
I can't think how to do this in a query - I could take the flat version and create a crosstab to go the other way. Any clues - I don't want to change too much about the way the imported excel file looks becasue I have no control over that (I already discard miscellaneous heading and junk before I import it)
My Brain has completely lost all of it's gray matter.
I have two tables tbl_Employers and tbl_Services that are linked via a one to many relationship. Each Employer can have Many Services. The field i am useing in the relationship is EmployerID
If I build a simple select query using these tables I will see all of the employers that have a related record in the services table. Any employer could be listed there many times as per the relationship.
What i want is a simple count of how many employers actually have recieved a service.
Why is this so difficult for me to get my head around.
this is my query in design viewhttp://img99.imageshack.us/img99/3095/querytc3.th.gif (http://img99.imageshack.us/my.php?image=querytc3.gif)i use this query when entering datai want to know how i can set it up it display all or any one/two/none of the criteria i put into the question text boxes, number surname and state, or even just number, or just state.anyone?
this might be a crude roundabout way of doing things but it works as is, and i'm trying to streamline a little.
i have a database that contains all of my customer's details. once a month remove those whom no longer want my mailout, and export the remainder into excel so that my printer can print and stuff envelopes.
i record customers that no longer wish to recieve my mailout, by putting something(anything) in a column that says rts.
what i'm trying to work out is, how in a simple query i can prevent any record that has any value in the rts column from displaying the name/address etc.
if there's a code to say
if anything in rts exists then hide name address state postcode
I am working on a simnple form that has just a few simple calculations in it. One being wieght difference and the other being percentage weight difference.
I get Access to calculate the wieght difference easily, but Ican't then get it to calculate a percentage weight difference (in another cell).
This is what I have down for code.
If Weigh1 <> 0 Then WeightDiff = Weigh1 - Weigh2 End If If WeightDiff <> 0 Then PerWeightLoss = WeightDiff / Weigh1 End If
:confused: I have a logon form which works well and authenticates users via a table. The problem is when the form is loaded the user has to click into the text box to type their name. I want a cursor to be flashing in the text box when the form opens so the user can type their name straight away without having to touch the mouse. I have ensured that the tab index for the text box is first however this has not resolved. Is it something to do with focus? If so how to I ensure that the text box has focus on launching the logon form??
I have a very basic form (frmHalfTab) based on a basic table (tblHalfTab2). The table is uploaded periodically from excel, overwriting the original table. I'm trying to determine the best way to accomplish the following:
I need a query, Dlookup, SQL statement, or something, that will look at the contents of one field (MBRDrug) and populate the following field (MBRDrugAlt) based on what's in the first field. For example, if MBRDrug field has "Pravachol 10MG" in it, I need it to fill in MBRDrugAlt with "Pravachol 20MG". I am hoping to set this up in the form's OnOpen event to occur automatically with no direct user input, as this is what my boss is asking for. Any suggestions on how to proceed? Also, in my failed attempts, I have the original drug and it's suggested alternate in it's own table, tblHalfTab1, if that helps with any suggested solutions. Help?
This has racked my brain. Problem= The "simple problem" is to create a 2nd subform that alerts the user that a vendor order has already been generated and show a listing of what forms they are. This is determined by field VendorOrderID. So I thought a little something like this would work.
If BidOrdersSubDetail.Form.VenID = VendorOrdersub.Form.VendorOrderID Then BidOrdersSubDetail.Form.AllowAdditions = False VendorOrdersub.Form.Visible = True Else If BidOrdersSubDetail.Form.VenID = VendorOrdersub.Form.VendorOrderID Then BidOrdersSubDetail.Form.AllowAdditions = True VendorOrdersub.Form.Visible = False End If End If :( then came Error 2427 :eek: expression has no value.
Now i have lost the last of my hair to pull and rewrote the code several ways. It will not let me assign a value of 0, I cannot figure the propper use of IIF, true, false, :confused:
any help, the vendor id is not generated untill after the order is created. They can review an order, which is the reason to block additions to the main sub form, and to notify the user that it is complete and what vendors were used. There for there is no value to vendorOrdersId for a new one which is the only identifier I have.
My form has just started to add new records when I use the mouse scroller. Before when I used the scroller it would stop at my last inputted record. Any ideas what I've done to it or how it can be stopped?
I want a field on a form for new bookings where a bookingno. can be entered to do this:
When the bookingno is not yet in the database, I want it to create a new booking, like it is doing now... Now, when someone enteres a bookingno that is already in the database, I want it to display all data that was entered earlier on this form.
I suspect this is simple, but as some of you know by now, I am a total Noob when it comes to access.
