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.
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 have two tables, "Summary" and 'POC Information". In the "POC Information Table I have all my Contacts Information (Name, Title, Phone, Email, etc...) and I am trying to assign 2 POCs to each of my multiple projects located in the "Summary" Table. I am using a Form called "JCIDS Tracker Input Form" as the link. So far I am able to assign one POC by a combo box that lists "Full Name", then it autofills the other information...Phone Number, Email, etc... The problem come into being when I want to assign a second POC to the same Project...I can assign a name, but it won't correctly autofill the rest of the information...it just autofills in the information from the first POC that was selected.
I had an existing database with 2329 records entered into it. All of the fields (220ish) were all in one table. Myself and my co-workers wanted to rebuild the database without losing the data. We wrote queries to transfer the data from the original database to the new database and split the data from the original 1 giant table to 9 smaller tables.
The transfer of data worked so I went to start making forms. When I went to add fields from different tables I had to built a relationship, which I did. All of the data that transferred over from the new database is in the form (now multiple forms linked by button) but I can't add new information. I get an error stating, "You cannot add or change a record because a related record is required in table ..." and the table referenced keeps changing.
Stuck on a problem and was wondering if anybody has a possible solution. I currently have the beginnings of a database setup that uses a search field on a form to find a reference listing using a part number input by the user. It finds the part number, and then displays all the information about it on another part of the form. My question/problem is, I now want to expand this database to multiple specialties, all with different parts in their respective listings, so they would have different tables. The tables would be the same format, just different information. No one table would ever contain the same info.
Q: How would I set up a form to search for a unique part number over multiple tables, and return that parts info?
It works great for one table, but I am stuck on the multiple tables.
It's been a while since I've asked a question here, but I can't seem to figure this problem out.
I am setting up a form using data from 4 different tables, all related, and I can't figure out how to add fields that I didn't initially add during the wizard set-up. I used the wizard because it's easier for me to do that and then go in and make the changes I want to make.
When I get data from just one table, all of the available fields from that table are in the "Field List" no matter what fields I chose to include in the wizard process. That's good. But when I get data from more than one table, only the fields I initially chose in the Wizard process are showing up in the field list. I can't figure out how to add a new and different field from one of those tables.
The way I "solved" this problem the first time was to start over, creating my form from scratch--but now that I've done a lot of work, it just occurred to me that I will need to add 2 more fields. (I actually don't even have those fields in a table yet, but I will add them later.) So, this will be an ongoing problem. Since this form will get much use, by several people, I don't want to have to re-create it every time I want to change something.
So, is this possible? How can I add another field?
Also, while I am at it... A combo box was working beautifully to look up specific records when I was gathering information from only one table. It isn't working anymore.
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 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 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 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
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 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 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).
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.
