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 a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
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
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 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];
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 would like to know if it is possible to pull the max and min start date and end date. Basically i have an option where by the user selects the desired date range that they would like.
Now I would like to know how to get the earliest and latest date range should they not select a date range. My date range is being displayed at the top of my report.
I need to know a query to pull information from two date fields into one. I have a BuriedStartDate field and an AerialStartDate field. In a third field I have PlacementStart. I would like to put the date into the PlacementStart field which ever is the oldest date bewtween the BuriedStartDate and AerialStartDate. It has been awhile since I have done any queries on Access 2007. I am completely self taught in Access.
We're looking at a way that we can easily display what stage our clients' email marketing campaigns are at - in one section of our CRM our Campaign Manager will enter information on whether the campaign has broadcast (ie: emailed out to the required circulation list(s)), if we're waiting for artwork from the client or if we're chasing for that information.
I have four text boxes (date format) hidden on a subform that I need to pull ONE max date from.
So, as an example:
Email1 (our first email to the client chasing for artwork) = 01/01/2015 Email2 (our second email to the client chasing artwork) = 08/01/2015 Email3 (our third chase email) = 15/01/2015 Broadcast (the date the email campaign was finally sent) = 29/01/2015
I would need some code to show in an unbound textbox "Broadcast: 29/01/2015"
But, on the other hand, if the dates looked like this:
I would need some code to show the following in an unbound textbox "Last Chased: 08/01/2015"
So we can easily see the status of our marketing campaigns at any stage in the process.
I've tried using IF THEN ELSE statements in the subform's On Current event, but that populates the information from the record you've selected across all the other records on the sub as well.
So I'm wondering if it's worth setting the Control Source of the unbound textbox I want to display the campaign status in as a bunch of nested IIF statements instead?
I am trying to create an expression to pull data from 2011 to present date. I need the data for 2011 to only reflect 1/1/11 through 10/7/11 (today's date in 2011). I need the same for 2012 and 2013.
I don't want to have to enter dates each time I run this, therefore, a formula would be preferred rather than hard numbers.
I am trying to create an expression to pull data from 2011 to present date. I need the data for 2011 to only reflect 1/1/11 through 10/7/11 (today's date in 2011). I need the same for 2012 and 2013.
I don't want to have to enter dates each time I run this, therefore, a formula would be preferred rather than hard numbers.
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 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.
Code BrandName Lead Free Nickel Free 001 AAAA Yes Yes 002 AAAA Yes No
On a form the user selects the code field and in this table the field is called BRAND. On the same form, I need to display the value in the Nickel Free field so if selects 001, the field on the form needs to be "Yes", if the user selects "002", the value needs to be "No"
I am brand new to building a database.What I want is a database to store Quote, Job and Invoicing information. We receive quotes first and then they can, but don't always, turn into jobs. We can also receive a job without quoting it. We currently have two spreadsheets. One is for Quotes and the other is for Jobs.
QUOTES INFORMATION Quote # Date Customer Name Part # Part Name Quote Due Date Qty Lead Time Price Unit
JOBS INFORMATION Job # Qty Quote # Customer Name PO# Part # Part Name Est Hours Start Date Due Date Price
As you can see a lot of the information in the Quote spreadsheet is also used in the Job spreadsheet. (Bold represents duplicated items) We currently type the information into each spreadsheet.
Then there are different forms that are filled out for quotes and jobs that contain the information in the spreadsheets.Is there a way that I can have the QUOTE Table automatically populate the JOB Table information?
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 am having trouble with our receiving database. This database consists of two tables. One for vendors, which basically contains their vendor ID as well as vendor name and phone # etc. The other table is our receiving data table. When our receiving person receives product in, they log this in the receiving table including info such as date, autonumber for record, vendor etc. The problem is, when the person selects the vendor id, which is set up as a lookup field, we would like to have the vendor name pop up atuomatically within that record. I cannot for the life of me figure out why this isn't happening. The person is using a form to enter all of this data and runs a summary report at the end of the day. Our accounting dept. is requesting this info be added, but I cannot seem to figure it out. Thanks so much for any assistance! Amy (monet1369);)
v sorry for the basic question, but ive been banging away at access and i cant my head around this..
i need to run a simple query. the query/ search will ask users to enter in the number of a document. i want, when this code is entered, for 2 controls on the form be updated with codes that are stored in a table based on the code they searched for originally.
heres my table structure... Table A Doc ID (PK) Doc No (manually input and is the search item that users enter)
Table B Unique ID (PK) Doc No (FK) Info (to populate field 1) Info (to populate field 2)
how do i perform this in the query section? do i need to manually code the SQL required, or is this query basic enough that i can just select the fields required in the design view of the query?
I have a slight problem here with a database program I am designing. I have two excel spreadsheets, one created by one department here, and the other created by a second department.
I have the access program importing the relevant fields from both programs into 2 tables. Table 1 is the listing of employees in the company and their info (minus the location where they work). Table 2 is the listing of employees who are in this facility.
I need to prepare a report of all employees in this facility who appear on the other table.
I have a field in each table (both called F1) with the employee names.
Is there a way I can run a report in Access where it pulls all the info I need into a Report as long as the name appears in both tables F1 fields?
Even if I can create another table based on that info, and then just do a full dump to a report from that new table, that would be great.
I am not familiar with VB or SQL really, but I figure something like this would be doable, I just don't know how to go about writing it :
If Table1.F1 == Table2.F1 Then { Append Table1.* INTO NewTable3 }
Something where if the name on F1 in Table1 matches the name on F1 in Table2, it takes all the data from that record and appends it or dumps it brand new into a NewTable3..
I have a form I use for data entry, it needs to generate an id called RO Number and i need it be generated by access starting at number RO129036 and then keep adding sequentially, so RO129037 etc etc.
as i already have data in my DB that i need i cannot just reset any fields
having a table with just one field - the numeric part of the RO number so first one would be 129036 - so i need the form to pull this field, add 1, and then add "RO" at the beginning of it? i have really been struggling with this database
I am trying to add an attendance records to my database but cannot figure out how best to do it..I already have a 'children' table were all the kids info is stored and have created a 'roll' table.
i want to be able to open a form and search first and/or last name from the 'Children' table, then be able to save both first and last names and the date into the 'Roll' table. (then open reports etc later based on dates)how to pull data from one table and store it in another.
I have 3 tables invoice, customer and items. I need to create a query that will get the next invoice number from the invoice table and mark that invoice number in the items table for a set customer where the invoice field in the items table is blank.
The invoice table is now set up as a autonumber, therefore I need to append to get the last new number.
The items in the item table are unique and this is why I want to mark the invoice number in the item table field invoice.
I have a table that has four columns. Column 1 had people's names, column 2 has their email, 3 has a category, and 4 has their office.
I have a form with radio buttons, When you check a radio button and press OK it will display the e-mails for the people selected. Problem comes when it's by category, since categories are not unique to one person. I want to click the category radio button and have it display every person under that category, concatenated with a comma.
Problem is I can't make a listbox because it will show the same categories several times instead of just once, so I want the radio button with the category name on its label, and then in the code I need to tell Access to go to the table and search for the e-mails from a specified criteria, the category.
