Refering To The Current Record In A Query
Jun 12, 2007
It is possible to refer in a SQL statement to the "current" or "actual" record?
I want to base the querry in the present value of two related tables. I will try to show you what I have to do, maybe you can give an alternative solution.
These are the tables:
tblTeam (idTeam, TeamName, DayCreated)
tblTeamPerson (idPerson, idTeam)
tblPerson (idPerson, PersonName, MusicPreference)
Every person have a music preference, and the teams have to be formed by people with similar music preference.
So, one way to filter that is to use the following method: everytime I create a team, I choose a person, ok. That firts guy is the one that will give the team his "MusicalPreference". The next members with different MusicalPreferences will be excluded from the query.
But in order to do that, I need to refer to the current team that have 1 member, and use the value of the field MusicPreference in that Person in particular.
I hope you have understand what I have to do, and I hope you can help me with this.
P.S.: I´m using a combo-box with a special query in a subform to select the team members. In the main form I create the team. So it would be great to be able to use that "idTeam" value in relation with the firts team member of that team.
View Replies
ADVERTISEMENT
Mar 15, 2006
Hi All,
Can anybody tell me whether it is possible to use a global variable defined in a vb module as part of the criterea in a query, if not whether there is a work around.
I suppose I could temporarily write the data to a table and use it from there, I thought there would be less messing around this way though.
Cheers
View 2 Replies
View Related
Sep 15, 2005
In my query, i want to automatically display the value of another field from the previous record in a field in my current record. i.e.
Name Value Previous
Record 1 1
Record 2 2 1
Record 3 7 2
Record 4 1 7
Is it possible to create some kind of simple expression to refer to data in another record?
Cheers,
Ben
View 6 Replies
View Related
Jun 10, 2007
hi all, i create a form which have a button to append the current entry to another table but i only want to append the current entry tat is open. So how do u set the query to append one entry(the current open entry on the form) using the criteria.
i notice the query only have sum, aver, +- ...etc..
tks.
View 7 Replies
View Related
May 10, 2005
I have a form with a subform in Datasheet view. I want the user to double click a record in that subform and a query to ammend it to another table.
I want to trap the double click event of the subform but
how do i run a query based on a current record in a subform?
View 3 Replies
View Related
Sep 9, 2005
Hello,
Can anybody see what I'm doing wrong here. I have the following query
SELECT Contacts.Cust_FK, Contacts.ContactNr, Contacts.FirstLastName
FROM Contacts
WHERE (Forms.ComLogDetail.Cust_FK=Contacts.Cust_FK)
ORDER BY Contacts.ContactNr;
The "Forms.ComLogDetail.Cust_FK" part should render the current record in the form ComLogDetail but its not. Any ideas?? I'm an amateur programmer so my apolgies if the solution is all too simple.
View 3 Replies
View Related
Aug 23, 2007
I am designing a contact management system and have hit a bit of a wall. I am farily new to Access so please advise if I am going about this the totally wrong way.
I have three tables; Contacts, Companies, Company_Addresses
Contacts:
ContactID (P)
CompanyID
OfficeName
Companies:
CompanyID (P)
Company_Addresses:
CompanyID
OfficeName (P)
What I want is when adding a contact, to have a drop down list in the CompanyID field showing all the companies in the Companies table, and then in the OfficeName field, have a list of only the offices related to the company selected in the previous field.
I can do the first list, but cant work out how to list only data related to the company selected in the previous field. Any help would be greatly appreciated.
Sorry if this is in the wrong forum.
Thanks. James
View 3 Replies
View Related
Dec 21, 2007
This is my first time posting to a forum so don't hesitate to correct me if I am doing something wrong.
I am trying to create autofill templates based on the worktype.
How do I append data to the current record only!
I have a table that contains all of the job information including a record autonumber.
I have made a form using this table and it includes a subform for charge items from a separate table.
My users are complaining about having to key in every line item and want the form to autofill the subform for charge items based on the worktype field.
I have written an append query which is correctly extracting the worktype field and updating the charge items fields but it is doing so for every record with the same work type. I just want it to update the current record I am looking at.
View 3 Replies
View Related
Jan 8, 2006
right, struggling here.
Basically I have continuous form listing all the standard letters sent out when I double click on a letter, I need to to run a query for me. The code I have put behind the button is:
If ([StandardLetterType] = "Homework") Then
DoCmd.OpenQuery ("qryHomeworkClearMailMerge")
DoCmd.OpenQuery ("MergeHomework"), , , , , acDialog, Me.[StandardLetterID]
DoCmd.OpenReport ("rptHomework"), acViewPreview
Else
'
end if
and in the query I am first clearing the mailmerge yes/no field out which works fine and then running the merge to put the yes back in the current record so the query looks like this
Its an update query with update to yes for mailmerge and under the standard letter ID i have the criteria [Me].[OpenArgs]
is this completely wrong tried the [Forms!][frmFullStudentReport].[standardletterID] but it kept popping the box up asking for the number.
On the form I have the standardletterID and its hidden but there.
Any help would be greatly appreciated
View 1 Replies
View Related
Aug 14, 2007
I have a table of customers who report trouble on their equipment. I would like to compare the date in the current record to the previous record to see if it occurred within 30 days. Can I do this with an SQL query or expression?
View 2 Replies
View Related
Oct 31, 2014
I have a query pulling data from two other queries (qry_Reports and qry_Surveys). Clients may have more than one ReportID, but only one ClientID. I need to query for only the most current ReportID (which is the larger value) for each client to find the surveys for the most recent report. How can I query for only the most recent report for each client based on the highest value of the ReportID per ClientID?
View 11 Replies
View Related
May 3, 2015
I have a table, with a related value in another table. E.g. A Items table with a batch value from another table.
I have a form to enter how many of these items has been used and from which batch number they belong.
The batch number is from a dropdown, and batches can be finished(exhausted) and marked such in the table so they no more show in the dropdown.
All this works fine, until, I go back to a entry which was from a batch that has been finished. The combobox is empty although the (Already finished) batch number is mentioned in the table. This is perfectly normal as my query for the combobox is :
Code:
SELECT ItemBatch.ItemId, ItemBatch.ItemBatchNumber, ItemBatch.Finished, ItemBatch.ItemName
FROM ItemBatch
WHERE (((ItemBatch.Finished)=False)
AND ((ItemBatch.ItemName)=[Forms]![ItemMasterForm]![ItemDataSheet].[Form]![ItemName]));
What I want is to show the current batch number as well. I tried to make this query get the current value, but wasn't successful. I tried to make a calculated field based on the dropdown and show its value.
Is there any way I can show the batch number in the datasheet? I have to use a datasheet and not a form, because there will be many sub records for the main form, and having a form will be very uneasy.
View 3 Replies
View Related
Feb 9, 2005
Searching returned too many relationship questions so appols if the answer was staring in my face.
A = big company database (updated monthly by the big cheeses)
B = small "team" database (continually, manually updated by ground level staff)
on the first of the month A is updated
B contains various records from A, but doesn't link in any way (nothing changes when A changes)
I want to change this - I want a query whereby B looks in A, and updates:
1: the balance (how much the customer is in credit or debt)
2: the identifier (a number used to identify customer treatment - e.g 1 - customer is blind, send brail info, 2- customer always pays on time, 5% discount - this changes day in day out)
So lets say, for simplicities sake there are 3 fields in both databases, the 2 above and a number primary key.
How would I do it? bearing in mind both databases are seperate.
(note/edit)
also note I only want to run the query at the start of the month - as reflecting the info of A would result in the data in A+B being 31 days out of date at the end of the month (as opposed to just A)
(useless info :) )
The idea of this is to reinforce integrity in B at the start of the month - ensuring everything is correct, whilst still allowing workers to update the info during the month (allowing me to make sure the highest value debt is worked first)
View 13 Replies
View Related
Nov 11, 2007
So I've been able to link in a table to my orders table. The thing is that my employer needs me to link to two tables. One that holds the tasks that needs to be done. This is completed. Now I need to link the orders table into a version history table with a sub linked table of features related to each version. The problem is that when I link in the version table then when I alter the table and try to access the table view then I can only asign one relation. Is there a way around this?
View 1 Replies
View Related
Jul 11, 2006
After checking on the validity of data entered in a contol on a sub form using the beforeupdate event focus is moved to a password field on the main form. After entering a valid password I would like the focus to be returned to the control on the subform. How would I do this. There are a number of controls on the subform that would be validated in this way so writing the code once with a variable that holds the name of the referring control would be best.
View 1 Replies
View Related
Aug 1, 2005
I have a custom menu bar that has a couple of menu lists, with a list of five custom commands that i want to run,depending on what is selected.I know you can assign a macro to each command,but is there a way to know which command has been selected.
What i want to do is to have 1 macro that will run several functions depending on what was selected, rather than 1 macro for each button
View 1 Replies
View Related
Apr 30, 2007
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1".
So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record.
I have a report that will use these results to count only the number of records that have a "WWW" in "field_2".
Is this doable, maybe in a query somehow?
I should add that whatever the solution, it needs to be compatible with Access 2000.
View 1 Replies
View Related
Aug 19, 2013
I have two forms both with separate tables
(1) Register and
(2) Payments.
One of the common denominators between them is the URN which is auto-populated as it is an auto number field. My issue is that when I want to add a new record to the payments table using the forms (I can get to the payments form via the register form), I want to be able to identify the record that I am currently viewing within the register and auto populate the URN field with the same number. This is what I have done so far,
Option Compare Database
Option Explicit
Private Sub AttachPaymentDetails()
Call PerformInsert("tblFinancialBudget", "frmFinancialBudget")
End Sub
[code]....
View 5 Replies
View Related
May 6, 2015
I'm trying to make a form that shows what the last record was next to the empty space where you enter a new record.
This is so the user knows that what they are entering is roughly in line with what has come before.
So for example if I was recording temperature every May, I would like a form that has a field called temperature and next to that field I would like to see last year's temperature.
Records:
Date | Temp
2014 | 20.5
2013 | 18.5
2012 | 19.0
2011 | 22.7
2010 | 15.2
So when I enter the record for 2015 I have a box that says: Temp and next to that box is "Last year was 20.5" or something like that.
View 2 Replies
View Related
Aug 8, 2014
I'm struggling with a query to evaluate current and prior record data. I have a query producing 5000 records. I need to group records by Case ID and compare current date record to previous date record to determine if a team and worker name has changed during the year. I need to count how many times a cases is transferred to and out of a particular team from the beggining of the year. i.e., On 1/1/2014-Team1 has an inventory of 500 cases.
During the month 25 cases are transferred into Team1 and 15 cases are transferred out of Team1. So on 2/1/2014 Team1 begins with an inventory of 510 cases. Throughout the year cases come and go from and to Team1 each month so need to figure out how to create a query to count each change. See attachment displaying how the data is listed and how I invision it to work with the In/out column counts.
Specifically, at the begginig of the year (1/1/14) for case ID 1003 you can see it belongs to Mary in Team1 for January and February. Then in March the case is transferred out and went to Joe in Team 3. So for Mary a "-1" is recorded as a negative count for that Case ID. If later in the year the case is transferred back to Mary a "+1" would be recorded. Respectively evaluated for each of the 5000 records to get a total count for each of the teams by Case ID throughout the year.
I've started with DLookup, tried comparing current month to previous month using DMax. It works as long as I only select one case ID used in a separate query but if I use the whole subset (5000 records) it fails. I can't figure out how to group each set of case IDs and then apply the query.
My attempted query: DLookUp("[Team]","[Team_qry]","[Team_qry]![Date]=#" & CStr(DMax("[Date]","[Team_qry]","[Team_qry]![Date] <#" & CStr([Date]) & "#")) & "#")
View 3 Replies
View Related
Apr 14, 2007
I need to write a query which populates an empty field in the current record with information from a specific field in the next sequential record. Any ideas?:confused:
View 3 Replies
View Related
May 1, 2013
I have a query to bring in values, I need to select 2 rows of data but the criteria is as such:
x= starting value on form
now the row of data must match the following criteria previous row to current row(ref temp)<=x And Current row(ref temp)>xnext row to current row(ref temp)>=x And Current row(ref temp)<x
I have dealt with SQL before but how to do the above.The isolated 2 rows of data will then go into unbound boxes on a form from which I will do intercept and gradient calculations.
View 5 Replies
View Related
Sep 5, 2006
Hi all,
Another problem bothering me. Wish someone can help me to solve it. I am doing the 'Edit' Option right now ... As a user friendly propuse, I had try to use a combo box to let user to choose my employee record with all the detail on but the problem is the program 'JUST' list the record they can not go to the current record!! When i try to edit my record, the program is not allow (for sure because they can't have a duplicate record on it). The reason is because the record just stay in the FIRST record but can not go to the current record. I do believe is the problem of i using the way of listing the record as below:
Me.EmployeeName = Me.EmployeeName.Column(0)
Me.IC = Me.EmployeeName.Column(1)
Me.Nationality = Me.EmployeeName.Column(2)
Me.Race = Me.EmployeeName.Column(3)
Me.Sex = Me.EmployeeName.Column(4)
Me.FMU = Me.EmployeeName.Column(5)
Me.Position = Me.EmployeeName.Column(6)
Me.SectionField = Me.EmployeeName.Column(7)
Me.DOEmploment = Me.EmployeeName.Column(8)
Me.DOResign = Me.EmployeeName.Column(9)
Me.Remarks = Me.EmployeeName.Column(10)
So, Could someone help me to solve it like when i can use the combo box to select the Employee record with the details are on as the same time can go to current record... Thanks alot!!!
i do have the confident if i can go to the current record then my edit option can be working properly.
P/S: I had attach the picture... showing my program problem (with the red circle)
View 8 Replies
View Related
May 12, 2005
I have a table called tblFinishedGoods. There are 3 fields in the table:Serial Number, Model Number and Location.
I would like to have a form that will take input from my barcode reader and input the data for the 3 fields, if the serial number is not already in the table.(I have this already working).
But now I would like the same form to also check the table for any serial number that is scanned in...and if it is in the table already, have it bring up the model number and location for that record.
I'm thinking maybe there is something I can do with Serial Number afterupdate. Basically I'm looking for a way to not require the user to input anything via a mouse or keyboard.
If the 1 form could accept the 3 scans:Serial Number,Model Number and Location, and either 1)enter a new record if that serial number isnt in the table or 2)find that the serial number already exists and overwrite the model number and location with whatever the next 2 scans are.
Thanks
View 2 Replies
View Related
Feb 23, 2006
hello,
I have faxing setup through the XP wizard, I setup my report in Access to use the Fax as specific printer. So When I select a record, it generates the Fax wizard which then prompts for the senders name Fax # etc. and away it goes.
that is ok but It would be nice if I could use the Fax number from the record I am sending, either current record or all. I guess the main trick is how would I pass the fax number to the Fax Printer interface?
Is it possible? I've read a few articles on Microsoft Fax for workgroups to work with Outlook, Outlook help says to go to Office update and download it but I can't find it there? Also do the newer versions of Outlook/Access have a better means of doing this? I am running office 2000, but I can get 2002 installed if that is the case.
thanks
Ziggy
View 1 Replies
View Related
Dec 12, 2005
I have a query that pulls all orders out of 3 tables that fit specific criteria.
tblLotInfo.WOSD = Between ([tblLotInfo].[WOSD])<=Date() And [Forms]![frmRaisedPanelLots].[EndingDate]
tblDelivery.Status = <>"On Floor" And <>"Floor Hold" And <>"On Floor LV Losee" And <>"On Floor LV N. 5th" And <>"On Floor ONT Cucamonga" And <>"On Floor ONT Locust" And <>"Shipped" And <>"Installed" And <>"Invoiced" And <>"Completed" And <>"Cancelled" And <>"Returned to Floor LV Losee" And <>"RETURNED TO FLOOR N. 5th" And <>"Returned to Floor ONT Cucamonga" And <>"Returned to Floor ONT Locust"
tblLotInfo.DoorStyle = Like "*eagle*" Or Like "*H/E*" Or Like "*RP*" Or Like "*F/E*" Or Like "*CC-23R*" Or Like "*AR-756*" Or Like "*Deco*"
This query works fine through 12/29/05. Anything date entered after that gives me "NO CURRENT RECORD". I don't know why. We have orders through Feb of next year scheduled. Any help would be greatly appreciated!!
View 2 Replies
View Related
