Pulling Data From Other Tables Based On Values Entered

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.

View Replies


Automatic Values Based On Entered Data

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.

Thanks for the help.

View Replies View Related

Combo Box - Populate List Options Based On Data Already Entered

Microsoft Access 2003

Novice and working on a project with deadline... please help.

I have a table w a field that I want to convert into a combo bo and use data in the field as the options:

FIELD: Student Status


How do I get the combo box to automatically pull the values from that field into the options in the field when u pull down the drop down box?

Am I being clear?

View Replies View Related

Pulling Data From Two Tables - Version: 2003 (11.0)

Pretty basic question, but still having troubles:

Let's say I have two tables. One table (Table A) has the following fields: Name, Phone, Address, Country. The other table (Table B) has the following fields: Company Name, Company Address, Country.

I would like to create a form that will automatically have all the information listed in Table A, but if I check a box on the form, then the data in the form's "Address" field will be replaced by Table B's "Company Address" for the corresponding country (the two tables are related through the country field). Does this make sense?

(checkbox unchecked)

("Use company address" checkbox checked)
Company Address

I have tried to design a query using the "IIf" function but I must be doing something wrong. Is there a better way to do this?


View Replies View Related

Creating Reports Based On User-entered Data - Version: 2000 (9.0)

Hi there,

I'm very new to Access so please excuse me if this question is really basic.

I have a table of questions and answers for conducting customer interviews. Each record also has a Customers field and a Products field. I want the Interviewer to open a report and be asked to select one customer from a displayed list of customers, and one or more products from a displayed list of all products. Then the report will open, showing only the questions and answers relevant to that customer.

I hope this explanation makes sense. Can anyone give me some clues? I have been trying to work out how to do this for a while and I can't find anything in the help files.

Thanks in advance,

View Replies View Related

How To Automatically Fill Fields In A Form Based On Data Entered - Version: 2003 (11.0)

I am trying to automatically fill a field in a form based on the year of the date entered in MM/DD/YYYY format. The year entered is used to access a row in different table than the form's RecordSource whose key field is intControlYear. Once the row is accessed I want to return the value of the intControlNumber field. I am trying to use the following code in an On Exit event of the Date Entered control. When it executes I get a run time error 2001 saying that I have cancelled the previous operation at the time it tries to execute the DLookup statement. What am I doing wrong;

Dim intYear As Integer
Dim varNumber as Variant

intYear =Year(datDateEntered)
varNumber = DLookup("intControlNumber", "tblYearControl", "intControlYear=[intYear]")
Me!intYearSeen.Value = varNumber

Thanks in advance for your help

View Replies View Related

Inputting Data Once And Having It Entered On Mult. Tables - Version: 2000 (9.0)

I would like to input new data in one table and have it fill in the same data in the same field on other tables. (e.g. I have the same three fields on 6 different tables. I would like to not have to input new values in every table.) Can anyone help me?

Thank you in advance,

View Replies View Related

Excel Values Entered Then Values Populate In Access Table - Version: 2007 (12.0)

I currently have a spreadsheet that when I enter data in Excel, am I able to create a Table where the info in Excel will populate in the Access Table? If so, how do I go about doing that. I can do it in Excel, but I am trying to avoid adding all these formulas and locking the cells.



View Replies View Related

Update Query Based On Lookup Tables Values

Hi, not sure if this is possible or the most economical approach but here goes:

I have a lookup table (tblHolidays) with a list of holiday dates (fieldname "HolidayDate") in it.

I need an update query that can check all "StartDate" values on a table called "tblMasterLog" and where this date matches the ones on "tblHolidays", will add 1 day to the "StartDate" and then keep repeating until there are no more dates to change.

Can this be done?!

Thanks in advance

View Replies View Related

Pulling Values From A Table Onto The Net?

Hello all,

I would consider myself a novice to almost intermediate with access.

Anyways what I need to do seems pretty simple. I just need to make a web page that pulls values from a table we have stored on our server. The users WILL NOT need to modify the info, they simple need to view it.

I want it set up so that when users modify the table locally here at our company, people from another site can view the changes on the internet...

Thanks for your time!

View Replies View Related

Pulling TOP Values From One Table To Another - Version: 2000 (9.0)

This is the SQL for the query that is used now to pull the TOP Values from 1 table and append to another table.
The problem with this is-it requires a separate query for every client because their TOP values are different. How can I do this and not have a separate query for each client. There is a field in the Client Table for the percent or number of records to be pulled (clDrugSampling and clBatSampling). Some clients have 2 queries if they pull for both Drug and Alcohol because the number to be pulled is different for both.

