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 am trying to retrieve data for a particular record.
When Project field matches a certain project number I want it to pick the record with the latest date in the date field field to select certain data fields(Owner & Rating) from that record.
Below is my attempt. However the problem is that displays all records with that project number and not just the record with the latest date.
Code: SELECT [Combined PRB Roadmap].ProjectNumber, Max([Combined PRB Roadmap].DateField) AS MaxOfFDateField, [Combined PRB Roadmap].Owner, [Combined PRB Roadmap].Rating FROM [Combined PRB Roadmap] GROUP BY [Combined PRB Roadmap].ProjectNumber, [Combined PRB Roadmap].Owner, [Combined PRB Roadmap].Rating HAVING ((([Combined PRB Roadmap].ProjectNumber)="NR-4237"));
I have a report that displays simple date fields. One of the fields is "lease execution". On the report, I want "lease execution" to display differently depending on the date the report was run.
So:
- If the "lease execution" date is more than 120 days away from today, I want it to display as Q YYYY. - If the "lease execution" date is between 120 and 90 days away from today, I want it to display as MM/YYYY - If the "lease execution" date is 90 days or less away, I want it to display the normal date MM/DD/YYYY
I was thinking I would need to do DateDiff() to figure out an amount of days that's between Now() and [Lease_Execution]. Then based on that amount make the report show it differently. Pseudocode would be: if DateDiff() = 40, then display [lease_execution] as MM/DD/YYYY
I am searching the net for a response to my problem but no result; I have 1 BD access (Facture) in witch there is 2 tables (Entete_Facture and Detail_Facture) with a relation in Field (Numero_Fac).
It is very easy to use "INNER JOIN" to do a Query (Facture_with_date) fusionning the 2 tables, But I want to add to this query another column : " SUM(Qte_Article) GROUP BY Code_Article " as below:
I wonder if anyone can help me out on a db i have inherited. Basically I need to add a field to a table and backfill the field with a number depending on a date field.
The new field (CallID) needs to record the order in which calls are received on a certain date (CallDate in the table). So for example the first call on 1/3/05 CallID=1, the second on 1/3/05 CallID=2. The following day CallID restarts at CallID=1.
So I'm looking for an update query which will set CallID for the several hundred existing records in the database. If anyone can help with a pointer or too, or example SQL, I would be very grateful
The following code should sum a field depending on the date. The field IncidentYear is created and set to Last, This or "-" depending on the date. I should get three rows, one for each of the three values..
I get..... you tried to execute a query that does not include the specified expression as part of an aggregate function
I'm sure I've missed something obvious and I can't see it! If I remove the group by clause and the sum field..... it works.
SELECT IIf([Incident Date]>=#01 July 2012#And [Incident Date]<=#30 June 2013#,'Last', IIf([Incident Date]>=#01 July 2013# And [Incident Date]<=#30 June 2014#,'This','-')) AS IncidentYear, Sum(tbl_2c_Acc.[Vehicle Off Road Time]) AS VOR_Acc_Days FROM (tbl_2c_Acc LEFT JOIN tbl_Value_Proposition ON tbl_2c_Acc.Lessee = tbl_Value_Proposition.[Lessee No]) LEFT JOIN tbl_Vehicle_Type ON tbl_2c_Acc.[Vehicle Reg] = tbl_Vehicle_Type.[Reg No] GROUP BY IncidentYear;
I would like a query to run, depending on a date..If the date in [Txt_Date_Account_Opened] is between todays date, and within the last 30 days.do CurrentDb.Execute ("Qry_Append_Units")else a msg box stating "account is not yet open"
I'm looking to have either a combo box,, or 2 options buttons (whichever is easiest) that set a date field based on what is selected in the combo box/ option buttons.
The variables are 'Payment in 30 days" " Payment upfront"
if "payment in 30 days" is selected the date field on the form will enter the date as + 30 days from todays date if "payment upfront" is selected the field on the form will enter todays date.
This date field must be blank unless an option is selected, as this information is only entered in a later stage of the form.
I've tried a few different ways to enter this but i can't quite get it to work as i want.
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?
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 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?
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 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?
