Hi,
My question 'sounds' easy, but i can't think of a way to do this. Please help!
Ok, i have an 'employee' history table with 200K records. Each employee could have multiple records with different 'effective' dates(please see below for an example of two employees' records). What I want to do is to run a query where it will pick up ONLY the top TWO records of each emplyee. How would I go about doing this?
Thank you.
Joe
Employee History Table:
SSN--------------------EFFV_Date
123-45-6789---------------01/02/2006
123-45-6789---------------12/03/2001
123-45-6789---------------08/22/2000
222-33-4444---------------05/28/2003
222-33-4444---------------07/01/1995
222-33-4444---------------06/30/1990
Query result should be somthing like this:
SSN-----------------------EFFV_date
123-45-6789---------------01/02/2006
123-45-6789---------------12/03/2001
222-33-4444---------------05/28/2003
222-33-4444---------------07/01/1995
In an Access 2010 form is it possible to export select records and fields in those records to a specific location?
Code: Set objDialog = Application.FileDialog(4) With objDialog .AllowMultiSelect = False .Title = "Please select a File" .InitialFilename = "C:" .Show If .SelectedItems.Count = 0 Then MsgBox ("Action Cancelled") Else
[code]....
The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?
How can I get a Query Criteria To Select All Records or specific records in query design section.
I have a table that shows many departments with credit card transactions. I like to run a query to see specific department, or have an option to see all the departments when the query is run.
Is there a statement that will allow me to select a range of records in the middle of a selection of records? e.g. Rather than TOP 10, can I get like records 10-20, or 50-65 etc? Prodigy.
I was wondering if anyone has seen a easy way to use a calender to select records rather than having to type in a "between such and such a date".
Because what i have now is a qry that pulls data from a table between two dates. And i would like to make it more GUI rather than typing to prevent user imput error if you understand what i mean.
I tried searching but didnt know what to search for.
Ok I am probably goining nuts by now. I could have swore that I had a referance book that showed how to set up a form with multiple combo boxes that were used to filter forms/reports for desired records. Now I can't find that referance anywhere! Does anyone know how to do this or can they point me to a referance with this information? Please help me, this is one of the final touches I need for a project I have been working on for months.
I currently have a ListBox which I am using on a form as a "jump To..." record selector. It has to show 4 values to the users, and so far it has worked quite well. However, I recently added some queries to the footer of the form to help narrow down the data for searches, or if the user wants to see all open data of a certain topic. I'd like to have the ListBox "refresh" and only display the results of the query, but I cannot for the life of me figure out how to do it. The root of the problem seems to be that the initial SQL for the ListBox is selecting values from the under-lying table, and when the filter is applied, it simply doesn't care. When you click on entries that aren't in the filtered dataset, it doesn't error out or anything, and when you click ones that ARE in the filtered dataset, it does work properly... I'm close... How do I filter the listbox as well?
how I would write a code that will recognize information by same “ID” and then only take the first 6 years of that information. Each row has an ID and a corresponding year with other information associated with it. The data looks similar to this:
ID Year Avg HR JohnSmith 1988 .789 78 JohnSmith 1989 .854 85 JohnSmith 1990 .456 85
If I want to select a row from subform A and a row from subform b and click a button and have the keys stored in another table what would be the best way to do that?
Hello, I am sure this is quite simple, however I cannot figure it out. I have a query that shows tblPartNumber and tblAverage. The average is calculated in the query by taking the total minutes to assemble the parts, divided by the number of parts completed. What I am trying to do is run a query that will show me the most efficient (lowest average), time for each Partnumber. Ideally the query would show me something like this;
Part # Average 1234 .72 1234 .86 1234 .94 etc...
I searched on here and it seemed like the Top 10 is used, but in my application that ommits all of the other partnumbers because it only shows ten of the records for the first partnumber. I only want to see the five most efficient averages. Any help is greatly appreciated.
I have two tables, bad design, not my choice of the way it's set up.
Table a has Fields 1-2, dups allowed in all fields. No pk was originally assigned. Table b also has the same fields 1-6, but also has field 6.
Table a has all of the records in question, table b only has some of the records from table a but does not have any records that are not in a.
Something like this. Table a
Field1 Field 2 Field 3 Field 4 Field 5 John Shirt blue denim button Mark pants green straight John Shirt blue silk button
Table b
Field1 Field 2 Field 3 Field 4 Field 5 Field6 John Shirt blue denim button new Mark pants green straight used
If I query the two tables, a left join to b, then I get the correct number of total records. If I select any records from b, then the total records goes higher than there are, even for the two combined, listing the same record multiple time.
Any suggestions are appreciated. I am a blood bank lab person, not an IT person, or DBA, so simple responses are greatly appreciated.
As part of my job, each month I have to select 20 problem report resolutions and grade them on quality. Typically we have about 100 problem report resolutions per month. Is there a way I can use a query to return a random selection of 20?
I have to review 20 reports each month for quality check. I have a query that lists the reports completed within the past 30 days. Is there a way to filter this query to show only 20 random records?
If there is no way to do this, can you suggest some way of doing this so that it's impartial? For example I don't want to select the first 20 of the month because everyone will catch on and wait late in the month to post their report. Normally about 70 reports are completed in a month.
I'm sure this has a simple solution, but iv searched this forum and every solution that i get is filled with code that goes over my head! Well here goes...
I have a main form called expense... which contains a tab control. the tabcontrol in turn has three pages containing a subform each. (lets call them sbfrm1 sbfrm2 and sbrm3.) All three subforms are based on three different queries (say Qry1 Qry2 and Qry3) but the three queries are based on the same table. this table contains all the expenses incurred over the months across three categories (hence three queries). the subforms are to display these expenses according to categories.. i.e. sbfrm1 displays records pertaining to Category1, sbfrm2 for category2 etc.(the queries ensure that!)
when the main form opens, the subforms display all the records in the table according to category...but not according to the month in which the expenditure was incurred.
I now want to add a feature that enables the user to choose records pertaining to a given month at the click of a button.
for eg. if there is an option group named month, (with toggle buttons as the month names), then if the user selects the month Aug, then immediately the subform1 displays records under category1 for the month Aug? However I want to include an "ALL" option as well whereby all the records are displayed for all months (the category criteria must be maintained at any cost!!!! Is there a solution that will not use too much code!
I have a continuous form that opens all of the records for a specific user on a specific date and displays them in a continuous form. Each record requires an approval check box be marked before it can be processed for reporting. I want the approver to be able to click a button and mark all of the records displayed at one time. Currently my button will only mark the current record. How to I select all of the records?
I am currently building a db that will handle information on many events around the world. What I would like to do is set a check box that when people scroll through the different records they could check it and it (the current record they are viewing) will go into a compiled list wherein the iindividual record details of that list which the user created by their choices could be exported to an excell sheet (to automate a reporting need in the job) I have seen functions like this in large websites particularly the classified job sections in the seattle Times . where as you scroll through you see something you like you mark it continue scrolling mark another and then when you are all done you go to a page that has all and only your selections.
Can anyone tell me where to start on this one what am I Looking for Etc. This will be the next big step in this my first database. All help greatly appreciated
My statement below is current returning 4 records. Two of the records have the same GBPAmount value.
RequestID is the Primary Key
Code: SELECT TOP 3 RequestID, GBPAmount, Currency, RequestDate FROM PayRequest WHERE (((Currency)="CAD Canadian Dollar") AND ((RequestDate)>#11/16/2014#)) ORDER BY GBPAmount;
Code: RequestID GBPAmount Currency RequestDate 10207 8.17 CAD Canadian Dollar 03/02/2015 9874 33.82 CAD Canadian Dollar 20/01/2015 11327 109.58 CAD Canadian Dollar 23/02/2015 10495 109.58 CAD Canadian Dollar 05/02/2015
I'm having a problem get a query to select all of the records it should be.
When I filter the source table (200_STANDARDIZED NRGL) to show the data I want to see (PC2 = 6000; GAAP = 02; CGL = 0950, 2735, 2736, 3500 and 3501; STD VENDOR NAME = blanks), I get 33 records.
NOTE: Had to take the PC2 records that were not '6000' out of the dB I've attached in order to be able to send dB bu the PC2 filter is needed in the complete dB.
When I create a select query to the do the same thing, I get either:3 records (when I set STD VEND NAME to Like '') or 30 records (when I set STD VEND NAME to NOT Like '*' ) I've attached the dB - Query 1 is the subject of bullet 1 above and Query 2 is the subject of bullet 2 above.
BTW, the three records that show up in Query 1 are the same records missing from Query 2.
I have been tasked to determine the actual cost to build a product by summing the cost of the most recent required components to come off of our assembly line. For example, say I'm trying to determine the cost of a car with multiple sub-components (1 engine block, 4 doors, 4 wheels, 2 headlights, etc.). We make parts for all kinds of products (cars, boats, televisions, etc.). All of the data for ALL of these parts are contained in a table called PART_DATA (i.e. part ID, date started, date finished, hours spent, etc.).
Then I have a list of parts required to build a certain model car in a table called CAR_PARTS_LIST. This table contains data like (part ID, quantity needed, etc.).
If I want to know how much it would cost to build a car using the most recent car-part data, I need to pull data for the most recent required car parts to come off the assembly line that are required for a single car. That is, my car needs 4 wheels, so I need data for the most recent 4 wheels to come off the line.
How can I write a query for the "4 most recent wheel records" given that "4" is specified in the CAR_PARTS_LIST?