INSERT INTO Results ( reRandomizationDate, reEmployeeID, reEmployeeLastName, reEmployeeFirstName, relCLientID, reAthleticPoolID, reDrugRandomNumber, reTestTypeID )
SELECT TOP 5 PERCENT Random_Selections.rsRandomizationDate, Random_Selections.rsEmployeeID, Random_Selections.rsLastName, Random_Selections.rsFirstName, Random_Selections.rsClientID, Random_Selections.rsAthleticPoolID, Random_Selections.rsDrugRandumNumber, "R" AS TestType
FROM Random_Selections
WHERE (((Random_Selections.rsRandomizationDate)=Date()) AND ((Random_Selections.rsClientID)=[Enter Client ID]))
ORDER BY Random_Selections.rsClientID, Random_Selections.rsDrugRandumNumber, Random_Selections.rs2ndRandomNumber DESC;


View Replies View Related

Pulling Values From A Table Onto The Web? - Version: 2003 (11.0)

Hello all,

I apologize if this is in the wrong section, I was not quite sure where to put it.

I would consider myself a novice to almost intermediate with access.

Anyways what I need to do seems pretty simple. I just need to make a web page that pulls values from a table we have stored on our server. The users WILL NOT need to modify the info, they simple need to view it.

I want it set up so that when users modify the table locally here at our company, people from another site can view the changes on the internet...

Thanks for your time!

View Replies View Related

Pulling Latest Record Based On Date And Time?

Hello All...
I'm very new to Access.. so please forgive my ignorance..

Here is the scenario..

I have a table with two fields named "item" & "description" that contains 10 records total.

I have another table with 100 records with the fields "invoice #","item", "price paid","date paid","time paid".
The item fields are linked between the two, and the 100 records contain different invoices for these ten items.

I want to pull the price paid off of the latest invoice, based on the time and date... What is the easiest way to do this?


View Replies View Related

Pulling Information From One Table/ Sheet Based On Some Conditio - Version: 2003 (11.0)

Hi All,

I need to pull info from one sheet to another but need help in getting this done in excel / access.

Here is what i am trying to accomplish. i have to come up with a staffing plan for a transaction process and need help in formatting the data ( 6 months ) so that i can start analysing it.

I have 2 sheets in my excel workbook.
A) Mainsheet - Contains information about Mail in Time for different requests. Using this, i have been to arrive at "Day of week" . I now need to slot the times i have into different buckets.

B) Grid: Contains a 1/2 hour breakup of the times in a day.

I need to be able to slot the "Mail in Time" for each request in the Mainsheet after looking up the different time slots in the "Grid " sheet.

This will help me understand how many tickets come at each 1/2 hour interval and staff accordingly.

Attached is an abridged version of the sheet.


Am uploading an abridged version

View Replies View Related

Field Updates Based On Month Entered

I have a report were I want to enter a month say "November" and I have 31 independent fields that reflect each day of that precise month. I also need the the last day of the month to only show the actual end date. 28, 29, 30, or 31 respectivly.

View Replies View Related

Updating Data On 2 Tables Based On Criteria

Ok. Here's the situation. I have query setup. This query has 2 columns:
1. Agent Name
2. Sale Value

I have 2 tables.
Table1: Includes Sales for this pay period only
Table2: Includes all Sales From past to present.

We are setting up our system on Tiers and Each sale value is going to change. So, Here's what I need:
I need to run an update query that first Looks up all the records in Table1 based on Agent Name. The field that needs to be updated is called SaleValue. It needs to be updated with what's in my Query mentioned above.
Then, all the values in Table1, need to be copied (or updated) into Table2. This can be done based on the Primary Key (SaleID), which exists in both tables. So I guess we will need to run 2 separate update queries. Any ideas on how this can be done?

View Replies View Related

Validating Values Entered In A Textbox - Version: 2007 (12.0)

I have form where the user must enter a month. After entering the month the user must click on a search button. I would like to check to see if the text they have entered is a value month.
I am not sure how this should be coded.

I have code in the beginning that will check for an empty value. I am prettty new to this.

help please

View Replies View Related

Compare Data In Different Tables And List Non Matching Values


I am trying to compare data in two different tables and list the data that does not match. Not sure if this is possible. Details below

Table 1

