Hi all,
Please bear with me if I'm asking a dumb question. I've been tasked to create a database from scratch and I've not had much experience working with Access.
**My Question**
Using a form is it possible to have the form automatically enter a "value" in one field based on the information inputed by someone in a different field.
ie. When some one puts in a value between 0 and 20 in one field it automatically enters a 1 in the other, and when some one puts in a value between 21 and 30 you get a 2 and so on down the line.
I'm attempting to build a simple database for a martial arts tournament registration. The weight division (Fin, Fly, Heavy, etc.) depends on the age, the sex and the weight. I've tried this several different ways, most recently by using one table called "competitors" with all the entrant's information, and several other tables based on the age class/sex (i.e. I have a table called Age 8-9 Male with the weights and divisions in it, and other tables for the other ages/sex). So, basically, what I'd like to have happen is a report that will lookup in the correct table the division, based on the weight entered. In other words, for a record where the age is 8-9 and the sex is Male, the report compares the weight to the values in the 8-9 Male table, or if the record is a 12-13 Female, it looks in the 12-13 Female table for the weight, and places the correct division in the Division field of the report. Probably going about this wrong.....but any help would be appreciated.
I've put together a database for my company to track our archived files. When the files are archived, there are three important values pertaining to their location: a list number, a box number, and a barcode number. Each box has a barcode on it, so what I'd like to do is make it so that when any record has both a list and box number entered (since it is dependent on the list number to know which, for example, "Box 1" I'm entering information for), it would automatically insert the appropriate barcode number in the field. It would obviously need a seed for each barcode number to be entered before it would know which one to use, but that could either be based on the first unique entry, or entered another way. If anyone can help me do this, I'd really appreciate it. Also, if my description was insufficient or confusing, just let me know and I'll try to clear it up. Thanks!
Example: If I entered: List # Box # Barcode # 77 4 It might not know what the barcode is the first time. But once I had entered List # Box # Barcode # 77 4 366582 It would know what I was entering. Keeping in mind that I'm not showing the rest of the record, but just this end part, my next entry would look like: List # Box # Barcode # 77 4 366582 77 4 366582 ...the red representing that it would automatically fill that in, because it knows that if I'm talking about Box 4 in List 77, that must be the barcode.
I am trying to color code text boxes based on the data entered in. The column is "PHASE" and the items entered in are either "1" "2" or "3". 1 is supposed to be Red, 2 Yellow, 3 Green.
I have Microsoft Access XP (I'm sure). I've tried to do it on VB and had no luck. I couldn't figure out Macro and I don't have On Click or Code Builder.
I have tried a couple of different expressions and no luck. I'm definitely an amateur to Access. I'm only using it cause it's what we have in the office.
I have two text boxes for which I am trying have show and error message when the proper data is not entered. One text box is for PLANID and the other is for Date. I would like the PLANID to be checked 1st. The PLANID needs to be either 7 digits or 10. If it is not, the message would be "Please enter a valid PLANID". If it is 7 or 10 digits, then it should check the Date text box.
If it is blank, the message would be "Please enter a valid Date". If they are both blank, the PLANID message should appear 1st. Below is what I have written. The problem that I am having is that when the PLANID is 10 digits and the date is blank, the "Please enter a valid PLANID" message is showing rather than the "Please enter a valid Date" message.
Private Sub Btn_Refresh_Data_for_One_Plan_Click() Me.txtboxPLANID.Value = UCase(Me.txtboxPLANID.Value) If ((Len(Me.txtboxPLANID.Value) = 7 And Len(Me.txtboxPLANID.Value) = 10) And (Not IsNull(Me.txtboxPLANID.Value)) And (Not IsNull(Me.TxtboxDate.Value))) Then Dim stDocName As String stDocName = "Mcr_RUN_MATCH_DIFFERENCES"
How to disable fields in Access based on data entered in a previous field? For example if "yes" is chosen from a drop down show "Date field" if "no" is chose hide "Date Field".
I have a form that request the User to enter a Parcel ID number (99-9a-99-99aaa-aaa-aaaa). The sub form asks for the subdivision number, block number, and lot number. The subdivision number, block number and lot number are normally part of the Parcel ID number but sometimes they can be different. I want the sub form to fill in the Subdivision number, block number, and lot number as a default but allow the user to change the value if needed. currently the sub form will only show what is entered into the table itself if a change is made on the main form the sub form does not reflect the change.
I am fairly new to access but so far I have been able to get what i need from it, until now. I am trying to find a way of comparing two sets of data to find out an employee's average productivty.
Table 1 -Hours Worked (by day) - contains 'name' 'date' and '# of hours' worked - an employee would enter the hours here on a daily basis
Table 2 - Contracts Keyed (by month) - contains the number of contracts worked that is derived from seperate system - this is entered on a monthly basis (so for example: John keyed 30 contracts for the month of January) - the system i am pulling this info from does not have the ability to pull a daily count of contract per employee, only a range of dates and it then provides the sum for that range (unless I ran a query for each day, for each employee which would take me hours) - employees dont have access to this system to enter their own # of contracts keyed on a daily basis. - for entry, so far i have just been putting the first of the month and then the # of contracts.
In a nutshell, this is the calculation I am trying to create:
(Sum of "# of hours" for the month) / (total "# of contracts keyed" for the month) = employees average hourly productivity.
I have tried to do this with various types of queries and reports but with no luck, I get a prompt saying that access can't compare the 2 fields.
Is there a way to compare the data that is entered daily with the data i would enter monthly?
I am wanting to update data in one field which is being pulled in from another table based on an entry in another field in a form
Attached is the database. In the sales form I want to enter a customer ID which will then pull in the customer name from the customer table and put it in the Customer Field in the sales table.
I know I am duplicating the data by having customer name in both tables which is bad database design!
I have a form that collects a survey where the user selects between 1 to 9. I was wondering if the user selects a certain range, for example between 1 to 4 a yes/no dialog appears. If the user selects a range from 5 to 9 , they get no dialog box.
I have two Tables, Table A and Table B...Table A and B have a one to many relationship with A (one) and B (many).I have a Master form that displays information for Table A. Also, I have a subform within this Master that displays information for Table B.
As I scroll through the records of A, you can see the 1-many relationship elucidated in the subform with many being displayed for Table B.I run into a dilemma, though, when I try to ADD a new record to Table A (and in turn Table B).When I add a new record, there is no Primary Key left to be displayed since this primary key is generated from a query.Hence, when I move to a new record, I can not save the record because there is no primary key. Since there is no primary key, there is nothing that i can input into the subform either. I want to create the primary key from values entered in the field. However, i CAN NOT create a relationship with an expression for field values.
My database tracks individuals qualifications. These qualifying scores relate to Unqualified, Expert, Advanced etc. What I want to do is add a entry into my roster table which will have the qualification score. I then want to have the score and actual qualification (Advanced, Expert, etc) in a Report. The qualification type is a range (less then 30, 31-40, 41-50), and I think that I will need a BETWEEN statement. What I am not sure of is how to put this together to make it equate the number with the equivalent word in a report.
I am an amature with databases and a bit rusty too. Can anyone help me with this problem?
basically I want to automatically enter a unique asset code for one table that is based on product and manuafacturer's codes from other tables
e.g. for audio visual eqipment
The first table would be a manufacturers table with the manufacturers code as the primary key e.g. JVC, HTC (hitachi), SHP (sharp) etc
The second table would be a product table with the product code as the primary key e.g. AMP, DVD, LCD (lcd tv) etc.
( i would also have a customer table and an asset repair table)
In my asset table I would like to enter an asset code as the primary key of the type JVC-DVD-001, JVC-DVD-002, HTC-LCD-001 etc by looking up codes from the other tables and then adding the last number. (Ideally once the manufacturer's code has been selected only the product codes for products made by that manufacturer would be displayed). (Also it would be ideal if referential integrity could be set up so that the asset code components can only contain valid manufacturer and product keys.)
The asset code cannot be a compound key as I want to have a repair records table in which the asset code is entered as a single field with referential integrity to the asset field in the asset table.
When entering repairs I would like the lookup field to display only the assets belonging to a particular customer.
I have a textbox & button that when entered & pushed - run a query for the top values randomly depending on the value entered into the textbox.
My code is...
strSQL = "SELECT TOP " & Me.Text140.Value & " [CAN - NAME].Name, [CAN - CPT/VOUCHER].Voucher_Number, " & _ "[CAN - CPT/VOUCHER].Procedure_Code, [CAN - CPT/VOUCHER].Service_Date_From, [CAN - CPT/VOUCHER].Patient_ID, [CAN - CPT/VOUCHER].service_id, Rnd([service_id]) AS RandomNum " & _ "FROM [CAN - CPT/VOUCHER], [CAN - NAME] WHERE [CAN - CPT/VOUCHER].Service_Date_From Between " & Me.StartDate.Value & " And " & Me.EndDate.Value & " ORDER BY Rnd([service_id]) DESC "
It runs like it should but doesn't populate any records...that is until I go into the design view, deselect one of the fields and then reselect the same one - only then does it populate.
Do I need to open the query first and then update/append instead?
I have an append query that currently looks like this URL...This query automatically adds the machine parameters for a product code and lot number into the running condition log. This is so the user does not have to manually go in and tediously select each machine parameter.
The running condition log also has a date field to specify what day and record number the machine parameter's value was recorded on. When I run the query the appended rows look like this. The product and lot are defined by user parameter and there are actually about 36 machine parameters
Code: Productstockcode LotNo Day Record Parameter ActualCond PE-500 130816m71 StockTemp PE-500 130816m71 Zone 1 PE-500 130816m71 Zone 2
My question is: how do I modify the query to automatically add the date and record number in one shot? It needs to be user defined at the time of the query because this data is not stored anywhere else in the database. For each 36 machine parameters the day and record would be the same.
I have a form where I am capturing StartTime and EndTime of certain events. I have set the input mask as 99:00 >LL;0;_
My users think it is too much to enter am or pm. What they want is the system to calculate the am pm based on the time entered.
If the time entered is between 8:00 to 11:59, the system should save it as 11:59 am and if the time entered is between 12:00 to 7.00, the system should save it as 7.00 pm.
I am looking to (on the mainform) have the user enter into a text box whatever date they would like to enter and have that value become the current date of the system. So that all operations are based off of this newly entered date.
I need to assign those appraisers to an order based on what county they cover. My main table (orders) is simple. Just an order number, order date, county and appraiser.I have a form with entry fields for all these except appraiser. I envision a "assign" button that will then pick the next appraiser in the county that was chosen. It's just a "round robin" type of thing, so the first order placed for Monroe would be assigned to ABC. The next order would be for Cecil and would assign 123. Next order for Monroe would assign DEF. I know there are many way this can be done but I've been looking at this for hours and I'm drawing a blank on the easiest way to do it.
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
Is it possible to run a basic select query to pull records based on multiple strings entered by a user?
I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'
This allows the user to input one MMDBID and the records are retrieved from the db.
I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.
But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above
SQL statement (Like) is below :
SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline] FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID) WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));
I'd like to create a query which will consist of simple SELECT statements as follows:
SELECT [table1].[field1], [table2].[field1], [table2].[field2] FROM table1 INNER JOIN table2 ON ([table1].[fieldX] = [table2].[fieldX]);
The challenge arises b/c instead of joining on equal values, such as the following: [table1] INNER JOIN [table2] ON [table1].[field1] = [table2].[field1]
I would like to join based on equivalencies, such as: [table1] INNER JOIN [table2] ON [table1].[field1] = 34 is equivalent to [table2].[field1] = 2;
I do not know the proper syntax, so this is where I need help. I tried to search online without any success.
