Query To Pull First & Last Record?
Jan 4, 2007
I am having trouble. I have a log table and I am trying to pull two records at once. The records im trying to pull is the most recent record update as well as the original record.
Example:
I want to pull for Toysrus the first log intry for this company when I first put the record in. And I want to pull the most recent update that I did for this company. Is there a way to pull both records at the same time? The first and the last?
Any help would be appreciated. THANK YOU SO MUCH!!!!!
View Replies
ADVERTISEMENT
Jan 20, 2014
I have a form that is used to book a new event for a client who is already in the database.Within that form I have a subform that is based on a query which displays information from that client's previous event. I did this using a solution found in the following thread: URL...It is based on pulling the second to last record that is related to the current client. It works perfectly when booking a new event that has taken place on a previous date. However, if the previous event occured on that same date (but at a previous time), it doesn't register.
I would prefer a query that would pull the record previous to the current one, instead of pulling the second to last record out of all that client's events.it would also mean that if more events are booked, then a past event is opened in a form, the sub forms in that form will display the event just prior to the current record.
View 14 Replies
View Related
Dec 31, 2013
I have 2 tables. One has employee info and the second has time data based on thresholds reached in the employee info. Obviously, a basic join will bring in all of the threshold records. How do I get it to only pull in the one that satisfies the threshold condition?
View 5 Replies
View Related
Feb 28, 2005
Sorry if this has been asked before but I have searched and couldn't find an answer
I have a string that is set on startup (username) and have another form(hidden) that loads on startup this form contains information on what forms and options the user can access. Just having a little trouble using the string to pull the correct record, the information in the string would be from the primary key field on the table.
Thanks
Ryan
View 2 Replies
View Related
Jul 31, 2014
I have two tables, one table Data has member(SUBSCRIBER_ID) data including service rep(UserName). Another table ServCoord has data including service rep and their manager. What I am trying to do is pull 5 random accounts per service rep. I can pull 5 random accounts, but not per service rep. I have currently 77 service reps. Is there any way I can pull 5 random accounts per service rep? Here is my starting point so far.
SELECT TOP 385 [Data].SUBSCRIBER_ID, Rnd(1) AS Expr1, [Data].UserName, Rnd(Len([UserName])) AS Expr2
FROM ServCoord INNER JOIN [Data] ON ServCoord.ServCoord = [Data].UserName
GROUP BY [Data].SUBSCRIBER_ID, Rnd(1), [Data].UserName, Rnd(Len([UserName])), [Data].SUBSCRIBER_ID, [Data].UserName
HAVING ((([Data].UserName)<>"NULL"))
ORDER BY [Data].UserName, [Data].SUBSCRIBER_ID;
View 2 Replies
View Related
Aug 10, 2005
Looking for an automation solution
currently I have a database for client progress and prescriptions. when the file is opened it is set for data entry so that a clean note form is opened. I have a button that pulls up past notes for their review.
it has been requested that there be a way to pull the last narative field forward to the new record for editing as a number of the features of the narative remain the same.
Currently the only way is for them to open the last seen note hightlight the field and past into the new note. to high level for some of them.
Need to make this an automated feature where they would just click on a button and the process would all occur behind the scean and the note would apper in the new note narative box.
I know that I could tell the form to not be for data entry but then they write over the last note. this needs to be a new record.
Any suggestions greatly appreciated
J
View 1 Replies
View Related
Aug 28, 2013
I built a report that provides all of the open support tickets. My idea is to have a button (Which I called "WorkIssue_Button") that displays beside each record that is populated on the report... I need this button to be able to pull the ID number of the record (Called "ID"). I then plan on opening a form that is preloaded to this record. How to pull that information?
View 5 Replies
View Related
Mar 24, 2005
I'm a complete newbie at Access. A friend (she's the Secretary of our small town's Service District Board of Trustees) asked me to develop a database for keeping records of property owners here, to facilitate such things as sending the yearly fee letter, tracking mailing addresses and property addresses, whether the fee is paid, etc.
I figured out most of it by "taking apart" a database she uses for another organization. It wasn't easy, but it's all working EXCEPT the thing she uses most: a last name input form (a combo box entry field with a command button) which is supposed to bring up the appropriate "Edit Existing Owner" form by the last name entered (or give an error message if there's no owner by that name, of course). The form works - but simply brings up the last-entered record.
I've looked at the setup in the other database, I see that when I create this form relationship, the VB code is nowhere close to what's in the other database. Trying to use the code from the other database is ineffective, even though the relationship is exactly the same. I'd be happy to have someone look at the forms etc. but the database compressed to a .rar file is 818k which is pretty big - and I'm not sure how to provide just the parts which aren't working.
I can upload the database to webspace and direct someone there if they'd be willing to take a look....
Thanks for any help!
View 14 Replies
View Related
Dec 5, 2012
I want to use a Form or Report to have the end user enter say a Customer # or the Customer Last Name and then have Access pull and display that record so that the end user can than print all the saved information from that record.
View 2 Replies
View Related
Aug 22, 2014
I am attaching a picture of what I am talking about as this is going to get confusing...at least it is for me.
I have a Navigation Form that holds 7 tabs that pull reports or data entry forms.
I am trying to get the Preview Selected Record function to pull the UniqueID field from the Data Entry Form and generate a report for printing.
I can search by UniqueID in the data entry form with no problem, it is linked to two subforms with no problem.
Here is the code I have used most successfully:
Code:
DoCmd.OpenReport "rpt1", acViewPreview,, "UniqueID =" & Forms!MainForm!NavigationSubform.Form.UniqueID
This actually works, but every time the print preview is closed, it crashes Access. I have researched this particular issue, and some of the solutions I have read and tried lead to "that method is not allowed or supported" errors or Run-Time 438 errors.
View 9 Replies
View Related
May 22, 2006
I would like to build a query and ask it to choose a boat name out of a list of pre set names. When the pop up box appears to type the name, i would like it to be a combo box where the name can be pulled from the list. can this be done? thanks in advance.
- Andre'
View 1 Replies
View Related
Nov 1, 2006
I was wondering was it possible to have my queries in a pull down list and I hit a button and the query/report is ran? Or do I have to stay with about 6 or 7 buttons on a form?
I know it sounds crazy.....
View 2 Replies
View Related
May 22, 2007
I have a query that I need to always pull data from the newest past 3 months, so if this query were ran today it would take from months 2,3,4 (Feb, Mar, Apr). Any clue on how to do this?
View 14 Replies
View Related
Nov 3, 2004
Hello,
I am building a query that pulls from one databases but I am running into duplicates.
Here is the skinny:
From the [tbl_databases], I am setting criteria that restricts a column containing “Visio”, more specifically, “Not Like “*Visio*”. I am also setting criteria that restricts a column containing “OLD” (it is a Yes/No field).
.
The problem is that there are duplicate rows that contain both “Visio” and “Old. So, simply suing my criteria restriction are pulling out the Visio and Visio/Old orders as well as Old and Old/Visio order.
Can I create a subquery that contain the combination of “Visio” and “Old” to restrict the subset from being extracted twice?
I think I have the logic but not sure not to set it up.
Thanks
Tuktuk
View 1 Replies
View Related
May 5, 2006
Greetings,
I have a table with the following 2 fields: Make and Model.
When I am in data sheet view I have Make set up as a pull down menu with the various distinct options (i.e. Ford, Chevy, Toyota, etc.). I would like to set up the table so that when I click on Ford, for instance, it will query a different table (carclass) and output only the Ford vehicles on a drop down menu for the Make field to choose from. (i.e. Mustang, Freestar, Focus, etc.)
For Model, I have the following code for the Row Source:
select model from carclass where make=make(from the user input of the make).
Any help or suggestions would be greatly appreciated.
Thank you in advance for your assistance.
Cheers.
John
View 2 Replies
View Related
Sep 26, 2007
Hello,
I have a form with a subform. I have the subform running a update query on a command button in the subform. The subform has the textboxes and I cant seem to get the query to pull the data from those textboxes. I have used the normal [forms]![form_name]![textbox_name] in the past but everything I try to pull the subform textbox data is not working.
I have tried
[forms]![form_name]![subform_name]![textbox_name]
[forms]![form_name]![subform_name].[form]![textbox_name]
[forms]![form_name]![subform_name].[form].[textbox_name]
Nothing works. What is the correct syntax for this? Any help would be great !!! Thank you in advance.
Joe
View 2 Replies
View Related
Jul 4, 2014
Is there a way to pull data into my form so that I can see current stats about my table in the same box as where I'm entering new data?
View 7 Replies
View Related
Nov 6, 2007
I am trying to run a query to select the most recent balance from a table that lists balance by date and account number. Basically I need to select the most recent balance for a given account number. Here are the details:
Table: CBALANCE
=========================
ACCOUNTNO
BDATE
AMOUNT
What works: This gives me the most recent account, but I cannot get the amount
=========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO;
What I want to work: This returns results which should not be in the result set (older balances)
==========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT;
Am I trying to do something too complicated for the query language?
View 5 Replies
View Related
Nov 6, 2007
I am trying to run a query to select the most recent balance from a table that lists balance by date and account number. Basically I need to select the most recent balance for a given account number. Here are the details:
Table: CBALANCE
=========================
ACCOUNTNO
BDATE
AMOUNT
What works: This gives me the most recent account, but I cannot get the amount
=========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO;
What I want to work: This returns results which should not be in the result set (older balances)
==========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT;
Am I trying to do something too complicated for the query language?
View 6 Replies
View Related
Jun 24, 2013
I have two tables: Table #1) [Request Table] Customers request our company to stock a certain Part Number with a minimum value. Ajax - P/N "NP4i" - Min "30".
Table #2) [Sales History] All sales purchased and the "Qty-Total" purchased. For simplicity Fields are limited.I created a CrossTab Query that displays from [Request Table] P/N's requested/ min values (Row Headings) then showing P/N's purchased [Sales History] Value="Qty-Total" Sum and
Column=Format([Invoice Date],"mmm/yyyy") group by, showing last twelve months.
Ex: Customer name, Part Number, Minimum, May/2013, Apr/2013, Mar/2013 and so on.
AJAX, NP4i, 30 4 21 5 and so on.
The Crosstab query list only requested Part Numbers that have at least One sale wihin the last twelve months.If there are NO sales, the crosstab query will NOT show this Part Number.My boss wants to see the requested Part Numbers that were never purchased along with the ones purchased.He wants to address the NO sales, yet they requested we stock that particular Part Number for them.
Is there any criteria I can use to force the Part Number field on the [Request Table] side to populate even though there were NO sales within the last twelve months?
View 1 Replies
View Related
May 21, 2015
I have a perplexing problem. I'm creating a simple db to enter borrower audit checklist data with the following tables:
Questions -- A list of 17 pre-defined audit questions, with fields QNum and Question
Audits -- One record for each audit (pk AuditKey is an Autonumber), and some borrower fields
AuditQuestions -- Linked to Audits, with pk of AuditKey and Qnum, and a Question and Answer (yes/no) field
The Audits form has the Audits table as its datasource, and an AuditQuestions subform. When I go to a new Audits record, there are initially no subform records attached. When I enter a borrower name, the field AfterUpdate event runs the following code:
Code:
DoCmd.OpenQuery "LoadAuditQuestions"
Me.AuditQuestions.Requery
The SQL for LoadAuditQuestions is
Code:
INSERT INTO AuditQuestions ( AuditKey, QNum, Question )
SELECT [Forms]![Audits]![AuditKey] AS Keyval, Questions.QNum, Questions.Question
FROM Questions
ORDER BY Questions.QNum;
I start out with both Audits and AuditQuestions tables empty. When I run the code by entering a borrower name, I get, "... can't append all the records in the append query ... didn't add 17 records due to key violations". It acts like it's getting a null value from Forms!Audits!Auditkey, but if I select the Debug option and check the value from the Immediate pane, it shows a valid number. Also, if I run the query manually with the form open, it loads the questions correctly.
The first Audits record is loaded with questions in the subform so you can see the desired results. To see the problem, go to a new record, enter a name, and hit tab. Another piece to the puzzle is, if you do this on an existing record, it works fine: Click No in response to the error message that appears, then click End on the Debug message. Go to a different audit record, then come back to the one you just created. Change the name, and it works correctly. It's as if it doesn't know what the AuditKey is the 1st time, but if you check it in debugger, it is loaded. In fact, I put in code in the AfterUpdate event to plug the LoanNumber field with the Forms!Audits!Auditkey value, and it worked, but the subsequent query still failed.
View 3 Replies
View Related
Jul 28, 2014
I have a query built that gives me info per employee. I built a question into the criteria that ask for the employees name. When a person types the name results are given. If the person mispells the name, results are not given.
I have everyones name on a seperate table. How do i create a pull down menu on the query to choose the names?
View 7 Replies
View Related
Mar 18, 2014
I have one table let's call is table1. It contains about 5 columns with standard employee data with about 10,000 rows of data containing about 1,150 employees. All employees have multiple rows of data. There is one column titled JOB. I need to pull all rows of data for each employee ONLY if there is at the minimum one value for the employee in the JOB column. I do not want to pull employees that have no values in the JOB column. They can be excluded from the query.
Example of all record for one employee. I need to pull all rows of data ONLY if the employee has a value in the JOB column.
Name EE_ID JOB CAT EFF_DATE
John Doe 1006 CLERK F 01-JAN-2010
John Doe 1006 P 21-JAN-2010
John Doe 1006 CLERK P 01-FEB-2011
John Doe 1006 F 01-MAR-2011
John Doe 1006 P 01-APR-2011
John Doe 1006 CLERK F 01-JUL-2012
View 3 Replies
View Related
Mar 18, 2014
I have one table let's call is table1. It contains about 5 columns with standard employee data with about 10,000 rows of data containing about 1,150 employees. All employees have multiple rows of data. There is one column titled JOB. I need to pull all rows of data for each employee ONLY if there is at the minimum one value for the employee in the JOB column. I do not want to pull employees that have no values in the JOB column. They can be excluded from the query.
Example of all record for one employee. I need to pull all rows of data ONLY if the employee has a value in the JOB column.
Name EE_ID JOB CAT EFF_DATE
John Doe 1006 CLERK F 01-JAN-2010
John Doe 1006 P 21-JAN-2010
John Doe 1006 CLERK P 01-FEB-2011
John Doe 1006 F 01-MAR-2011
John Doe 1006 P 01-APR-2011
John Doe 1006 CLERK F 01-JUL-2012
View 3 Replies
View Related
May 4, 2015
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.
How do I do this?
View 1 Replies
View Related
Mar 13, 2014
I have 9 seperate tables - each of the tables has similar headers
Claim #
Agent
Pass/Fail
Request type
Record Date
I have built relationships between all the tables. I am attempting have a singular query be run based on start and end dates under "record date" . I have the criteria already set. But when I run the Query no information is pulled. How do I get the query to pull this data from all 9 tables?
View 2 Replies
View Related
