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'm creating a database for a travel agency. There are 2 tables, one for customers and one for their bookings. I have established a relationship between the tables so the AutoNumber for customer ID links to a field on the add booking table.
In practice though, it would be easier to enter the customer NAME into the booking table and have it retrieve their ID that way to link them. This seems like I am missing something simple but can't fathom a way to do it.
The other factor is obviously more than one customer will have the same name so I may need to link first name as well to differentiate?
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.
Hello everybody! Im new in Access as well as in this Forum! Glad to be here :) Im currently developping a small application in Access 2003 and here is my problem: I am trying to autofill the value of the field OrderID in this form : "FRM/SupplierID/CurrentYear/OrderNumber" Where the SupplierID is charged once the SupplierName is selected from a combobox and the OrderNumber entered from another textbox. Note: SupplierName values are from other table: Suppliers
Need some help with a table, or possible more to do with a form design..
I'm looking to autofill in a field depending on data that's in the previous field.
E.G. I have too fields.. One for employee and one for department.. I want the department field to automatically select the department whenever the employee is selected from the drop down list.
It's probably a simple query or something I need to do..
I made a form to search some values in a table and print them in a report. The form looks like this: http://www.minezone.nl/jamie/search.bmp
When clicking the "show report" button a macro is executed. In this macro I have the following code:
OpenReport --> Report name: main View: print preview Where condition: [name]=[Forms]![searchfeedback]![namebox] And [week]=[Forms]![searchfeedback]![week]
Now what I want is a bit difficult. I want to give users the ability to search by each option and by using multiple options. i.e. - When filling in the name and click the show button it gives all reports for that person. - When filling in the name and selecting the weeknumber it gives all reports for that person in a particular week.
and so on with the id field, the date field and the supervisor field.
I have a form that has a field called scope of work which is a combo box that you have 3 options to select from.
I have set up a table called scope of work which has other fields ie: Scope of work, Est Man Hours, Normal Rate, Overtime Rate and Est Due Date.
The combo box works fine selecting the scope of work but I want the fields on the form to automatically fill in the other information. Say they select option 1 in the scope of work based on this option it will fill in 40hrs at $150 per hour normal rate and $180 per hour overtime rate and this will take 4 weeks to complete. Based on the start date field it will also calculate 4 weeks from now for the completion date.
I have tried everything from queries using SQL statements, joining tables one to many etc.. and for some reason I cannot seem to get it to work for me and I've spent hours searching around for a solution..
If anyone has any ideas it would be greatly greatly greatly appreciated!!
I'm building a database with supplier information. Some users prefer to use the supplier names, but it is sometimes hard know how foreign suppliers are spelled, therefore some users prefer to use supplier numbers.
I want my form to show all supplier data based on either the supplier number or the supplier name, which can both be chosen from 2 comboboxes.
In addition, and here's the catch, I would also like the two comboboxes to update eachother. So if I choose supplier 2 in the first box, the other box should automatically show me the name of vendor 2 and vice versa.
I've been using this forum for a couple of weeks but this is my first post, so apologies if I've put it in the wrong place!! I have a table with some drop-down lists to fill certain fields, and I would the available list options to change based on information in other fields. To provide an example:
Field One: The drop down list lets the user choose "Alphabet" or "Number"
Field Two: The drop down list lets the user choose A-Z if "Alphabet" is selected in Field One or 1-100 if "Number" is selected in Field One
Hope I've been clear enough. Any help is greatly appreciated!!
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.
So I have a master form, with multiple subforms on different tabs within the master form. This form is not used to look up data, just to only enter data in. In one of the tabs, I have a subform in the form of a data table where the person filling it in can put multiple locations of one dealership. In the next tab, I want these loctions to autofill into the next subform on the second tab in the data sheet. If I were to go with the solution of using an unbound textbox on the parent/master form to refernece the first subform and then have the second subform reference the textbox, how would I go about doing this? Just with the expression builder and conrol source? Am I able to autofill multiple rows of data from one subform to the other for one recond?
I currently write (very long) reports for work, which have a lot of standard text in them - but the standard text varies according to the client's history.
I want to create a report-writing application for myself, composed of series of yes/no/maybe buttons and depending on whether I click yes/no/maybe in each section, a different segment of text is inserted into the report.
Ideally the application would be able to insert, or create, formatted text - such as headings, bullet point lists etc - I would need to be able to export this report into Word
I started out by making a table, with each section being a different field, and the various paragraph options as different cells in the field, but I am stuck at present on the basic functionality of "click button , insert text"...
I have an Access database with several tables and a multitude of subforms which are displayed on a single master form. The subforms are used to facilitate data entry. In several of the tables there are fields which are related and I would like to have some of these fields updated based on the results entered in the related field (i.e. the answer for one field depends on the other). Field 1Field 2 [facing][DISC_CODE] un3 up1 dn2
Field 1 is a simply a description of the basic dataset. This field is already set-up on form as a combo box that allows the user to choose one of three options. Field 2 is a code number used by another piece of software to identify a particular symbol. It is a new field being added to the database. There are 200 codes that identify a wide range of symbols for different types of data and I don’t want to have to look them up when I, or my assistants, are doing data entry.
My question is this; is there anyway to have the DISC_CODE value, Field 2, automatically entered in the table when the value for Field 1 is selected in the combo box on the form?
