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!!!!!
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.
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?
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.
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;
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
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?
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....
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.
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.
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.
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 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?
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.
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
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.
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:
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?
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:
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?
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?
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: 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.
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?
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
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
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.
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?