Modules & VBA :: Add Rows To Table Based On Record Count Of Another Table
Mar 10, 2015
I am trying to assign teams to players. I have an import table with all of the players information listed. What I want to do is determine the count of players in a given city. For every 9 players I want to add a new record to the Team table and assign the team number (auto incremented for each team created). Then I want to add the players to the Players table with the Team Number that was created.
View Replies
ADVERTISEMENT
Nov 10, 2014
I need to write a code that checks if every 6 lines in a table contains a string. If it contains the string, then carry on looping, but if it does not, it will delete the current line and the previous 6 lines before it. The program should then continue looping through the entire table.
For example,
Code:
Apple
a
b
c
d
e
Apple
f
g
h
i
j
Apple
l
m
n
o
p
q
Apple
.
.
.
I need to find the string "apple" in every 6 lines. So, that means that the first 2 Apples are fine, but for the third one needs to be deleted as it contains 7 lines instead of 6.
View 14 Replies
View Related
Nov 10, 2014
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
What is the best approach?
View 3 Replies
View Related
Oct 4, 2004
I need to do a count of the total number of rows in a table or query. Say I have a table with 7 records, I want to be able to get total number of rows instead of the sum of the row and save it to some other table.
View 5 Replies
View Related
Jan 6, 2015
I'm making a database that so I can log calibration information about equipment every year.I have 2 tables:
The Equipment details table
The Calibration record table
ID Number is shared between the 2 tables so a calibration record can be linked to its' details.
For Example (simplified sample data):
Equipment Table
ID Item Unit Type
104 Thermometer DegC PT100
Calibration Table
ID Cal Point 1 Test Equipment Unit Under Test Date
104 20 21 22 06/01/15
What I want is a button on a form that creates a new blank record in my Calibration record table with the ID number already entered based on what record I selected in a combo box linked to my equipment table. I really don't know where to start.
View 2 Replies
View Related
Sep 1, 2006
Apologies if this is a basic question. Here's my situation:
I'm trying to update rows in a table based on a count of items in the same table. The table in question contains order line items. On each line item, I'd like to store the total number of items attached to that order (because it affects how the individual line items are processed).
I have a query that seems like it should do the trick, but Access doesn't like it:
UPDATE sales AS S1 SET S1.EXPC = (select count(*) from sales S2 where S2.order_id = S1.order_id AND S2.product_code = "EXPC");
Here's the error I get:
Operation must use an updateable query.
Thoughts?
View 5 Replies
View Related
Apr 15, 2014
I have a table with the following columns: Task, Visa type, time it takes to perform the task. There are several taks that are performed for all visa types. I want to create a form to enter data to the table in which for the field visa type I have a list box that can allow multiple values, however, I do not to create a single line with the task and on visa type all the types of visas selected. I want to create a line for each type of visa with the information introduced.
I don't know if this is possible, the reason for which I want for the form to create several rows depending on the visas types is because then I have a query that sums all the types of visas. Can this be possible? I don't want the people to introduce manually directly to the table the data and also that for the same taks they have to enter manually 50 rows with values. I want it to be more simple and easier.
View 3 Replies
View Related
Jan 12, 2013
I have a keys table and a keysctivity table. I need keys to not be available if they are currently signed out (return_date is null) or if they have been marked as lost (lost_key = true)
This is my activity table. URL....If for example signin_id 1 was not returned or was lost, key_id 1 should no longer be available. Is this something that can be done?This is the access file I am working on: URL....
create two new tables "lostKeys" "unreturned_keys" and have records moved to their respective tables based on whether they are indicated to be lost or currently not returned.
View 4 Replies
View Related
Sep 19, 2012
I have a table with the name of a report, the items on the report, and the number of items on the report. I would like to count the instances of the names of the individual reports that appear on another table and then divide that count by the number of items on the report.
View 1 Replies
View Related
Apr 24, 2015
I'm trying to use VBA to update a new column in a table with info I already have in another table.The table I want to update is an inventory details table, it has around 25,000 records. I added a column called "UnitCost", of course the column is empty for all 25,000 records so I would like to fill it easily using DoCmd.RunSQL "UPDATE" feature.
I use that through-out the program however I'm unable to connect the dots for this one.What it needs to do is update "UnitCost" in "InventoryDetails" from "Products" where "InventoryDetails.ProductNumber" = "Products.ProductNumber"
The "Products" table has all the different unit cost, it just need to be placed in the "InventoryDetails" table for every record. Of course product1 needs products1 unit cost and product2 needs products2 unit cost, etc.
View 1 Replies
View Related
Jan 12, 2015
I have a table (tblConversions) that I'm using as a base for replacing values in a different table. tblConversions is set up as such and is made up of nearly 100 records:
ID LABEL CODE
1 Ashaway ASH
2 Barrington BAR
3 Bristol BRI
4 Jesse Smith BUR
....
Another table (tblSysItemLoc) has nearly 1,000 records with a field for Location Names such as Ashaway New Books, Ashaway Fiction, Ashaway Non-Fiction, Barrington DVDs, Barrington Reference, etc. Other fields in this table are just statistics.
What I need to do is loop through tblConversions, and find in tblSysItemLoc where the Location field Starts With the value from tblConversion.LABEL and replace the Entire field with the CODE. For example, from tblSysItemLoc "Ashaway New Books" gets changed to "ASH", "Barrington Reference" gets changed to "BAR", etc.
I feel like I need a loop inside of a loop, but I'm not sure where to begin. Loops are not my specialty.
View 10 Replies
View Related
Oct 5, 2012
I want to split a table into multiple sets based on rowcount. Suppose I have a table having 10,000 records. I want different sets which should have values based on rowcount. Suppose if I select set 1 then the table should populate records from 1-2500. If I select set 2 then the table should automatically give the records from 2501-5000. If i select set3 then the table should have values from 5001-7500 and so on.
View 3 Replies
View Related
Jan 25, 2015
I have a main form users enter info into then submit, which adds it to the table being displayed in the subform...
One of the options is a "Urgent" tick box, if they tick this box, once it has submitted to the table, i need that row to highlight red...
View 3 Replies
View Related
Jun 25, 2014
I am trying to format a spreadsheet to import into a Table. To do this I need to delete the top 8 rows and then the 4 rows below the data I need, both areas contain header data. The 4 rows below the data I need are blank but formatted oddly and it is causing issues during import.
Code:
Sub ExcelFormat()
Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")
excelApp.worbooks.Open ("Z:DataTest.xlsx")
End Sub
View 11 Replies
View Related
Jul 9, 2014
I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.
what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.
I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.
'------------------------------------------------------------
' Master_tbl_sub_fm
'
'------------------------------------------------------------
Function Master_tbl_sub_fm()
On Error GoTo Master_tbl_sub_fm_Err
With CodeContextObject
On Error Resume Next
[Code] ....
View 8 Replies
View Related
Sep 20, 2014
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position
0001 10 30 50 5 45
0002 5 40 47 5 23
0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
ItemId Qty Start inv Aggregated inventory Prioritization
0002 5 23 28 Yellow
0002 5 28 33 Yellow
0002 5 33 38 Yellow
0002 5 38 43 Green
0002 5 43 48 Green
0003 10 5 15 Red
0003 10 15 25 Yellow
0003 10 25 35 Green
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
View 8 Replies
View Related
Aug 8, 2013
I am trying to count rows in the result of one of the queries and I am having a bit of trouble getting it going.
The current code - this is executed as on-click event when clicked on List Box feed with query below.
What I want to add is simple if that when number of rows produced by the querry is 1 it will enable a picture item in the different part of the form, however it does not want to count the rows for me.
Code:
Private Sub search_items_Click()
Me.OBSFullFilledOrdersHolder.Enabled = True
mysql = "SELECT orders.[order id] , STUDENTS.[first name]& ' ' & students.[surname] AS Name, students.[contact name] AS ContactName , ORDERS.[Online Bookshelf order] AS OBS , STUDENTS.[Delivery Address 1], STUDENTS.[Delivery Address 2], STUDENTS.[Delivery Address 3], STUDENTS.[Delivery Address 4]"
[Code] ....
The query itself works when tested but when used in code with DCount function will return error: Run-Time 2471 the expression you entered as query parameter prouced this error
'[Forms]![FULLFILL ORDERS]![search items].[Column(0)]'.
View 5 Replies
View Related
May 13, 2015
I am trying to loop through a table and count the duplicate records only once. Please see the attached file.
1) In the test table, I want to loop through all pt_accts.
2) In the countable column, if an acct repeats, I essentially just want to put "1" in one of the records. For the non-repeating accts, putting in a 1 is sufficient. Basically, I just don't want to count the duplicate ones multiple times.
In the example, acct "aaa" repeats, but there is just one "1" in the countable column. "eee" repeats as well, but there is just one "1" and the rest is 0.
Is this possible via VBA?
View 14 Replies
View Related
Dec 28, 2014
I am very new to VBA and I have been self-learning VBA from two month and have been assigned to new project Work Authorization & Lock Out Tag Out.I have table called tblWA & tblLOTO.Basically most of the LOTO/s are associated with WA #, following example
WA # in tblWA
Associated LOTO/s in tblLOTOs
25258
123456
123457
123458
123459
25259
123410
123411
123412
123413
However, both tables are in relationship right now..I have form called WA Switch Board where I have Datasheet View form with all WA records, fields are WA#, WAStatus, WAIssuedDate, IssuedBy, CompletionDate and etc but I have dropdown with with WAStatus with (In Field Today, On-Hold, In Approval, Cancelled, Close).Now from the main switch board when authorized people try to change the status of permit to Closed I want recordset or count to loop through tblLOTO and give me a message box if associated LOTO/s status not equal to Close.In other word, if the associated LOTO/s are not close then the selected WA # in WA Switch Board cant close.Here is the code I have been playing with no success
Private Sub PermitStatus_AfterUpdate()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
[code]....
View 3 Replies
View Related
Sep 24, 2014
I have a table which specifies the delivery date
I have a from that allows you to choose a year and a month.
I have an unbound textbox which I wan to display the count
I want to be able to count all the records from a table with the year and month specified in the comboboxes and display this in the texbox.
View 4 Replies
View Related
Mar 14, 2014
I wanna extract a variable count of columns and transpose it to another table.
My source table is called FC containing columns like 1 FC, 2 FC .., 12 FC
My target table is called Forecast_Quantities
Tried the following:
Code:
Public Sub TRANSPOSE()
Dim rs As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim varItm As Variant
Dim I As Integer
Set rs = CurrentDb.OpenRecordset("FC")
[Code] ....
Somehow it doesn't recognize the I FC column in the table FC.
View 1 Replies
View Related
Apr 9, 2014
the project I have comprises four seperate databases all linked but kept apart for logic and data reasons. I must have rapidly approaching 300k records across all of them. As a result I am trying to extract on a regular basis (monthly) the dimensions of each database. Specifically, I want to be able to produce for each database;The number of tables (I have two types data and reference, it would be nice to be able to split the result).The number of fields per table.The number of records per tableI am not really interested at this point about other database objects, such as queries or reports.
View 11 Replies
View Related
Jan 13, 2006
A SELECT works:
SELECT *
from mastertable
INNER JOIN secondarytable
ON mastertable.Identicalfield = secondarytable.Identicalfield
But none of these do:
DELETE
FROM mastertable
INNER JOIN secondarytable
ON mastertable.Identicalfield = secondarytable.Identicalfield
**Incorrect syntax near the keyword 'INNER'.**
DELETE
FROM mastertable JOIN secondarytable
ON (mastertable.Identicalfield = secondarytable.Identicalfield)
**Incorrect syntax near the keyword 'JOIN'.**
DELETE
FROM mastertable
WHERE (mastertable.Identicalfield = secondarytable.Identicalfield)
**The column prefix 'secondarytable' does not match with a table name or alias name used in the query.**
DELETE
FROM mastertable
WHERE IN (SELECT *
FROM secondarytable)
**Incorrect syntax near the keyword 'IN'.**
Can anyone help? This should be easy, shouldn't it?!
Thank you.
View 2 Replies
View Related
Sep 19, 2013
I'd like to learn how to do them quickly and effectively in Access. One of those things seems like it would be an incredibly simple, intuitive operation, but it's not, at least not to me.Say I have some data that really should only be on one table, but it's currently on two tables in Access: "List Main" and "List September Adds." They both have the following fields: "Full Name" "Email" "Address" "City" "State" "ZIP" "Phone."
The first table is my main list of contacts. The second contains new info, consisting of 1. a few new contacts, and 2. updated info for a few of the contacts already in table "List Main."
I would simply like to put all the new contacts from "List September Adds" into my "List Main" table, and I'd also like to fill in a few missing e-mails in "List Main" with newly gathered e-mails for those contacts, info that is in my table "List September Adds."I'm sure there is built-in functionality to do something as basic as essentially turning two pages of the same spreadsheet into one. In fact, I know I could import data from an Excel file and have it "append" to a preexisting table if the fields are all the same.
However, I'd like to know how to do it when the tables are already in Access (without having to learn any SQL, mostly because I'm fairly certain I don't need to know SQL to do something like this). I've heard of Append and Update Queries and given their names, it sounds like they'd be useful, maybe with use of "Totals" and "Group By" to get rid of duplicates; however, I can't seem to get any of this to work right.
Mostly, when I think I am doing an Append correctly, it doesn't add new data at the bottom of a table; it just wipes out all the data that was there, and replaces it with data from the source. For instance, when I've been running an Append Query to get my "List September Adds" rows into my "Main List" table, what I end up with is only the "List September Adds" rows, and all the "Main List" rows gone.
View 7 Replies
View Related
Oct 10, 2014
I want to Delete all records from the Table Named "CustomerWiseRotaryTillerRates" (Given Below) if the State in the State Filed in found in the "Customer_Master" Table (attached Herewith). What will be the correct procedure ?
I followed the steps referred in page no. 67 & 68 in the Book "Microsoft Access 2007 Data Analysis" by Mr. Michel Alexender.
Customer Name
State
Product_Code
Product_Name
Price_perunit
TSI Engineering Indutries (P) Ltd.
Assam
P_05
1500_Rotary_Tiller_220_RPM
Rs. 63,722.00
Kishan Krishi Engineering Works
Jharkhand
P_05
1500_Rotary_Tiller_220_RPM
Rs. 62,997.00
TSI Engineering Indutries (P) Ltd.
Assam
P_06
1750_Rotary_Tiller_220_RPM
Rs. 66,663.00
Shri_Tata_Enterprises
Bihar
P_06
1750_Rotary_Tiller_220_RPM
Rs. 65,683.00
View 1 Replies
View Related
Sep 29, 2013
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies
View Related