xyz - A1 - Do not use
ABC - A4 - Use

Table 2

123 - A1

The result of the query should show me

A4 (as this does not appear in table 2

I am not too familiar with Null values as there will be many null values that i want to ignore.

Any help will be appreciated.

View Replies View Related

Pulling Info From Tables

I am trying to get an email address from a table by choosing a employee number from a combo box...it is almost working,
however, it is returning the employee number as opposed to the email address...here is the code I am trying to use:

Private Sub Combo0_AfterUpdate()

Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = myConnection
Dim mySQL As String

mySQL = "SELECT EmailName .*,[Employees] FROM EmployeeNumber"

myRecordSet.Open mySQL, , adOpenStatic, adLockOptimistic


Set myRecordSet = Nothing
Set myConnection = Nothing

End Sub

Then, in the section for the command button and the Outlook code, I have:

eMailAddress = Me![Combo0].Value

like I said, it is returning the same value that is in the combo box...if anyone could help, it would be greatly appreciated

View Replies View Related

Pull Records For Report With Entered Values - Version: 2000 (9.0)

I am working on rewriting a database for Drug and Alcohol testing. I need to pull a client's employee records to be run thru 2 queries 1. to set random numbers 2. to pull a set number of employees for testing. I would like to be able to use a form to enter the criteria for the report - i.e. Client ID, Pool ID, Drug/ETOH, # of Emp to pull or Percent of Emp to pull. I found the BuildCriteria method but I'm not sure if this would be the way to go or how to write the code to use it. I would appreciate any and all help I could get.

View Replies View Related

Change Control Colour Based Upon Value Entered - Version: 2000 (9.0)

Hi, I am seeking a way of solving the following problem.

How do I change the value of a control, based on the value entered? (MS Access help says; I should go to the Developers Solutions Database). That is, if the user enters an incorrect value into a field or control, how to I write some code to make the control go from a background of white to a background colour of red ?

I think this may be something to do with the "Iif" command.

If anyone can help, may I thank you in advance for your help.

Kindest Regards
David Roper

View Replies View Related

How To Print The Date Values Entered Through Parameter In A Repo - Version: 2003 (11.0)

I have report that created a chart using a query. I am getting the date range from the parameter prompts.

The chart doesn't display the date range. So I am trying to display the date range I got from the parameter on the Report Title.

I created a Text box and set the control source =[Begin Date]& " To " &[End Date].
When I run the report it prompted for date range twice. Once for the query and once for the report title.
Then I went to properties and chose the report property. set the Record source to the query name. Now it prompted for the date range only once. But now the chart was dispalyed more than once (for every record) instead just one chart.

any clue how to fix this?


View Replies View Related

Custom Report Based On Info Entered In A Form - Version: 2003 (11.0)

I'm working a summer internship at a machine shop this summer, and one of my tasks is to build a database that allows the employees in the tool crib, and the employees in engineering to add tooling assemblies, new tools, new extensions, etc..

I chose to use a database even though i have never used access before because i know all that data would just get ugly and intimidating in excel

I have the basic database up and running, which allows users to add in new tools, holders, extensions etc.. through the use of forms. I also have a report set up that prints out ALL of the assemblies in the database (assemblies being compilations of one "holder," one "extension" and one "tool").

Now, the question is if i want to print out a report that only contains, say, assemblies 1, 5, and 10, but dont want to constantly create and format a report with the wizard each time i need to make a new assembly sheet for each part, can i make a form, enter which assemblies i need (you can assume there is a constant number of assemblies per part - in reality there isnt, but ill cross that bridge when i get there), maybe a part number and a few other random pieces of data and have access automatically generate a report for me?

keep in mind i'm only familiar with the very basics of access (i just dont know where to look to create this). I'm not opposed to using some VBA script as i can do some elementary basic code, but i dont know all the commands i would need to use.

from reading this over before i post it, this seems like it would be a pretty common request. If anyone knows of a guide that outlines how to do this that would be greatly appreciated as well.

an intern in over his head

View Replies View Related

Filling Textboxes Based On Whats Entered In A Textbox - Version: 2003 (11.0)

Hello all,
I have several textboxes that fill based on what I enter into one textbox (Ill call this the Tnumber textbox), I put the code behind the on exit event property of the Tnumber Textbox and it works fine, when I tab to next textbox all the textboxes fill with the correct information,BUT!!!, if I go back and change the Tnumber textbox the textboxes keep the data from the original Tnumber entry, Im not sure how to go about solving this, Any ideas, Thanks in advance, below is the code I used

Private Sub TxttNum_Exit(Cancel As Integer)
Dim TableName As String
Dim dbname As String
dbname = "c:studentswithdisabilities"
Dim ChangeCount As Long
TableName = "InputStudent"
ChangeCount = 0

Dim CustDB As Database, CustTable As Recordset
Set CustDB = DBEngine.Workspaces(0).OpenDatabase(dbname)
Set CustTable = CustDB.OpenRecordset(TableName, dbOpenTable)
Do Until CustTable.EOF
If CustTable![Tnumber] = Forms!InputStudent!Txttnum Then
Me.TxtAgr.Value = CustTable![AgressoID]
Me.TxtStu.Value = CustTable![First Name]
Me.Text124.Value = CustTable![Surname]
Me.TxtAdd.Value = CustTable![Address]
Me.TxtTown.Value = CustTable![Town]
Me.Text122.Value = CustTable![Country]
Me.TxtCourse.Value = CustTable![Course]
Me.Text127.Value = CustTable![Year of Entry]

ChangeCount = ChangeCount + 1
End If
End Sub

View Replies View Related

INNER JOIN Based On Equivalent Values, Instead Of Equal Values

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.

I appreciate your help in advance.

View Replies View Related

1 Query Pulling Info From Multiple Identicle Tables

Hi guys.
For my coursework I was told I need complex queries to gian high grades.

This is my problem:
I have many many tables all identicle. They have the same field names just different information on them.
I wanna be able ot query ALL those tables to bring 1 result which I will then make a report out of.

I have tried everything, I fidled with the relationship but can't understand it.

Oh, and the tables have been linked form excel. I know i can't edit it then, but i dont need to.

I would really really apretiate all responses as this needs to be done asap.

Thanks in advanced.

View Replies View Related

One Report, Three Tables – It’s Pulling My Hair. - Version: 2003 (11.0)

I have created a DB that will help me organize my meetings. When opening frmSchedule I enter the dates, then the schedule meeting and location and then on the SfrmPersonMailingList I enter the people I want to send the report to. All works fine. Generating report: rptSchedule2 is displaying all the events grouped by the date, which is how I like it, BUT, here is the problem:

I want to have the list of the people who I listed on the subform SfrmPersonMailingList at the end of my report. So I will make a text box “People notified”: and then Access will list them.
I can not make it happened.
Please lookup the attachments and see how can you help. I have a feeling it is simple but I can not figure it out.
If you have any other thoughts or ideas please bring it up too. Thank you much!!

View Replies View Related

Pulling Data Out

I have a member roster. I have members who have attended. How do I subtracta list of those who have attended from the roster to find those who havent attended. The only way I know those who have attended is by their sign in date.

View Replies View Related

Pulling No Data

How could I pull in someone when they do not have data?

There is a name table and data table. My goal is that when I run a parameter query if someone does not have data for that time period to have that name pulled in so that eventually a report could be generated stating there is no data for this person for the time period.

View Replies View Related

How To Check If Entered Data Correct According To Hard Copy Data - Version: 2002 (10.0) XP


Is there a typical way to verify if the entered data correct or not according to the hard copy of data sheet?

Is it possible to ask different people to enter the same data or just same people enter same data after some time period, then compare these two groups's data to check? Because there will be a lot of data, it is too time-consuming. Do you have any better idea to confirm they enter correct data?

Thank you very much for yoru kind help!

View Replies View Related

Saving History Data When New Data Is Entered. - Version: 2003 (11.0)

I have 2 tables. One is employee info table, the second is a perf review table. They are linked together by the employee id field. Have a form for each one also. You enter data into employee info form, then click a command button to open perf review form. After enter perf review info, it saves to perf review table, overwriting any previous perf review data for that employee id. How do I get the table to save the new perf review info for the same employee id instead of overwriting the old entry?

View Replies View Related

Pulling Data From A Table

I trying to build a usage tracker. Users will take a reading once a week and enter data. I want to open the form and have 2 fields filled in based off the last reading entered into the table. I have an ID field (autonumber) and I tried using that field -1 to populate my other fields but it does not work. any suggestions


View Replies View Related

Pulling Data From SQL To Access DB

Ok, so I am creating an Access Database to track the progress of our collections staff. I need to pull data from a remote SQL database and filter it so that the appropriate records are appended to the appropriate tables in teh Access DB. Any ideas?

View Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved