Hi every body. I want create a query that has the following fields on it:
Project : project number
Year: year that project is carried out
Weekno: week no that project is carried out
Task: Task number that is done for this project
Employee: employee number that did this task
Amount: amount of salary given for this employee for this particular project
I used access query designer and selected the db_hours_worked table and dbo_hourly_wages I joined the employee fields together and project fields together. But I cannot join weekno in both tables together since an employee
can have different salary in different weeks!
Amount is calculated by multiplying the number of hours worked during this week multiplied by most recent salary of that employee for the particular project. I be happy if some one show me how I can get the most recent salary of employee from dbo_Hourly_wages table then multiply it by number of hours worked this week and put it in amount column. For the current population of db_hourly_wages as u can see in the picture posted the amount that I want to use for amount calculation is 40.
I managed to write part of the query but it does not out put a result and also it does not calculate the salary amount. Thanks
- One employee can work in more then one project
- One employee can have more then one salary (amount) for the same
project because he might get raise in salary!
- Only tasks carried this week will be printed in the weekly report
Code:SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hourly_wages.amountFROM dbo_Hours_worked INNER JOIN dbo_Hourly_wages ON (dbo_Hours_worked.Employee = dbo_Hourly_wages.Employee) AND (dbo_Hours_worked.Project = dbo_Hourly_wages.Project);
http://i5.photobucket.com/albums/y180/method007/constraint.jpg
==>pic of database
http://i5.photobucket.com/albums/y180/method007/reporterror2maximumofamount.jpg
===> pic of hours wages table
http://i5.photobucket.com/albums/y180/method007/reporterror1.jpg
====> pic of query in design view
http://i5.photobucket.com/albums/y180/method007/queryresult.jpg
====pic of query result
I'm sure the answer is on here somewhere but trying to find it and then getting it to work is a bit of a problem, so i resorted to posting.
I have 5 tables Partnership Details, Invoices, Funding, Communication, Communication Types.
the field Partnership Name in the Partnership Details table has a one to many relationship with the partnership name on tables: Invoices, Funding and Comunication.
What I want to do is create a query that will show the most recent invoice (detirmined by date) and the most recent funding (detirmined by date) for each partnership.
I can do this using max if I only want the feilds Partnership name, invoice date and funding date. however I want to create a report that will show several feilds from partnership details and I want to show the invoice no. and amount as well as the date and also the amount and notes field for funding
I have a table called Tests that contains 80+ tests that need to be performed on our system. A second table is Test_Data. It contains the test results. I need a query that will pull the most recent test data for each test in the Tests table. So supposing there are 83 tests, I should only get 83 results from my query. I am will need to join the tables as I need to get some of the descriptive information from the Tests table. Another problem I have is some tests have not been performed even once, so not every item in the Tests table has a corresponding entry in the Test_Data table.
I am trying to set up a query which will filter out the 10 most recent additions by date.
My table contains a 'Title' field and a 'Date Added' field. I am trying to get the 10 most recent titles up in a query so I can then create a report based on this.
So far I have managed to single out the most recent date simply by using the MAX function but am a bit lost from there on..:(
Hi, I have a a table that keeps notes about the status of an invoice. It can have 1 or a lot of notes per invoice. I need it to just display the last 4 notes for each invoice. I used TOP 4 but it only shows the top 4 notes and note the top 4 notes for each invoice. Can anyone help?
Hi, Im building a DB that basically tracks the hours people have worked, its all going fine apart from one major bug which i cant get my head around, I am trying to work out a way so that if someone decides to change the hours they have worked it replaces the existing record and does not create a new record.
Each record has a unique user ID attached and each day is dated, do you think this is a VBA solution or a change in the relationships perhaps?
Sorry fi there is already a similar answer to this question on the forum, but i couldnt find one!:confused:
Here's the scenario. I have 3 fields [AgentID], [QA Date], [Points Issued]. What I want to do is run a query that will give me the last date [QA Date] a point [Points Issued] was issued for each agent [AgentID]. And I want the query to display all 3 fields according. Is this possible?
I need to know if it is possible to make a query that selects the highest or lowest value below or above a number that a user or form defines.
For example, the attached database has a history of which of three children held the position of mom's favorite by recording the day that they became mom's favorite, the idea being that they stayed there until someone replaced them.
It is possible to make a report that would request a date from the user and then would return with who was mom's favorite on that date? Perhaps by checking the records to see which record had the most recent date before the date entered.
Just in case you haven't noticed, this is not the practical application of this concept, I am just trying to find out if it is possible.
I have a database with test scores that I am trying to get the most recent date for. Each student can take a test multiple times, but I only need to see the most recent test score. The tables I am using are one called Students which has the following fields, Grade, Student number, Last name, First Name, and Inactive, and another table called scores with the folowing fields,student number, score, test result, test name and test date. When I do a total query it still shows me multiple records for each student. How can I get this to show only the most recent test score for each test name? I am pretty inexperienced with access, and am totally unfamiliar with expresion builder. If anyone has suggestions I would appreciate it.
This is what my data looks like: Grade Student # Last Name First Name Inactive Score Test Result Test Name Test Date 11 751240 BarretoLuisNo577XSBST Math 2/3/2004 11 751240 BarretoLuisNo611PSBST Math 2/3/2005 11 751240 BarretoLuisNoNTBST Math 2/6/2003
What I would like to have it show is the most recent test date of 2/3/05
Now, what I need is a report that will give the last price of an item. I import the new pricing on to a table once I receive, but not all the items get new princing all the time. This means I can't query for the pricing of a specific date. I need to figure out a query that will give me the LAST or MOST RECENT price entered. Also I can't just sort it out without going through thousand of records to get what I need, that's what I've been doing so far.
Hi, been searching this forum for a couple of months now im working with access and up till now i have solved most of my problems:D
Ok the problem.
For the purpose of the question I have a training database: running one table with user id and user name. another for job id and job name. Another for training table with an autonumber, user id, job id, and date
Id like to build a query which bought up the newest only for each user id and job id.
eg. UI JI Date 2 5 01/05/05 2 5 01/05/06 3 5 01/05/05 3 6 01/05/05 4 6 01/05/06
to UI JI Date 2 5 01/05/06 3 5 01/05/05 3 6 01/05/05 4 6 01/05/06
Iv tried using a few different ways of using duplicate queryies etc, but im just too newb to figure it out :(
Any suggestions or help would be appreciated. Thanks
My tables are structured right now such that; Table 1 has all the main information and uses a primary key. Table 2 is linked to table one without a primary key, for every row in Table 1 it can have several rows in Table 2. In Table 2, i have a date field.
I want to query information such that, it brings up information for the row in table one and only the most recent row in its corresponding table 2. Most recent indicated by date field.
I've tried some things using Max(date) etc, but to no avail.
I have these fields ID,InvoiceID,DateEntered,Description
What I need now is to show the most recent transaction per invoice and what was the description for it.
Doing this
SELECT InvoiceID, Max(DateEntered) AS MaxOfDate FROM TableName GROUP BY InvoiceID;
Works Perfect. The problem I have is when I display the Description field. As soon as I do that, I see all invoices and not just the most recent. Anyone know how to write this sql statement that will return the invoiceID, dateentered, and Description only for the most recent invoice in the system?
I have built an access database (Access 2002) which contains around 370 records.
It would be useful to be able to click a button on a form to review which records (forms) have been amended or what records are new.
This could be presented by either (1) sorting the records so the newest/or latest changed records came first, followed by the next most recent etc etc, or by (2) presenting a list of record names which could be followed manually.
I have tried several text books but have been unable to find a solution so any help from the experts would be very welcome.
My form has a button that opens a report. This report is based on a query that sets the report to display ONLY information related to the person in the current query record.
How can I get the report/query to display ONLY the last (most recent) record in the query?
Hi--I have a subform where a contact's contributions are entered. After much wrangling with the subform's properties, I finally got more than one record to display at a time, but is there a way to get the contributions displayed in descending order (i.e. more recent to less recent) without having to totally redo the subform by basing it on a new underlying query? I tried sorting the contribution date of the underlying bound table that way, but it had no effect.
HI, I have a table with multiple records of inspections, so there are multiples of inspection_num and each has a different date. I only want to extract the most recent inspections? I have an example of the table below.
tblCourses holds details of all courses attended by staff and is linked to tblPersonnel by the UniqueStaffNo. Fields in this table include the CourseTitle and CourseDate (short date format).
I can easily identify who is a First Aider by selecting on tblPersonnel.FirstAider and I can identify which of the thousands of course records are First Aid related by looking at tbl.CourseTitle.
My question is - how can I produce a list of all current first aiders showing ONLY their most recent first aid course (latest CourseDate) so that I can calculate when they need to renew their certificate?
I have a TRANSACTIONS table that adds a new record each time a customer makes a payment. Fields are: our account number, date of payment and amount of payment. Our account number has a relationship with the main CUSTOMERS table which never changes.
I need to query out the most recent payment record for each customer in order to arrive at the most recent date a payment was made.
I've been looking over my books and having a hard time figuring out how to do this. Any help will be appreciated.
I've done querying before, but none as complex as this. What I'm trying to do is query a table for the most recent date (field labeled "Sample Date") for each ID (field lableled "Sample ID"). It is also giving me other output as a sum of my "Results" field. So, in essence, what I want is the total number (sum) of each "Sample ID" for that "Sample ID"'s most recent "Sample Date".
I have a query set up right now where its showing me the sum of "Results" for each "Sample ID", but with multiple "Sample Date"s and "Sample ID"s. I have the "Sample Date" field in descending order, but how do I get it to show only the most recent "Sample Date" for each "Sample ID"?
In my database I have records that are exactly the same except for some dates. My fields consists of the following: “Model Number”, “Serial Number”, “Last Cal Date”, “Cal Due Date” “Days Due” and “Interval”. The Cal Due Date and Days Due are calculated fields in my query. Each time a device has been recalibrated it is logged into the database. Example my torque screwdrivers are to be calibrated every three months. Once the screwdriver has been recalibrated it is logged into the database. The model number stays the same, the serial number stays the same, a new “Last Cal Date” is entered. I select the interval and the “Cal Due Date” and “Days Due” are calculated. Every time calibration is due I repeat this process. So in 1 year I will have 4 records showing that serial number XXXX was calibrated and when. This process is repeated for every piece of test equipment that requires calibration. How can I query to give me the most recent records for the entire database for each unique serial number.
I'm replying because I am having the same sort of problem with my current database. I do have the separate tables like don has described. Basically here is what I have:
Employee table: Employee number (primary key) Other fields for employee information
Training table: (A different table for each training that I am tracking) ID (autonumber - primary key) Employee number Training Date
There is a new entry on the training table each time an employee completes a training. I then have a query that links the 2 tables (using a join between employee number) and calculates next training due dates and filters to overdue training and trainings that are due in the next 30 days. Currently, the query is not pulling in the most recent training dates for each employee. What do I need to do to the query design so that it will only pull in the most recent training date for each employee? Any help would be greatly appreciated.
i have a database where jobs are entered and the date and times of the jobs are kept in separate Date and Time fields. to enter a date the user uses this method "30 12 05" and to enter a time like this "21.30"
i would like a query that will display the 10 most recent job additions according to their respective date and times. however, it will not be enough simply to view these records in a report.. what would be ideal is if the 10 records are opened.. in their original form format (goto next record...previous...(1/10...5/10) and the fields are editable like in the default job entry form. this is because after a job record has been added, maybe 20 or so minutes later, that record would need to be retreived as the last bits of information for that record will have been established.
how can this be acheived. i am clueless at the moment, so if there is a solution even resembling this scenario that would be ideal. (if a filter is going to be used...it can be either the 'date' or the 'time' fields, ideally though the query would use a combination of the two to retreive a more exact date to filter the records by)
thank you access world for even considering my dilema.
I have a database table comprising inspection records for about 300 process control instruments. A new record is entered for each inspection so that an inspection history builds up for each instrument.
For one report I need to extract all the records with the only the latest inspection date for each instrument.
I attempted to build a query using the design grid screen. Initially I thought I could use the "Max of" approach as I have done with numbers. This did not work so tried without success to convert the dates to serial format thinking I could "Max of".
Have now concluded that a module is needed. However with my Access Basic programming skills this would take me a couple of weeks and still be wrong!
I have two tables: tblA with PersonID field; tblB with PersonID field and AppointmentDates field. Tables are joined on PersonID field. How to run a query which would return all records from tblA and only the most recent date from AppointmentsDates field from tableB?
Do I use GroupBy somewhere or Top 1 to acomplish this? Or maybe I should use a sub-query?
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?
