Modules & VBA :: Efficient Comparison Of Multiple Fields On Different Tables
Oct 7, 2013
I am wondering if there is an efficient way to compare two fields from one table to another two fields from another table. So basically
Code:
If targetTable.Field1.Value = sourceTable.Field1.Value And targetTable.Field2.Value = sourceTable.Field2.Value Then
targetTable.Field3.Value = sourceTable.Field3.Value
The problem is that I need to run this for all entries in targetTable. The only I could think of was to use 2 nested for loops (one for target table and one for source table) as outlined in the following (my data is currently in Excel, but I want to import it to Access)
Code:
For i = 2 To 5754
For j = 2 To 3500
If targetSheet.Range("I" & i).Value = sourceSheet.Range("AR" & j).Value And targetSheet.Range("K" & i).Value = sourceSheet.Range("AS" & j).Value Then
targetSheet.Range("I" & i).Value = sourceSheet.Range("AT" & j).Value
The above code works but it is really slow (takes about 12 mins on a high-end CPU).
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _ & " FROM Table1 " _ & " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _ & "FROM Table2 " _ & " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
I'm thinking of 2 different ways, but not sure how Access will handle them.
1) A table that maintains the start and stop date of the relationship (i.e. employee has a job title from a start date to an end date).
This is the ideal, but I'm concerned about the number of records. The database will store 3,000 employees and I'd estimate around 2000 changes a month can occur to the employee data (transfers, hires, promotions, terminations and all cascading changes on dependent information).
2) A different database for each month/year. (i.e. Employees_March2013, Employees_April2013)
I don't have concerns about the number of records, but I'm not sure how the front-end will work with multiple back-end databases. Is there an easy way to setup a form to choose which "effective date" of employee information you'd like to choose and have it link to the correct back-end at that point before running a query/report?
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
I'm trying reference a dcount on two fields, one is a string and works fine, the other is on a date which does not.
I've investigated the problem and found a couple of references to making sure I use a # symbol to reference the date but I think the thing that's throwing the comparison out is that the date field stores time as well, even if you can't see it?
I thought should count the records in the healthcheck table that match the check_date field against the first_health field on the current form, but I keep getting a 0 count even though the dates seem to match.
I am working on a database which has two tables used as part of a registration and login process.
I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.
The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.
I made a database that in one of the forms, I like by clicking on a button the user be able to select 5 excel files with different file names (in the same directory) and then based on the imported file's names, it be stored in 5 different tables.
At the moment by using the bellow code, I can import multiple files (with the same formats) only into one table . My vba code comes as follow:
Function GetAllFiles() Dim fd As Object Dim strFilter As String Dim lngItems As Long
Const msoFileDialogOpen As Long = 3 Const msoFileDialogViewDetails As Long = 2
I am trying to setup a database for vehicle stock control. Im not sure if I have gone about this the right way as I am new to this but thus far it is working correctly except for one annoying problem. The database consists of so far
In the Vehicle Details table a stock number has to be manually added as this will be used for new stock as well as current stock (Number range from 100 - whatever) "IAWVehicleNo". This table contains all relevent data with reguards to make, model, bodytype, color etc. The sellers table contains the details of the seller Name, address, Phone, LicenceNo etc. The Buyers table contains employee names. The States table contains all Australian states. The Status table contains current vehicle status Retail, Wholesale, Wrecking etc. The Sold table contains the details of the person who purchased the vehicle if sold. The Safety details table contains a safety checklist for pre purchase inspections eg: Headlights yes/no checkbox, Headlight text field for any extra info. It also has a field for a safety Certificate No once the vehicle is checked and recieves a Safety cert. The forms are setup as frmIAWVehicleDetails frmIAWSellers Subform frmIAWVehicleSafetyDetails frmIAWVehicleSafetyDetails Subform
The Vehicle Details,Sellers,Sold & SafetyDetails tables all have the IAWVehicleID & IAWVehicleNo Fields but when the details are entered through the forms the IAWVehicleNo which is the manually entered number only updates to the tblIAWVehiclesDetails but the other IAWVehicleNo fields in the other tables remain blank. Any advice or help would be greatly appreciated.
I have a table that is going to track people. First Name, Last Name, and Date of Birth in three separate fields.I dont want to be able to add the same person in the table. How do i do this when the data is in separate fields.
I have a member table and donation table. For a donation, I'd like to lookup the last and first names from the member table. I set the lookup for the last name and can see bothe first and last names in the drop down list. When I pick, I get the last name in the DonationT but how to I pick up the first name? That is, how do I fill DonorFirstName in DonationT from the FirstName field in the MemberT?
What I have is a table ('tblJobLog') with our main listing in it where we add customer job information as it comes in. The 'Customer Name' is a dropdown list that links to our table 'tblCustomers' so that we can ONLY select a customer name that we deal with. The alternative is to add a customer or list them as COD.
Now this is where I would like to target, the COD's. We still like to keep a record of who the COD is and we have some common customers that are listed as COD only.... So when we go to enter the customer name we have to put COD and in the description we are suppose to input the customer name (We will says RandyShop <--- me, for now).
What I would like to have done, if possible, is to see when we select 'RandysShop' from the dropdown, it checks to see if the COD Status (in the 'tblCustomers' table) is set to YES (its a check box, or YES/NO field). If it is set to YES (or selected) then the change the name shown in my main 'tblJobLog' to look something like COD: RandysShop.
The reason I would like, if possible, to do it this way, is because some customer go on and off COD regularly as the mess around with payment. So instead of creating a new customer that is listed as COD: RandysShop to have access do the leg work based on a simple YES/NO field (COD Status).
Hi All, Being a newb, have a hopefully straightforward question. I'm writing a vehicle management database which covers eleven seperate areas. The data is currently contained in a spreadsheet with eleven seperate data sheets, one for each area. My thinking is I use linked tables as the spreadsheet needs to be occasionally updated. My difficulty... If I want to cycle through all records, I assumed I could query against all tables but don't seem to be able to. The tables are not currently linked in any way and contain fields such as registration, emissions, list price, make and model and so on. Any suggestions would be greatly appreciated.
Below is a Tab Delimited section of text to represent a SQL Table.
This is the result set of a select * from table where RNmbr = 0508.
I added the Top Row of A TAB B…TAB H
Row 1 references Column names of SQL Table.
And the First Column (under A) as Excel references.
A B C D E F G
1 RNmbr MesPt R1 R2 R3 R4
2 0508-1 1 28.0 48.0 48.0 74.0
3 0508-1 2 77.0 78.0 75.0 48.0
4 0508-1 3 81.0 59.0 65.0 56.0
5 0508-1 4 54.0 46.0 24.0 25.0
6 0508-1 5 21.0 2.0 15.0 74.0
7 0508-1 6 4.0 88.0 68.0 14.0
8 0508-1 7 8.0 94.0 87.0 96.0
9 0508-1 8 9.0 76.0 66.0 58.0
10 0508-1 9 48.0 48.0 35.0 74.0
11 0508-1 10 36.0 59.0 26.0 888.0
The challenge:
We can easily create STDEV(R1), STDEV(R2), STDEV(R3), STDEV(R4) from the above info.
However, we need to do a STDEV of all these points combined.
Within Excel, this is easily accomplished with the formula
= STDEV(D2:D11,E2:E11,F2:F11,G2:G11)
However, we are tasked of getting away from an Excel spreadsheet and putting this data into a database (imagine the above several hundred thousand strong).
We are using Access as a front end to SQL 2000. We need to have this conglomerate STDEV to be within Access (should the resulting STDEV fall out of spec) it will trigger an alarm for the production operator.
We are not having much success using T-SQL or Access in getting something which seems so simple in Excel. An Access query would suit our needs better.
Now, once this hurdle is overcome; there is a second phase. This table represents one of three, and, you guessed it, there is a need for a Conglomerate STDEV of ALL these points. Any help would be greatly appreciated.
SELECT Assets.* FROM Assets WHERE (((EXISTS (SELECT * FROM LCAMdump WHERE Assets.BarcodeNumber = LCAMdump.T_TAG )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((SELECT BuildingName FROM Building_Names WHERE ASSETS.BuildingNameID = Building_Names.BuildingNameID)=LCAMdump.BUILDING) )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((Assets.FLOOR)=[LCAMdump]![FLOOR]) )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((Assets.DeskLocation)=[LCAMdump]![LOCATION_SEGMENT2]) )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((Assets.BuildingLocation)=[LCAMdump]![LOCATION_SEGMENT1]) )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((SELECT FirstName FROM Employees WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_FIRST) )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((SELECT LastName FROM Employees WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LAST) )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((SELECT SSO FROM Employees WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.LOGIN_SSO) )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((SELECT UserID FROM Employees WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LOGIN) )) =False));
It works great returns the correct results. But I don't need everything out of Assets. I just need a few things from there and a few things from 2 other tables.
I tried this but it now gives back over 220 repeating results.
SELECT Assets.BarcodeNumber , Employees.UserID , Building_names.BuildingName, Assets.Floor , Assets.BuildingLocation , Assets.DeskLocation , Employees.FirstName , Employees.LastName , Employees.SSO FROM Assets , Employees, Building_Names WHERE (((EXISTS (SELECT * FROM LCAMdump WHERE Assets.BarcodeNumber = LCAMdump.T_TAG )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((SELECT BuildingName FROM Building_Names WHERE ASSETS.BuildingNameID = Building_Names.BuildingNameID)=LCAMdump.BUILDING) )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((Assets.FLOOR)=[LCAMdump]![FLOOR]) )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((Assets.DeskLocation)=[LCAMdump]![LOCATION_SEGMENT2]) )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((Assets.BuildingLocation)=[LCAMdump]![LOCATION_SEGMENT1]) )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((SELECT FirstName FROM Employees WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_FIRST) )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((SELECT LastName FROM Employees WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LAST) )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((SELECT SSO FROM Employees WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.LOGIN_SSO) )) =False)) OR (((EXISTS (SELECT * FROM LCAMdump WHERE ((SELECT UserID FROM Employees WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LOGIN) )) =False));
I am sure it something simple but I am a novice at this so please help me. :D
Please be kind, i have little VB Knowledge, and wish to expand my learning on this topic.
I have a form that updates information on one table, and has a subform displaying info from another table.
the subform is filtered, and only shows data from what is specified from the filter of the main form.
If I update information on the main form for instance, field 1, (the data on the subform has the same data so there is the relation), how do i update the subform by only updating the mainform? can this be done through some VB or something?
if you folks out there can give a Smidget of info on which VB codes i can use i can pretty much figure it out.
is there any way to put into the calculated field (in expression builder) conditions? What I need is something like
Sum If (Table1.Field1="Y" And CurrentTable.Field2=Table1.Field3)
I means sum how many times there is "S" value in the field1 Table1, but only for records where the field3 in Table1 is equal to the value in the actual table in Field2 (in the actual row).
I am great with Excel but not soo much with Access 2010. I Excel, what I needed to do was very simple but duplicating what I did in Access is not soo easy.I am trying to calculate billings for FSA & HRA. I have set up one table with all the data. I want to add columns to the table to calculate:
1. Is there an account balance-excel formula: 2. If the account is still active 3. If the plan year run out is "active runout" or "runout over" 4. calculate each account type with a rate *count of FSA accounts = total to bill
Here are my excel formulas that for the life of me I can't get to work.
I am creating an access database for my employer which handles blood donating at different venues. I have come unstuck with a particular request.
They would like to create 3 autofill fields for "dates", which are dependent on the previous field "Venues"
There are different venues, which are visited 3/4 times per year. This data is stored in a separate "Venues" table.
On the main user form, they would like to see the dates available to donate, when the Venue field is selected (this is an autofill box, from the Venue table). So if the London venue is visited on 1/1/13, 2/2/13 and 4/4/13. When the user types London into the "Venue" field then the next 3 cells auto fill with 1/1/13, 2/2/13 and 4/4/13.
I've been playing around with a new database design and ran into a possible 'error' that I would like to avoid.
It's going to be a payroll database to store time codes for hours spent working on specific projects. I have been struggling on how to put this together to fit with what we've been doing for years and I think I hit a few breakthroughs this morning.
However I want to avoid this error of possible duplication of entry.
Simple table set up - primary key is just a running integer; Employee ID; and Week Ending Date.
I can have multiple week ending dates for a specific employee; but I want to avoid having the same employee with the same week ending date. I cannot set up either field as being unique.
I have a database with two tables, one for the amount that was estimated in each cost section, and one for the actual amount billed for each cost section. The tables have the same number of fields, all with the same names. They can be linked together with event ID. Each table has over 100 fields and I would like to find the difference between what was estimated and what the actual was for each event. I would also like to see which cost section has the most and least variance. I am trying to do this without going through each cost and putting [tEst].[CostName]-[tActual].[CostName].
I wrote a database several years ago and recently pulled it out to give to a friend. The problem is, back then (not knowing better) I set the Employee table up with as a single field "NAME". Now, in order to make it effective, I need the Employee's name in four (4) parts (First, Middle, Last, Suffix).
I have several queries based on the "NAME" field and and would like to avoid changing all of them. I have a simple form "frmUpdateEmployees" that populates the Employee table and Name field. I was hoping to change the form and/or add a query that would be easier and more simple.
Table: Employee Field: Name Form: frmUpdateEmployees Queries: 16 that depend on the table and field above.
I am creating a table in access 2010 for my consumable and bench stock report. I made a 12 fields which I name it the month of the year and another 1 field to add the total disburse materials in one whole year. I did this formula to add the 12 fields
But the problem is its just adding the complete consecutive months that I disburse and the row with blank section the total disburse doesn't show on the total disburse for the whole year. I try to use the code =Nz([Total Disburse],0) but it shows on the screen i cannot be used in calculated column.
I get tasked to use access very infrequently but now I have been asked to create a database. I am struggling with combining 2 tables. I have different data on each table however I do have a unique Identifier. So on table 1 I have Bud, his height, weight, etc. On table 2 I have Bud his home address, phone #, etc. I am using name "Bud" in this case as my unique identifier. I want to create a query that gives me Bud, his height, phone # etc. I want my query to pull in all records. Bud may only have info on table 1 and Budette could be on table 2 but not on table 1. I would like my query to include all the unique identifiers and as much info as I have in the tables.
Here is my current table structure (I have omitted some fields from this example and have given some sample data in italics to make the table structure more clear.
tblEmployees
ID (autonumber) 3 EmployeeName John EmployeePhone 555999555 EmployeeLocation New York
tblClients
ClientID (autonumber) 1 , 2 , 3 ClientName ABC Company , XYZ Company, PQR Company fkeyLocationID
tblLocations LocationID 1 , 2 Location New York , Chicago
The junction table tblEmployeeClients only stores ID of the Employee and in the second column (which is a multi-valued field), the ID of each of the clients the employee Supports.
I am trying to generate a report that lists say, EmployeeName alongside the clients supported by the Employee (listing the client location is not required, however, it would be good to know how to do that as well).
The report (for the example above), should look like this:
Name Clients Supported John ABC Company, PQR Company
Currently, I am able to get :
John 1, 2 i.e the client ID for the clients that the employee supports instead of the corresponding company names.
I have written a query to calculate how many hours we have worked for our clients. This has a number of sub-queries which sum to make different columns in the main query ie: Hours invoiced so far, Hours to be invoiced, Hours remaining from their allocation, etc. I am refreshing the queries due to a design change and now have a question..
Which is the better design:
To have those sub-queries with selection criteria from a join to the same table in each (to filter out records at a lower level) OR To have that same selection criteria and join just once in the main query? (which of course means its processing many more records but the join to the selection criteria is specified once)
Exit_cmdSearch_Click: Exit Sub Option Compare Database Option Explicit
Private Sub List2_DblClick(Cancel As Integer) Dim rs As Object
DoCmd.OpenForm "frmquery"
Set rs = Forms!frmquery.Recordset.Clone rs.FindFirst "[RETURN NUMBER] = " & Str(Nz(Me![List2], 0)) If Not rs.EOF Then Forms!frmquery.Bookmark = rs.Bookmark
