I've been searching the forum for an answer and see that there are a lot of knowledgable people out there but didn't find an answer to my problem so I'm hoping someone can help.
I have a table that has PartNo, Unit_Price, Updated_Date. It is filled with every purchase we have made for the past two years, so there are multiple records for the same part numbers with different prices and different dates. I'm looking to pull the most recent dated record for each part number. For example:
PartNo Unit_Price Updated_Date
123 7.89 08/12/07
123 7.91 10/15/07
123 8.02 1/10/08
I would like it to return the bottom record only (and do this for each of the hundereds of part numbers we have) so I end up with a table that looks like...
123 8.02 1/10/08
456 1.52 1/22/08
789 10.59 8/02/06
and so on... :D
I am trying to write a query that will check all records in a table but exclude the 10 newest records in the table.
The table is from a stock program i have wrote for the company i work for (i am a novice access user). what ive been asked to do is write a duplicate order system that will flag up if the order has already been packed.
the table logs the [OrderID] with each item [barcode] scanned out with a barcode scanner what i want is a query that checks the OrderID for a duplicate entry in the entire table but because the OrderID is entered with every item scanned i want to ommit the last 10 records as prety much no order has more than 10 items i understand this may not be 100% fool proof but it is close enough.
The other option is to have it ommit all records within the last 15 minutes there is also a [Time] and [PackDate] Field which im guessing could be used for this the time field records Now() and the PackDate records Date(). After searching the web i cant seem to find anyway to ommit the last 'n' records and the few things i have found with the Date("m",-15,Date()) doesnt seem to work
I have a crosstab query that I ONLY need the newest date from the table I'm pulling from reguardless of what it is for. Here is what I have:
TRANSFORM Max([Cust Count].[Active Customers]) AS [MaxOfActive Customers] SELECT [Cust Count].Node, Sum([Cust Count].[Active Customers]) AS [Total Of Active Customers] FROM [Cust Count] WHERE ((([Cust Count].Services) In ("core","data","telephone")) AND (([Cust Count].ASC)="uh") AND (([Cust Count].Date)=#6/12/2005#)) GROUP BY [Cust Count].Node, [Cust Count].Date ORDER BY [Cust Count].Node PIVOT [Cust Count].Services;
I know this is only setup to get items from 6/12/05 but more will be added to the table and I only want the new info for this query. Thank you.
I am a relatively new to some of the more advanced features of Access. Here is my problem:
I have a table that will be linked to a graph. Each record is for a particular date. There can be multiple dates within a month, but for each month I only want to display the most recent date within a given month. In other words, the graph will only display data from the last record in a given month.
My idea was to somehow flag the records that are the newest date within a month. However, I would also be open to running an append query to simply delete these records as the table is updated (though I would rather preserve the records and stick with the "flagging" option).
I have looked for ages for a solution to this and have not yet found one to suit me so here I am.
I am creating an Access 97 db in which I have a form (section1) which shows its records in datasheet format(results from a query). On this form is a button which opens another form (fileViewNewSec1) where the user can input the data for a new record. In the closeEvent() part of this form I have put:
=[Forms]![section1].[Requery]
This successfully refreshes the form and the new record can be found. However, there are many records in this database and it would be nice if once the form (section1) has been refreshed that it would focus on the newly created record. I am not sure if anyone can help me with this.
The records are sorted by "fileRef" and therefore the newly created record is not placed at the bottom of the records. They do however have an auto number primary key, I am not sure this will be useful but they you go.
I have Query which has dates in multiple columns. I need to know if there is a formula that will allow me to display the oldest date and the newest date?
Below is the output of my Query in Excel. I need the output to have two additional columns: Beginning Date and End Date. Based on the example below, I would need the beginning date to show 6/21 and the end date column to show 6/23 so when I export to excel, it shows these two dates. In Excel, there is a min/max formula that can do this but it looks like the min/max in Access only displays the min/max data? Unfortunately, creating this formula is not an option after the fact. Secondly, I need the word "DIRECTION" to show in it's own separate column in the in the query. I don't know if I can do this without adding another table to the Query or if I need can just add a column in design view and put something in the criteria that will do this.
I have included an example of my current output and what I need the output to read.
Current Output Sunday In Out Monday In Out Tuesday In Out
I've almost completed the DB I'm working on, but still have a few loose ends to tie up that I can't seem to figure out. I've spent many hours already on this forum searching for solutions, and have tried a few different things, but I still can't work out these bugs.
First, I need my form to open with the latest records filtered first, so that the most recently added records will be easiest to find and edit. I'm not sure how exactly to do this. I've tried a run query when the form is opened, but I still need all the records displayed or accessible. The form is called frmDenial, and I would like it to be looking in the DateLogged field of the form. Any suggestions?
Second, I have command buttons that will export data in the fields to MSWord Templates via bookmarks. The button runs well by opening the document, inserting the data, and printing. I have also tried several different code syntax to close word once it is done, but it is still staying open after printing. Here is a sample of the code: ________________________________________ Private Sub Print_Letter_Click() Dim objWord As Word.Application 'Start Microsoft Word 2000. Set objWord = CreateObject("Word.Application") With objWord 'Make the application visible. .Visible = False 'Open the document. .Documents.Open ("G:PharmacyPrior Auth Docs and DataRevised Pharmacy Denial ProcessesKAN Not Nec or Benefit2.dot") 'Move to each bookmark and insert text from the form. .ActiveDocument.Bookmarks("bmkFirstName").Select .Selection.Text = (CStr(Forms!frmDenial!MBRFirst)) .ActiveDocument.Bookmarks("bmkLastName").Select .Selection.Text = (CStr(Forms!frmDenial!MBRLast)) .ActiveDocument.Bookmarks("bmkHRN").Select .Selection.Text = (CStr(Forms!frmDenial!MemberNumber)) .ActiveDocument.Bookmarks("bmkAddress1").Select .Selection.Text = (CStr(Forms!frmDenial!MBRAddress1)) End With Print_Letter_Click_Err: 'If a field on the form is empty, remove the bookmark text, and 'continue. If Err.Number = 94 Then objWord.Selection.Text = "" Resume Next End If objWord.Application.Options.PrintBackground = False objWord.Application.ActiveDocument.PrintOut objWord.Application.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges 'Quit Microsoft Word and release the object variable. objWord.Quit Set objWord = Nothing Exit Sub End Sub _______________________________________
Finally, I have two cascading combo boxes set up that will auto pop related fields based on the selection made. But, if an item is not in the list, I would like the user to add it to the linked table to appear in the list. I also have this working well, with a pop up asking the user if they want to make the addition, type in the new item, and add to the list without requiring the user to refresh or exit then re-enter the form. But it will only add the item name, and not the item description (another field in the form and another column in the table). How can I modify the code to prompt the user to enter these other details? I can link it to a pop up sub form to enter the data, but if possible, would rather the boxes pop up to have the user type in the data. Here is the code I have so far in the NotInList Event... __________________________________________ Private Sub DrugName_NotInList(NewData As String, Response As Integer) Dim DB As DAO.Database Dim rs As DAO.Recordset Dim strMsg As String strMsg = "'" & NewData & "' is not an available Drug" & vbCrLf & vbCrLf strMsg = strMsg & "Do you want to add the new Drug to the current Database?" strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to re-type it." If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new model?") = vbNo Then Response = acDataErrContinue Else Set DB = CurrentDb Set rs = DB.OpenRecordset("tblDrug", dbOpenDynaset) On Error Resume Next rs.AddNew rs!Drug = NewData rs.Update If Err Then MsgBox "An error occurred. Please try again." Response = acDataErrContinue Else Response = acDataErrAdded End If rs.Close Set rs = Nothing Set DB = Nothing End If End Sub ______________________________________________ The two other colums in the tblDrug that I need the user to be prompted to fill are Denial Reason (column 3) and Alternative (column 4). What would be the best way to accomplish this?
I would really appreciate any help or suggestions with any of these problems. Thank you so much! :o
I need a Query that grabs the lastest "newest" data from the 5 tag.table tags generated from the query below and Display them in a result,
SELECT FloatTable.DateAndTime, FloatTable.TagIndex AS FloatTable_TagIndex, FloatTable.Val, TagTable.TagName, TagTable.TagIndex AS TagTable_TagIndex FROM FloatTable INNER JOIN TagTable ON FloatTable.[TagIndex] = TagTable.[TagIndex];
As you can see it consists of the name and a date of the backup. When it populates the combobox I want it to go in date order I newest to oldest. At the moment its going of the first number.
I have a form with 15 unbound text boxes (daily temperatures) and what I am trying to do after entering the temperatures into the text boxes the user clicks an add button which will add 15 new records into the temperature table
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
Here is the situation that I'm hoping that someone can help me with. I'm working with a database that tracks our condo units - from prospect coming into the system until we close them as a buyer. All the units are setup in the system so a salesperson will select from the units available. All that works fine when I create reports. The problem is trying to get the parking and storage on the same reports with the unit information. The problem is that there are multiple parking/storage units "attached" to a single unit and I cannot figure out how to get them to all appear on a single row of the report. As an example -
I have units A, B, C Parking units p1,p2,p3,p4,p5,p6 Storage units s1,s2,s3.
Unit A uses parking units p1, p2, p5 and storage unit s2.
Unit B uses parking unit p3 and storage unit s1.
Unit C uses parking unit p4, p6 and storage unit s3.
How do I write a query/report that would show:
Unit Parking Storage Unit A p1, p2, p5 s2 Unit B p3 s1 Unit C p4,p6 s3
I have three tables. Risk, Names and RiskAndNamesJunction table. I have the junction table because I have many to many relation (meaning many people can be connected to one risk and many risks can be connected to one people).
The problem is that If I make a query to show the people related to the risks, if there are many people for one risk then it will put the people in different rows. Meaning that for risk 2 I will have three rows, because there are three people connected to this rows. See the attached file!
What I would like to do is to have a query which (in case there are more than one risk owners) puts the second name in another column, the third name in another column and so on. So I will have only one row per risks.
The attached file is a dummy file, so there are only maximum three names per risk. In the real file the maximum is five names per risk. So I am talking about no more then five extra columns. (So I am talking about a query which would put the first finding in the first extra column, then the second item in the second and so on till five. It there is no third or fourth or fifht item then the columns remain blank).
Unfortunately I have to do this because our mother company works with excel and they are sticking to this format in excel.
I have a database with a table with company names, then a relationship to another table that shows that companies' address, but I also want there to be an address 2 and 3 and so on, and some of our companies have multiple sites.So what Im asking is how would you be able to show multiple data, on a sing record.
I have a DB where you there's 5 tables all linked together by one project ID
tables below
Project , Staff, Asset, allowances, travel, mark up
What I can do is create a new project, then add records to each of the other tables on what different items I require,
i.e. I create a new project - called project one, in the project table I create a record stating, name, time scale, client and location, then I add different records to each of the other tables on what I require all linked to the same project ID. (probably not explained that too well)
Now I want to create a query that lists all the requirements one after the other this will make it easier to create reports and to calculate costing's.
At the moment I have made 5 different queries listing all the data, then have one report containing 5 sub reports to display the data, no this does work.
I am trying to manage a contract price from month to month. Every month, some portion (or none) of the total contract will be completed. I have a form in which the user enters 'Amt Completed this Pay Period.' Then the 'Total Completed To Date' is automatically calculated by adding the 'Amt Completed this Pay Period' to the 'Previously Completed Amt.' I would then like to use the new 'Total Completed to Date' as the next months 'Previously Completed Amt.'
Basically, I am trying to calculate a value in a form for one entry, and then passing that value to another entry to use. Does anyone know how I would pass this info along?
I'm doing a Schedule Adherence Report in Access. One table has the schedule start time and stop time. The second table has exception times (lunches, breaks, etc). Both tables have the common denominator of a unique Schedule ID. How do I bring them together with repetitive Schedule records from the first table. What I get is the following:
ID Code Time Code Time 1 Open 12:00 Break 2:00 1 Open 12:00 Lunch 4:00 1 Open 12:00 Break 6:00
What I want is the following:
ID Code Time Code2 Time Code3 Time Code4 Time 1 Open 12:00 Break 2:00 Lunch 4:00 Break 6:00
I have drafted a simple VBA code in a form button that takes the result calculated in a query and pastes the result into a table. I have attached my test database to illistrate..In my attached database, i have a list of 5 records, i am wanting to push the calculation button have all the records updated with the result that is calculated in the query in 1 go.
As it stands right now i have to do it one at a time per record, which is not preferable when there are potentially hundreds or thousands of lines. I am wanting to do this because i want to reduce my reliance on calculated cells in forms and reports where i have to draft long formulae across 3 separate queries to get the result i am looking for.
I'm building a database for a realtor friend. Part of his job is keeping track of where his clients want to live. I have added a field named "Areas". I need to populate that field with names of cities where his clients want to buy thier house. Sometimes there are only a couple of cities. Other times there could be more then 10. I don't want him to type these cities in. He is not a good typer, either am I, and he is prone to abbreviations and typos. Garbage in garbage out. I would like to provide him a drop down list, or something like that, of all the cities or areas and have him select each area and then either hit a command button or copy/paste it to that text box. Either way will work. The command button would be nifty. The result would give him the option of doing a form filter and being able to filter that text box for ex: "atlanta" and "syracuse". He then could cue these people when he has a property come available in either one of those cities.
I DON'T understand VBA code. I don't know how to write it or where to put it.
I tried to search this site and I could not find any threads like this, to my amazement. If there is a similiar post out there and I could not find it I apologize in advance.
I need a little help with a new function in our CRM db (Ac2000).
I've made a system for handling sale-prospects. When a sale is done, I want to transfer the costumer info to the costumer table. All this is well, no problems with this.
BUT, each prospect (And costumer) has contacts. There can be several contacts to each prospect, connected with a prospect number.
The challange is to transfer the contacts as well. And assigning the new costumernumber..
Dont bother going techincal right away, but a point in the right direction would be great!
how do i delete multiple records in a table, for example if i have a table that as serial_num 123 twice, how do i delete the two records, basically i don't want to delete one record, i want to delete the two records. Thanks.
I believe this can be done with a query, but am having problems with it.
What i have is a db with a table for employee another table for training items and a third table to tie them both together. These employees are divided into several "teams" (team 1, team 2 etc ...) The training items are assigned to some or all of the teams. ( i know that this is a many to many relationship and i believe that is where i get confused ) Now the problem. What i would like to do is have a form that will allow the user to assign a new training item to teams 4, 5 and 6 and have access automatically create a record in the table. Now that the 20 or so employees that are assigned these training items will have a record that the user can simply fill in a date when the training has been completed by the employee.
I believe what i am looking for is an append query. Is that right?? Have been playing with this one for awhile and have no luck so i would appreciate any help i can get on this one
