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 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?
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?
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?
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.
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!
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?
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?
I have a database that makes use of standing orders. That means that if a client has a standing order to receive products during for example 4 time as year (quartely at the end of the month). to automate the new entry by copying an old entry in the database.
Let's say I have a client where we will have to send a product at the end of June, it will look at a field where the next send date is, and when it reaches 2 weeks for that date, to create a new entry in the database based on that entry. This way, it will pop-up in our open cases and we are aware of it and also will be visible in our report.
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 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?
Hi all, I am new to working with databases. My question is, how do you retrieve the autonumber ID of a field for a row just inserted into a database.
I have read numerous threads/articles, but none give me a clear picture of what the code actually looks like, just pieces(which is like greek to me). Could somebody tell me where I can see a working example of the code?
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 a list box with 3 columns and one line on my form and am writing an update query that is to use the value from the first column of the query to update a record in a table. I have referenced the list box as ListBox.Column(0) but the code displays a value of null when I run it and the record that is to be updated is updated to Null (it is blank). How do I write the code so that the value that is in column 0 of this list box is passed through the code?
I have an Access Database that I will be using on a desktop. I have a table in this database that mirrors the structure of a table on a remote server in a SQL database. I have successfully created a vba function within the Access database that uses a server-side php subroutine to select records (I usually won't know how many) from the SQL database and return them to the access database. The code I use in the access vba subroutine to access the php subroutine is:
Code: With CreateObject("Shell.Application").Windows Set ieWindow = CreateObject("InternetExplorer.Application") ieWindow.Visible = False apiShowWindow ieWindow.hwnd, SW_MAXIMIZE ieWindow.Navigate "Web address for server-side php file" End With
The last command in my php subroutine is "return $retrievedData." $retrievedData is a multidimensional array containing data from 42 fields in multiple records (again, I usually won't know how many). I've checked the data in php so I know it has been stored correctly.how do I access the returned data within my access vba subroutine?
I should add that my overall reason for doing it this way is that I want to maintain my server-side database as an untouched master. Users can only add data to it. My client-side database is used to update the input table and further process the data. The subroutines described above are intended to retrieve "new" records only (i.e., records posted since the last access database update) from the server-side database and transfer them to the access database for further processing.
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
