I put this on the tables forum but my answers have now stopped, can anyone here help me with how I get this information to appear on a form....
I have created an expense database but I now want to try to add fields to the main form which will allow the users to select their car engine capacity and the price they paid per litre to establish how much VAT can be reclaimed. A small extract from the table from customs & excise is set out as below. There are 5 engine capacity headers and numerous pence per litre rows.
Pence per litre ....Up to 1000cc.......1001 to 1500cc
75.0 ..................8.525.................9.653
75.2 ..................8.550.................9.682
75.5...................8.575.................9.710
75.7...................8.600.................9.738
75.9...................8.625.................9.767
76.1...................9.650.................9.795
So if someone had a car with the engine size between 1001 to 1500cc and had paid 75.7p per litre for their fuel we could reclaim VAT @ 9.738p per mile.
Is there anyway I can get access to look up this information for me?
Unfortunately I can't get hold of a formula. I'm still not sure how I would look up a value, even if I changed the table as you suggested. The user would need to select a cc size and then a price per litre which would then need to be cross referenced to give a value. I could set up different tables for each engine size, but then I'm not sure how I could point the answer at the correct table.
I don't even know if what I am trying to do is possible in access.
I think I've attached the file, but I've never done this before so it might not be there!
The table I'm trying to create is called pence per litre but it is completely stand alone at the moment until I can work out how to get any information out of it. I have changed the table to your suggested layout but have only entered a few records, there are hundreds to be entered if it can be made to work!
I have created an expense database but I now want to try to add fields to the main form which will allow the users to select their car engine capacity and the price they paid per litre to establish how much VAT can be reclaimed. A small extract from the table from customs & excise is set out as below (although the table headers have moved a bit). There are 5 engine capacity headers and numerous pence per litre rows.
Pence per litreUp to 1000cc1001 to 1500cc 75.08.5259.653 75.28.5509.682 75.58.5759.710 75.78.6009.738 75.98.6259.767 76.19.6509.795
So if someone had a car with the engine size between 1001 to 1500cc and had paid 75.7p per litre for their fuel we could reclaim VAT @ 9.738p per mile.
Is there anyway I can get access to look up this information for me?
Hopefully someone can help me this (and hopefully the solution isn't staring me in the face)
I am trying to export data from a cell in a worksheet to a field in an Access database that I am building. The database has two tables, tblCompanies and tblQuotes. It works through a macro accessed by a command button, which should export the total of a quote into the field Quote_Price in the tblQuotes table, which the users will access through a form. The problem is that if I export the data from Excel, I get an error message saying, "You cannot add or change a record because a related record is required in table 'tblCompanies'. The code looks like this:
Sub NewQuote()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:Documents and SettingsDJEdwardsMy DocumentsTest Foldermarketing.mdb;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "tblQuotes", cn, adOpenKeyset, adLockOptimistic, adCmdTable r = 55 ' the start row in the worksheet Do While Len(Range("I" & r).Formula) > 0 With rs .AddNew .Fields("Quote_Price") = Range("I" & r).Value .Update ' stores the new record End With rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub
I can import the cell contents from Access with no problems, but I would like the user to be able to do it from Excel to simplify things.
I have quite an extensive form linked to a table. When I add new columns to the table I seem to have a problem getting the form to read the data.I have just added a numerical column to the table and added a text box on the form that is bound to it. When I try to pull up the value using VBA it is blank, even though an entry is visible on the form. I have set the text box to general number and the entry shows in the actual table. When typing in the entry into VBA it capitalises where required so it must be registering the table entry. However the value it pulls remains blank.
I am new to access and need help trying to retreive data. Basically I have a unique ID field (123545). I what a user via a form to be able to retreive data using this unqiue ID. Does anyone know the best way to achieve this in access please?
In my employee attendance database each record contains an employee id#, a number corresponding to an attendance infraction, and a corresponding date. Each week a clerk queries the database to pull up records for all employees who have a yearly 'total number' of infraction values over a certain numerical limit. Any suggestions as to what is the best way to: 1 - Calculate that yearly 'total number' for every employee. 2 - Retrieve the date of the most recent attendance infraction for each employee that has a total value that is over the limit?
I need to make a payment based on the latest Verify Number to a specific person so i am trying to create a form that is focused around a person, looks up the latest Verify number and can make a new payment number to add new payments.
In my tables, it works perfectly excluding the latest Verify number whereas i do not have any filters set. The Verify numbers can change which is why i need to make a new payment based on the latest Verify number. Using this number, i can add many payments to a Payment Number and add many Payment Numbers to a Verify Number for example:
John Verify NumberGo4566546 Payment Number 44 Payments101Work carried out £800 102Delivery costs £100 103Material Cost £400 Payment Number 49 Payment 168 Work Carried out £700 170Work Carried out£450 Verify Number Go4566952 Payment Number 50 Payments171Work Carried Out£900 177Work Carried Out£500 Steve Verify Number Go5877654 Payment Number 51 Payments178Cleaning£120
My Tables are linked as follows: Person Table( name of table ) PersonID( unique ID of that person )
Verify Table( name of table ) VerifyID( unique ID of the Verify Number ) PersonID( linking to Person Table )
PayNo Table( name of table ) PayNoID( unique Payment Number ) VerifyID( linking to Verify Table )
Payments( name of table ) PaymentID( unique Payment ID ) PayNoID( linking to PayNo table )
The Payments figures have no relevance as they are numbers given by the person to me so i do not need to link them, i only need to link the table they are entered onto.
I am trying to get this onto one form whereas i can see who i am paying, the latest verify number, the last payment number to the person and the last payments in a table. Then, i can click add a new payment number, and i can add a new set of payments t the newly created payment number.
I have a table (Working_Year) that is storing a single field which is the year (current_year) that is selected by the user in my initial form when the database is loaded. Am I able to use that data in other forms and reports with out linking the table? I would like to simply display the current_year the user is working in a textbox somewhere on the forms (example form named: "Main Entry") and use the current_year as a filter on reports (example report called "Gifts - by Type").
i have the below code which inserts data and then i want to retrieve the value in the first two columns but firstly it says "you must use dbseechanges" but i have put this into the code you may see below,
Code: Dim db As DAO.Database Dim rs As DAO.Recordset dim sSQL as string
I have a database with a series of fields, all set to a String datatype, that I want to be able to fill and then use with MS Word as a mail merge data source. This function lets you query a MS Access db and use the results to fill in predefined spots attached to the various data values in the table. This is great for my needs however a large percentage of times that I have a dash in the value being queried it does not return any results. For example, 01-01 would return no entry found even though I can clearly see it in the database, however it would work fine if I queried for 0101 or any other value in the given field.
Basically I'm trying to figure out why dashes would cause problems. They are obviously fine for being stored in the access database and don't cause any troubles until they are queried for, also retrieving data containing dashes is fine so long as that value is not the one being queried for.
I basically can't avoid the dashes in the data for this setup and as such hopefully can figure out why this happens and how to get around it. Thanks for any help offered.
I have a table with part orders and I want to retrieve my five most recent orders in a query. That means I'll need the 5 most recent orderids How would I mention that in my criteria?
Front end Access 2010, back-end SQL-server 2008 R2.
Normally I retrieve a certain value by Dlookup("myvalue", "mytable",...)
or
strSQL = "SELECT myvalue FROM mytable...;"
Set rs = CurrentDb.OpenRecordset(strSQL, 4)
But is there any faster way to retrieve a single value from an SQL-server table, beside doing doing the select by a stored procedure running through a pass through query, then open a recordset
Set rs = CurrentDb.OpenRecordset("mypassthroughquery")
just to retrieve ONE value?
I could not find something like DLookup("...) for an SQL-Server or in T-SQL.
I am using VB.NET 2003 and MS Access XP for a desktop application. While developing the application we have a reached a situation where we want to print a report which retrieves records from four tables. Till here it is easy to think that it can be done by a simple SQL JOIN query, but following is the complexity:
The first table stores a single row. The second table stores multiple rows related to the Primary Key field defined in Table One. The third table stores a single row related to the Primary Key field defined in Table One. The fourth table stores a single row related to the Primary Key field defined in Table One.
The above SAVE RECORD option is performed when a user fills a Form of my application. As stated above, all the four tables are inter-related with a Primay Key field (TNo) defined in table one.
I also have a MS Access Report that will print information retrieved from all the four tables. The Report has some of the fields from each of the above table. The SAVE operation is performed in this way:
(1) A unique TNo is generated for a new record that is about to be created. (2) All the entries are saved in their respective tables (mentioned above.) (3) An access query will fetch the records pertaining to this TNo from all the tables to fill the report.
I want to know how to write such a query when I have to fetch multiple rows of a table in between. Is there any way that I can pass the TNO as a parameter to this query that is saved in MS Access?
I have a distributor table within an existing database that I use to keep track of quotations to each distributor for each project our distributors bid. It was simple to design 5 years ago when we only had to quote one mfg to each distributor. However, I now have to quote distributors multiple manufacturers and tried to develop a second "estimate" table to handle things. The issue arises when I want to view all the quotes I have for one distributor, no matter which manufacturer is involved.
I have set up a relationship between the [Distributors] table and [Special_lite Dist Quotes] and the [Distributors] table and [tbl-Unassoc-Quotes] as shown in attached (.jpg)
My intents is to go into the Distributor main form and click on an estimate button that will open a form to show all the estimates I have given a particular distributor. I have been able to do that when I only use the (1) [Special_lite Dist Quotes] table. However, things get pretty screwed up if I try to add the second table [[tbl-Unassoc-Quotes] to the underlying query.
I have an Access2003 database that contains a table. The table has 2 fields. One is a counter and the other stores a picture which is datatype ole object. I want to do a one time export of the pictures. I want to save the pictures as jpegs in a designated directory. I know very little about working with ole object datatypes. Can someone tell me the easiest way to do this?
Is there a way to retrieve the very last entry to a table (via a query) without passing a value to the query.
Lets say I have a table Pets
ID desc ============== 1 Dog 2 Cat 3 Lizard
For example lizard was added last, is there a way I could pull just this out using a query? (keep in mind that I don't know wahat the last entry is, so I cannot pass any kind of value to the query)
I have a table containing the following two fields, one with monthly dates (end of month plus year) and one with profits (per month). However, for some dates the records are missing. For example, for the 31-1-1994 there is no record (not in the date field, nor in the profits field).
How can i create a query that will only show me the records if 10 or more monthly subsequent profits are known, so meaning that in those 10 months no records are missing? So that only the timespans without the gaps (missing records) are shown.
So if the 31-1-1994 and the 30/6/1994 record are missing, then the 4 subsequent records in between those two dates should not be shown,, since the amount of records is not 10 or more. However, if the next missing date would be 30/6/1995, then all the 11 subsequent records between 30/6/1994 and 30/6/1995 should be shown. Since the number of records is bigger than the required 10.
Hi, I making a query which creates a list of customers in a month. For the start and end dates of the month, they are retrieved from a table and put into 2 seperate subforms. The query isn't working through, so I was wondering if anyone would see where I'm doing wrong.
In the order_date field in the query, I have this as the criteria: Between [Forms]![sub_fltStart]![DateList_Start] And [Forms]![sub_fltEnd]![DateList_End]
If you want any more clarification then just ask :)
Need some help, please. I'm writing a simple report that needs to show individuals and the number of times that each individual has been designated the author and/or owner of a document. The two tables in the query (simplified) are: Person, with columns personID (PK) and personName; Document with columns docID (PK), authorID and ownerID. Each report line needs to show one line per person, with the ID, name, count of authorID and ownerID (showing the number of times he/she was designated the author and/or owner of one or more documents). For example: ID ... Name ..........Author ... Owner 1 .... John Smith .... 0 ......... 3 2 .... Mary Smith .... 2 ......... 0 3 .... Peter Smith ... 1 ......... 2
I need to create a query to retrieve one row per person, then do some kind of subselect (?) to count the number of matches for Person.personID against Document.authorID and Document.ownerID. I'm having all kinds of problems in what I thought would be a simple SQL statement. Can't find anything out there, so all suggestions are welcome.
Lets hope that after finding this forum, my slight problems will begin to ease off a little.
I am unfortunately one of those newbies trying to get in well above my head and level of ms access workings, but we all have to start somewhere, right?!
My problem at the moment is as follows:
The scenerio is i work for an excursions provider in Cyprus and I am trying to set up an online excursions site for them.
Now with any excursion, the price flutuates through the year when its low season, high season etc. I have built a MS Access database with the following tables so far. Excursions details: this contains everything about the excursion on offer, along with additional columns for the price changes and dates that these apply for. ie. [adprice1][fromdate1][todate1], [adprice2][fromdate2][todate2] etc
Now what I am trying to acheive if at all possible is that when a viewer takes an interest in an excursion and selects the date they would like to go on the excursion, that the correct price is displayed for that specific time period i.e if it the date was betwen [fromdate1][todate1] or [fromdate2][todate2]
Is this at all possible and if so can someone please explain to me in real laymans terms what I need to do for this too occur within the database please.
Thank you in advance and sorry for waisitng anybody's time if this seems obvious to others and not myself!
I've got an access database that I am working on, but I have now ran into a problem where I can not figure out the correct VBA syntax to use. As a sidenote - I am using Access 2003. What I would like to do is find out the syntax to use to retrieve data from a query result.
I have a query, that when ran, searches a table that contains 4 columns. The query prompts the user to enter a number which would be found in column 1. It then searches for every match of that number that was entered, and only returns a result if there is an occurrence where column 4 is empty on the same row. The max number of occurrences where column 4 can be empty is 1. So to summarize, when I run the query, it either returns a blank record (with a 0 in the first 2 columns and last 2 columns blank) or it will return a record that matches the criteria.
What I would like to do is if no records are found, I need to go to the Time_IN form. If 1 result is found, I need to go to the Time_OUT form. I am unsure of the syntax to use and my IF statement fails every time, reverting to the else statement. Here is a copy of the current code I am using (The Me.MO_ID = Null was my attempt at retrieving the results from the query):
Code: Option Compare Database Private Sub Command3_Click() On Error GoTo Err_Command3_Click DoCmd.OpenQuery "Open MO Evaluation Query", acViewNormal, acEdit
I have obtained an ASP.NET website from my company and I need to gain access to the administrator password to edit the website. In order for this to happen I need to decode the password column which are set as an "OLE Object" in the master Access Database.
How am I able to decode the OLE Object and view the password for the administrator?
