How To Relate Tables And Display The Correct Values..

Feb 15, 2008

I'm sorry if this is obvious, but I have asked local professionals and scoured this site for answers before posting...

I have a database that I'm using to store and analyze language samples (basically, the mistakes that students make when trying to speak in another language). For each bit of language, I want to be able to say what domain the mistake belongs to - e.g. the 'clause', 'phrase', or 'word' domain. I have a table containing the five domains. I also have a table for each of these domains, containing the specific types of mistakes relevant to that domain. It is conceivable, but unlikely, that I will ever add to the domain table, but highly likely that I will add types of mistakes in the five related tables.

As I analyze bits of language, I will be appending each analysis (record) to a table as shown in the attached image. So far so good. The part that I cannot fathom, though, is how to display (let alone perform calculations on) this data: While the ErrorDomainID is a foreign key to the ErrorDomain table, the ErrorUnitID could refer to one of five tables, so that an ErrorUnitID of 2, for example, could refer to one thing if the Domain is 1 but quite another if the domain is 1 or 5 or whatever.

I've tried all kinds of coding and SQL workarounds, but I have a feeling I'm just missing something very basic here. If anyone could offer some guidance, I'd be extremely grateful!


View Replies


Relate 2 Tables

Jul 17, 2006

I am trying to figure out create a new table from two other tables. It is a tax document and one has a pin field that is unique and the other the pin field is not unique. So in the table the that doesn't have a unique pin field, the pins occur multiple times for all the different tax enitites people pay to. I need figure out how to make one table that combines these too. I have tried using the relationshop function but i am not sure if this is what i need to do and i am not that familiar with access. If there is anyone that can help me please let me know. or if you need any more information.

Mclean county regional planning commission

View 2 Replies View Related

Help ! Display Values From Tables!

Sep 7, 2005

