Records Under Record
Jan 24, 2005Can i Possible to create a set of records under a record..
ie.Linking a table under a record....
I'm :confused: confused about that.... Can any one pls help..me..
ADVERTISEMENT
Modules & VBA :: Delete Of A Record From A List Of Records In A Subform Deletes Wrong Record
Jun 28, 2013When we browse through records in a subform we store the records in the database.When we want to delete a records for example the third record from the five records always the first records will be deleted. How can we delete the records where the cursor is at? When we are at the third record and press the delete button the third record from the list in the subform should be deleted.
Code:
Option Compare Database
Dim FocusBln As Boolean
Private Sub Identificeer()
Me.[Datum Aangemaakt].Visible = True
Me.[Datum Aangemaakt].SetFocus
If Me.[Datum Aangemaakt].Text = "" Then
[code]...
Queries :: List Of Dates And Records With No Matching Record OR Existing Record With Higher Date
Nov 24, 2014I've been asked to get some information from my database and I'm a bit stuck.
I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field
My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.
So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)
I can manage a query that looks at a certain date that it prompts for on each run:
Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));
I would like a query that lists all dates in a range, and shows the same information for each day listed.
General :: Update Record ID To Another Record ID In Same Table And Update Related Records
Aug 22, 2013I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.
In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.
The problem is when both companies already have existing records in the table.
So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?
I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?
Deleting Records That Have Been Checked Or Adding A Record After A Checked Record
Jul 20, 2005Hi,
I was wondering how to delete records that have been checked (through a checkbox) in a form...
Also, I want to add a record after the record that has been checked. I only want this ability to add records available if only one record is checked. Otherwise, if more than one record is checked, the "Add Record" button would be disabled.
Any help would be much appreciated.
TIA.
Insert A Record Between Two Records
Jul 20, 2005Hello
I have created a table of Some machines specifications.
Item Name Type location
1. Sieve156 Sieve 3rd floor
2. Rollermill1 Mill 1st floor
....
...
...
When inserting for example a new sieve machine I want database
add automatically this machine between items 1. and 2. and assign the item
number 3 to rollermill1. but this new record is added to the last row and its
item number will be 3 but it should be 2.
What shall I do for this problem? (in excel it is easily possible to add a row
between two rows.. is it also possible in access?)
thank you
Merging 2 Records Into 1 Record
Aug 24, 2006Can anyone help me with this one ?
Attached is a spreadsheet showing data from an Access table. The Ivd Id field has produced 2 records.
I need to show this detail on 1 row. I have tried using crosstab queries to do this, but there a lot more fields than the ones shown and the processing required is too much.
I have colour coded the data on the spreadsheet to show the 2 records for Ivd Id =36926 (rows 2 and 3 on the spreadsheet), and how they would need to appear in an access query or table (row 16 on the spreadsheet)
Substraction Of 2 Records With 1 Record With No Data
Jun 23, 2005This is a code I used for a stock control form:
"Balance" =[QuantityReceived]-[Stock record table Subform].Form!SumQtyIssued
It is just a simple substraction between a record from a field and a sum of records from an another field contained in a subform.
The code is working fine apart for the following situation:
If there is no data, yet in the subform;the value for the "Balance" which should be Just equal to [QuantityReceived] appears for 1/2 a sec and disappear!!????
Just now, my knowledge is pretty poor with codes but what i guess is that Access start the calculation fine, then realised that there is no data in the subform and then cancel the calculation.
How can you set the value of a field to 0 or cancel that part of the equation if there is no record for that field?
Moving Record Information To New Records
Dec 29, 2005Hi, found this forum looking for an answer to this question. Hope someone can help.
I'm trying to build a database that tracks electronic components. Mostly the database tracks location movements, but also repair issues and other possible changes. I'm thinking the best way is a database that has a record for each move, and any other changes are included in each record.
My question is, is there a way I can easily have the current information be propogated over to a new record, where it could then be modified? Example: I'm looking at a record of an item that was just returned to me. I would like to click a button on the form which would create a new record, then move all currently displayed information into that record.
Thanks for any help.
Forms - Is It Possible To Add A Row/record Between Rows/records
Jul 19, 2005Is it possible to add a row between rows on a form. I am creating a contact list containing phone numbers and groups. I dont want to sort the form out in ascending order, just by group order.
For example:
Joe Teacher (primary school) 12345
John Teacher (primary school) 67777
Ann Teacher (secondary school) 35555
Mary Teacher (secondary school) 388338
Sarah Nurse 373773
Tina Nurse 111111
Peter Footballer 199999
Rob Footballer 888888
Any ideas would be great :confused:
Delete Record Deletes Other Records
May 11, 2006I have something very bizarre happening that I cannot figure out.
I have a form with a subform on it. These are not linked. On the first form is one combo box, the records in the subform are requeried every time the combo box changes with the criteria for the subform based on the selection in the combo box.
I haven't got these linked because I also have an Edit option group selection on the main form which changes the subform from Data Entry = False and AllowAdditions = False to Data Entry = True and AllowAdditions = True. This allows me to use the form to enter new records or to edit existing ones by changing the edit mode.
I also have a delete button on this form with the generic code to delete a record:
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
The record source for this subform is a primary table on the one side of a one to many relationship.
When I am in the new record mode (Data Entry = True and AllowAdditions = True) and I delete the record I am currently entering it works as expected.
When I am in edit mode (Data Entry = False and AllowAdditions = False ) and I delete the record I am on. It deletes the record but any record in the many side (the secondary table with the foreign key) is also deleted as well. Now I'd expect it to delete the foreign key on the many side and wipe the field but this actually deletes the whole record the foreign key is in from the secondary table.
I have checked my relationship set up for these tables and they are fine:
One To Many Relationship
Referential Inegrity Checked
Cascade Update Records Checked
Cascade Delete Records Checked
I get the usual access warning telling me it is about to delete one record. usually you would also expect the warning to include the comment about this deletion will also delete any related records in other tables, but I don't get that.
I cannot for the life of me figure out why the whole record from the secondary table is being deleted and not just the related field.
Does anyone have any ideas?
I hope I have explained it properly. (Tis always hard to put into words a problem)
Thanks in advance,
Daz
Show Two Different Records From Same Record Source
Oct 12, 2006Hi,
I am trying to create a textbox that will show a document number of the last record so when a user clicks add new document he/she knows what document number to enter in next. It must not involve the user looking a subform with the document numbers on it.
Thanks for your time,
Leon
Update Two Records In One Table From One Record In Another
Oct 14, 2004Hi,
I am creating a league table for my soccer team.
I have a database with these tables:
1. teams
2. league_table
3. fixtures
The Fixtures Table has a lookup where it pulls in the opposing teams - home_team & away_team. There is also Home_goals & away_goals. How can I get the fixtures table to update each teams goals in the league_table table where these details are held.
Anyone any ideas on how this is done?
Thanks in advance.
Queries :: Merging Records Into One Record?
Apr 9, 2015My database was working fine before there was an update to my external data source.
This was my original Append Query
INSERT INTO RLCR4000_CODED ( FY, PayPeriod, ProjCode, DelTask, RegHrs, OTHrs, TotalHrs, FYTDTtlHrs, ProjDesc, TtlCost,
FYTDRegHrs, FYTDOTHrs, FYTDTtlCost, TaskDesc, EmpName, ORG, EmpId, CostCtr, PPBegDate, PPEndDate,
Level2Nodes, Level3Nodes, Level4Nodes, Level5Nodes, Level6Node )
SELECT RLCR4000_pp5_RAW.[Fiscal Year], RLCR4000_pp5_RAW.[Pay Period], RLCR4000_pp5_RAW.[Delphi Project Code],
[Code] .....
The data source changed and the result is now I have a bunch of "Near duplicate" records. I built this query and found the duplicates:
SELECT [RLCR4000_-_Labor_Cost_by_Organi].DelphiProjectCode, [RLCR4000_-_Labor_Cost_by_Organi].DelphiTask, [RLCR4000_-_Labor_Cost_by_Organi].EmpId, [RLCR4000_-_Labor_Cost_by_Organi].CostCenterCode, [RLCR4000_-_Labor_Cost_by_Organi].RecordTypeCode, [RLCR4000_-_Labor_Cost_by_Organi].FiscalYear, [RLCR4000_-_Labor_Cost_by_Organi].PayPeriod, [RLCR4000_-_Labor_Cost_by_Organi].RegularHours, [RLCR4000_-_Labor_Cost_by_Organi].OvertimeHours, [RLCR4000_-_Labor_Cost_by_Organi].TotalHours, [RLCR4000_-
[code].....
There is a field called "RECORDTYPECODE" that was inserted in the external data source. It has one of four values : R, L, N, C
Ideally this is what I want
WHERE ((([RLCR4000_-_Labor_Cost_by_Organi].DelphiProjectCode) In (SELECT [DelphiProjectCode]
FROM [RLCR4000_-_Labor_Cost_by_Organi] As Tmp GROUP BY [DelphiProjectCode],[DelphiTask],[EmpId],[CostCenterCode]
HAVING Count(*)>1 And [DelphiTask] = [RLCR4000_-_Labor_Cost_by_Organi].[DelphiTask]
And [EmpId] = [RLCR4000_-_Labor_Cost_by_Organi].[EmpId]
And [CostCenterCode] = [RLCR4000_-_Labor_Cost_by_Organi].[CostCenterCode])))
Then R+L+N+C=
NOTE and in some cases the fields are null
I have attached a sample of the data. And the second tab is desired result. I have over 9000 near duplicates so it is imperative I create a formula to merge them
Queries :: Find Records Without Associated Record
Aug 3, 2013I have the two tables in my access DB:
Property Information
Lease Information
I need to write a query which shows where a property does not have an associated lease, or where there is no current lease associated with it.
Property Info:
PropertyID
Address
Lease Info:
LeaseID
LeaseStartDate
LeaseEndDate
PropertyID (linked field to property table)
How to show expired / not current leases, but can't seem to show where there has never been a lease on a property.
Queries :: Sum Records And Put Result In Next Record
Mar 27, 2013I have record like below:
id s q
1 11 11
2 14 15
I want to sum record 1 and record 2 and put them in next record.
Split One Record Into Multiple Records?
Mar 14, 2013I have a client who has data like this in an access table:
itemID
itemName
itemQuantity
1
Corvette
1
2
Mustang
2
3
Camaro
5
He wants to append the data to another table so it looks like this:
itemID
itemName
itemQuantity
1
Corvette
1
2
Mustang
1
2
Mustang
1
3
Camaro
1
[code]....
Delete Record Only If Child Records Don't Exist
Aug 6, 2005Hi,
I'm trying to have a button on a form that would delete a record from a table only if there are no child records linked to it.
If there are child records for the selected record, the button would be disabled.
Is there a simple way of doing this?
TIA.
Records In Table By Field, Not Record Number?
Oct 5, 2005I am pretty new to MS access and have a question that I have not been able to figure out. I am making a database with a form to input the data in to the database. The only thing is I want to be able to search/lookup/record data by the products ID code and not by its record number. For example, product 1234 instead of entry number 1. How would I go about this? Thanks for your time!
View 1 Replies View RelatedGrouping And Summing Mltpl Records To One Record
Jun 19, 2006:confused: Hi everyone,
I have one table that I want to query. It's a basic table that contains:
MYKEY, AMOUNT
1234430, 100
1234435, 200
1234435, 100
1264435, 300
1264430, 200
1154435, 100
1154435, 219
1294430, 983
2010, 845
........ so on and so forth
Now, I simply want to GROUP MyKEY ENDING with 4430 and 4435 into one line and SUM the AMOUNT, thus showing like this:
MYKEY, AMOUNT
1234430and1234435, 100+200+100 --> (123 being the first common group)
1264430and1264435, 300+200
1154435and1154435, 100+219
etc etc.
i tried using simple sum and group by but does NOT work:
SELECT MYKEY, SUM(AMOUNT)
FROM MYTABLE
WHERE MYKEY LIKE ('*4430') OR ('*4435')
GROUP BY MYKEY
It does NOT ROLL-UP the 4430 and 4435 together.
QS: do i need to use VBA for this?
QS: do i need to have subqueries?
QS: please help?
Thanks!
Excluding A Single Specific Record Among Many Records
Sep 13, 2007Hello Query Expert
Hi I would like to exclude two specific records from my recordset.
For example suppose I have a Flights Database and I want to see all the flight records for Aircrafts 132 and 232 only, between the date jan 1, 2007 to Sep 12, 2007.
The above I know how to do achieve...
([FltNum] = 132 or [FltNum] 232) AND ([FltDate] >= Jan 1, 2007 and [FltDate] <= Sep 12, 2007)
However, among this set of records I want to exclude flight 132 on Aug 1, 2007 and flight 232 on Sept 1, 2007.
([FltNum] = 132 or [FltNum] 232) AND ([FltDate] >= Jan 1, 2007 AND [FltDate] <= Sep 12, 2007) AND NOT ([FltNum]=132 AND [FltDate] = Aug 1, 2007 ) AND NOT ([FltNum]=232 AND [FltDate] = Sep 1, 2007)
I haven't been able to accomplish this using the query grid. Is it possible or do I have to write SQL?
How would you write this in SQL?
Thanks so much.
Adding New Record With Table That Has Duplicate Records
Jan 26, 2005My situation:
I have a table with duplicate ids. When I have a forum popup another form, the forms are linked by ids. This works great except for one problem. There are duplicate ids. So if I pop up a form then it will go to the first record that that id exist. This is not good if you are going to add another record. Therefore, I need to create a unique variable for a record. The autonumber would be a great variable but it seems I cannot use it to filter. Unless someone can show me how. Is there another way to create a unique variable automatically for a new record?
Open A New Record Without Showing The Existing Records. THank You
Jun 27, 2006Hi all,
How to open a fresh form without showing the existing records in the DB.
I.E I've 5 records in the DB. When I click on the add new form command button
Code: DoCmd.GoToRecord , , acNewRec
I want to have a fresh piece of form + I dont want the records which is in the DB to be shown.
Is that possible.
Thank You.
Adding A New Record, But Not Allowing Old Records To Be Edited
May 6, 2005I have a problem that should have an easy solution. But I can't find it.
I need to have a user add a new record. (Created a form with the fields on it, No problem , so far.)
The problem is when the user is adding a new record if they hit the PAGE DOWN Key or the mouse scroll, they then go to a new NEW record. (And if they are not paying attention they now have two new records)
How do I prevent this.
Thanks
Mike Lester
General :: How To Divide 1 Record In Multiple Records
Nov 12, 2013I have an order table
order nr quantity.
100 110
and a pack table
pack quantity
1 1
1 2
...
1 25
2 1...
I want to make a query that returns this :
order nr pack quantity
100 1 25
100 2 25
100 3 25
100 4 25
100 5 10
Queries :: Combine Several Records In A Table Into One Record
Jan 22, 2014How can I combine several records in a table into one record?
Suppose that I have a table like Table1 in the attached image.
Then I want to combine all records with the same value for Key1 in one record.
The result is shown in Table2 in the attached image.
I would prefer to do it using SQL only, but I guess that this is not possible. Is it possible?
Alternatively I could accept to turn to VBA that could do it. Any good links about this?