Hi guys,
I need urgent help on a simple question. I hope you will be able to help me.
I have a table CustomerMaster which stores customer number and name. (Customermaster)
I have another table which stores the product details for customer. (customerProduct)
I have another table which shall store order details. (CustomerOrders)
On the form, the user selects a customernumber, the system then displays the customer name.
The user then picks up the product ordered by customer (picked from combo box). On the combobox on recordsource I have given a select query which fetches the records matching the customer selected from table
Now I want the Product Price, tax and duty of the product to be displayed for the selected product. Somehow, I have to again make that as a combobox and then select the price (although there is only one record...
Can anyone tell me how to do this?

View 14 Replies View Related

Tables :: How To Show 3 Values But Display 3rd From Drop Down Box

Nov 20, 2012

I have created a table (data entered/selected via a form) which includes a drop down box, for 'Ethnicity' to pick a value from another table (ie used Lookup wizard to for options from another table).

The row source looks like this:

SELECT [Lookup_Tbl_ethnicity].[Code], [Lookup_Tbl_ethnicity].[Category], [Lookup_Tbl_ethnicity].[Sub-category] FROM Lookup_Tbl_ethnicity ORDER BY [Code];

Setting the ORDER BY [Code] displays the options in the drop down box in the correct sensible order.

The 'Bound Column' is column 3 - Sub-category. I assumed that this would be the 'value' stored, searched and displayed... but the table (and related form) display the Code.

how to change this so that the Sub-category is displayed?

View 5 Replies View Related

Display Form Correct With Different Resolutions

May 9, 2006

One of my probs with access is changing the form when a different resolution is being set.
Is there a visual basic module or something else to display the form properly dependent of the selected resolution?

View 1 Replies View Related

Modules & VBA :: Combo Box Will Not Display List In Correct Order

Nov 3, 2014

I have code attached to a command button to fill a Combo Box with data from a music collection. A letter of the alphabet is entered into a Text Box then records beginning with that letter are copied from a table, either by Artist or Title. They are saved to a temporary table at which time they are in no particular order. Those records are copied to a further table and saved in alphabetical order. This table is then used to fill the Combo Box.

I used two temporary tables because the records were not displayed in the correct order. I hoped this might cure it, it did not. The records are in order in the table but not in the Combo Box.

Private Sub Command68_Click()
On Error GoTo errTrap
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tempList;"

[Code] .....

View 3 Replies View Related

Reports :: Unbound Object Frame Failing To Display Linked Image At Correct Resolution

May 18, 2013

I am using an unbound object frame to display an .png image file which is set in vba.

The image is an excel chart saved as a .png

The problem I have however is that the image I save from Excel is great quality, and is pretty tack sharp, but when I display the same file in the unbound object frame in access, it is not nearly as sharp.

I am setting the picture property of the control as:

ubImageUserChart = "c: empmyChart.png"

It displays alright, but is just a bit fuzzy - still quite legible, but it is a complex graph with a lot going on - has regressions and formulas etc on the graph, and they need to be very clear.

BTW - the unbound frame is the same size as the Excel chart which gets saved as a .png file. If I tile the images (Access unbound frame and original file in picassa preview) side by side - they are identical - size, orientation etc.

It is not practical for me to try and do the chart natively in access as it is way to complex. I am using access vba to drive an excel session to do all the statistical yack work and chart rendering, then displaying a png image of the resulting chart in an unbound object frame in access.

View 7 Replies View Related

How To "Relate" Tables

Jul 6, 2007

Hi I have read quite a few posts and googled loads after advice fron some of the forum users and am still confused (which is not hard :confused: )

I am in the process of designing a new database to keep records for patients (medical type!)

At the moment I am designing the tables and am somewhat confused as to how I would link these in the future?

The tables I will have are

Patient Details (First Name, Last Name, Date Of Birth, Address etc...)
Treatment Table (various fields relating to a treatment)
Consuambles used Table (induvidual items used to treat a patient and the quantities used, bandages plasters medications ect)
Vaccination Table (Various field relating to vaccinations)
Accident Report Form Table (various fields relating to a treatment that was need because of an accident)

What I hope to be able to do in the end is to "pull" up a form / report that will show all the treatments and consumables used for that treatment, Vaccinations and accidents a particular patient has had?

I really would appriciate any help anyone can give me on this but more so if they could explain it very basically

Cheers guys

View 14 Replies View Related

Subform Correct Answer Adds To Number Correct In Main Form

Mar 2, 2012

The code I have is.

Private Sub Command26_Click()
If Forms![test site]![prp test].Form.[A Right Answer] = -1 Then
Forms![test site]![number correct] = Forms![test site]![number correct] + 1
End If
End Sub

Then when clicked it checks a yes/no box to see if "A right Answer" is the correct yes. Then it should pop to the main form and take the number correct cell and add one to it. I am trying to get the record to go to the next record inside the sub-form but docmd.findnext seems to be wrong too.

View 4 Replies View Related

Tables :: Correct Table Relationship?

Nov 13, 2013

I am in the beginning of setting up a database and have NOT messed with multiple tables. I just want to make sure that I am setting up the tables as well as the relationships correctly.Here is what I currently have, 4 tables and they are named. Employee, phone, radio and spotter. Employee table is where my primary key is located and I currently have a 1-to-many relationship between Employee and the others.

What I want to be able to do is to store information in the multiple tables from 1 form, which I don't think will be an issue and then at a later date be able to pull up information. EX.Thus being able to skip anything being put into the spotter table. Then at a later date be able to pull up only the select information I originally put it?

View 5 Replies View Related

Tables :: Subform Not Showing Correct Records

Jul 26, 2013

Table Info:

EmpProfile: EmpID (Primery Key), EmpFirstName, EmpLastName, Emptitle, EmpPhoneNumber

EmpTimes: EmpID (1 to Many relationship with EmpID primery key), Timein, Break, TimeOut, Totalhours

Query Name:

Selects the table from EmpTimes
Calculates the total hours worked for an empid, on a given day. It produces the total hours as "CalcTime"

Form Info:

1st Form Name: MainForm
Text name to enter empID : Txtempid (unbound)
Record Source: EmpProfile

2nd form name: SFTimeSheet
This is a subform within main form
Record Source: QryTime
Child and master fields are linked using : EmpID

Expecting Result:

When enter empid on the MainForm, in txtempid
subform should retrieve all entries for that empid

Getting result:

regardless what empid entered in txtempid, subform shows only the 1st record available in EmpTimes.

View 3 Replies View Related

Tables :: Correct Database / Table Design

Jan 15, 2014

I am still new at database design, and cant quite come to terms with my project and access way of doing things.

I have to keep a register of people who participate in projects. The projects can be of two different kinds. BUT (here comes the tricky part) The projects are being evaluated on three different indicators, with each one of these having 4 measurements, in the range of 4-0. That was a quick introduction. Now let me break it down in parts.

The people:

I have made a Uniqe identifier (Social Security number (PK))

First Name
Last Name
Department (This can be 4 different departsment) made a drop-down menu type.

The Projects:

Unique identifier (Project ID (PK))
Social Security number
Project Type
Start date
End date

Project type:

Unique identifier (TypeID (PK)
Project type (Cti / Regular)

How might i design this the best way, so i can combine the people with the projects there on. And which type.

There can only be one person, but he can be on many projects. These projects can vary in type. My problem is ensuring there connected proberly.

Furthermore, once the basic design is made i need to make evaluations based on their performance if they are on the projecttype "Cti".

here i need 3 x this:


Objective (range 0-4)
Baseline reading (range 0-4)
Midway reading (range 0-4)
End reading (0-4)
Success = Yes/NO (here i will do a End reading <= Objective formula).

That was a rather long list, but i have sat working on this in three whole days, and im getting a little fed up with not knowing up-and-down.

View 3 Replies View Related

Tables :: How To Get Information Inputted On Form To Store In Correct Table

Mar 4, 2014

I'm having some trouble getting my information that I input on my form to store in my correct table. I will attach my DB so you can take a look at what I have thus far.

Here is what I am wanting to do:

I have TblEmployee, TblEquipment and TblJunction and FrmTracking and FrmUpdate

I input the bulk of my information thru FrmTracking, my trouble is I can not seem to get the information that I input in my FrmTracking to store in the correct table. I can get the information to store in TblEmployee, however the information that I want to be stored in TblEquipment will not store in there.

View 14 Replies View Related

Relate Chart To Search Form On The Fly

Mar 17, 2006


I'm having problems relating a pivotchart with search criteria automatically after the search - all on the same form. This will be a nifty trick if it is possible, and I can imagine other uses for a form like this.

Without being overly wordy, I attach a mdb in hopes that someone with a bit more skill than I will take an interest and set me on the right track.

View 1 Replies View Related

Relate List Of Colours To Unique Product Id

Jun 13, 2007


I want to select a product then be able to select a colour from a list that relates to it.

I want the colour lists to be different for every product. (clothes)

Whats the best way to do it?



View 4 Replies View Related

Populating The Text Box’s That Relate To The Chosen Combo Box’s With The Sum Of A Cal

Sep 29, 2005

Hi, I'm in the process of building a program in access 2003 for estimating roofs, I've ran into a little problem, as described below. any suggestions would be very useful

Populating the text box’s that relate to the chosen combo box’s with the sum of a calculation.

In other words if X combo box is chosen (There are many combo boxes to choose from) populate xx text box with the sum of a calculation.

Refer to the print screen example of my program:

Notice under the label felts the 15lb 36” felt combo box is chosen and it displays the value of 11 and next to the combo box is a text box that displays the value of 25 The calculation will be (11*25) = 275 this value of 275 must be added to the text box values that are being shown in the forms header under 30yr where it reads $4,606.80 and 40 yr $6002.80 only, this is because the 30yr comp and the 40 yr comp combo boxes are the only chosen roof types under the label roof types above the felt label.
Hopefully this makes sense.
Thanks -- Any help with be greatly appreciated.

View 3 Replies View Related

Forms :: How Does Access Relate A Control And A Label

Oct 14, 2013

How does Access relate a label to a control?

We can delete the label, and then re-'attach' it, but there seems to be no visible property that retains this information - in either the label or the control (only done the detailed comparison for text box so far.

The reason for this is to be able to export enough data to recreate an Access form in html, and I need a label value if it exists. (And for most controls, of course, it does!)

View 4 Replies View Related

Can Relate Field Names To Primary Key In Another Table?

Jul 17, 2012

I am a new access user and I have several questions primarily can i relate field names to a primary key in another table?

View 9 Replies View Related

Queries :: Calculation Based On Values Selected In Table By Looking Up Corresponding Values In Other Tables

Aug 29, 2014

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).

View 5 Replies View Related

Display Only Positive Values In My Query

Jan 30, 2007

Hi.. I am a beginner using access and there is probably an easy solution for this which i dont know about!

I have made a query where i calculate the tax deductions for a payroll system. my problem is that different workers are to be taxed differently given maritial status and number of kids as well as net income.

I have managed to calculate everything with the kids and wifes but now i need to sort the table after how much they earn to deduct taxes. I have a "first stage NetPay field" now, where i can see the Gross pay minus allowances for wifes and kids.

Now, if that value(first stage netpay) is under 8840 no more deductions are to occur. If the value is between 8840 and 10000 i have to deduct 15% of the excess above 8840

If the value is between 10000 and 20000 i have to deduct a further 174+ 25% of excess over 10000

If the value is above 20000 i have to deduct 2674+ 31% of excess over 20000

So far what i have done is make a new coloumn, where i take "first stage netpay field" and subtract 8840. If the value is negative i thought i could show only the positive values by enterin >0 in the criteria field for the query, this however will make no posts show up..

(I tried entering Abs() in the Field coloumn where i made the expression, and this worked. )

So my question is: is there a function like Abs() i could use that would convert negative values into 0?

Or do any of you guys have a better idea of how to solve this?

Hope someone can help me...


View 8 Replies View Related

Combo Box Values Display Slowly

Sep 12, 2005

My Combo Box is tied to a table with many entries. When I click on it to display the table values it takes several seconds to display. Is there any way I can "pre-load" the Combo Box (such as on form load, etc.) so when I click on it it displays more quickly? Or is there another way to speed it up?

Thanks ...

View 4 Replies View Related

Tables :: Count Unique Values With Different Values

Feb 7, 2014

i have one table in which ID is Primary ID with Different Values


1 A X
1 B Y
1 A X
2 C Z
2 C G
3 D U

it shows that ID 1 having 2 Name (A& B,with PAN, X & Y ,respectively).how can i get this that ID having More than 1 Value like 1 and how can i select only these records ID which having more than 1 value and how can i update values for 1 ID.

View 8 Replies View Related

Queries :: Combo Box Won't Display Existing Values

Nov 28, 2013

I had a text box in a contacts DB form for the State field (Named txtState). It worked fine. I decided to replace it with a combo box. Now not only will it not display the existing values, but to rub salt in the wound it won't allow me to select from the drop down list. The error message that says that the field is too small for the value. I tried changing each of the yes/no properties one at a time but none of them change the outcome.

I have attached a couple of screenshots:


View 3 Replies View Related

Display Labels Instead Of Values In A Subform Datasheet

Sep 10, 2014

I have a subform that is displayed as a datasheet, and the source of this data is from another database. Essentially, it shows someone's name and status, but the status is displayed as a number. The people using the database don't know what this value means, so I was asked to display the label instead.

The values still need to remain in the database where the data resides, but can I use a lookup table or some other mechanism so that the label is displayed in the database the users are viewing? How exactly do I do this?

i.e...the data below resides in Database A but is displayed in Database B:
Col1 | Col2 | Col3
John | Smith | 2
Jane | Doe | 1

And in Database B only, it should show as:
Col1 | Col2 | Col3
John | Smith | Inactive
Jane | Doe | Active

Note that the users of Database B will not be updating Col3 data, they will just be viewing it.

View 1 Replies View Related

Queries :: Criteria To Display Records With Values >0 In Different Fields

Sep 24, 2014

i made a query to display students results after an exam. there are 12 subjects but a student chooses 8 from that. After an exam, the results are keyed in and a result slip is printed later. The problem is that the result slip displays all subjects including those not done by the student. I typed a criteria >0 in one of the fields and no records appeared. Is there a way that i can use to display only the subjects chosen and marks attained by a student and leave out the rest?

View 1 Replies View Related

Forms :: Change Display Of Field Values On Combo Box

Feb 17, 2015

I'm trying to sort and filter a continuous form. I want to sort by descending and ascending, but I also want to have a combo box for filtering. For example, there a combo box for sorting with Value List as the RowSourceType, the list would be "Ascending" and "Descending", and there should be another combo box to choose the field to be sorted with RowSourceType as Field List from a query.

My field names are: "LastName", "FirstName", "MiddleName". But I want them to appear as "Last Name:, "First Name", "Middle Name". I want to be able to change the display of the field names within my combo box which should come from my query. How is that possible? I already tried to put caption in the properties but it didn't work.

View 6 Replies View Related

Copyrights 2005-15, All rights reserved