Design using relationship Results


I am new to this site so please feel free to direct me where I need to be or correct my posting style. Thank you in advance.

I am tasked with creating the inventory system for the company I work for. For this project I am using Access 2010 in the .mbd file format. I have a pretty good understanding of visual basic, but I have no experience with Access.

I am currently struggling with setting up my tables and relationships. Our inventory is comprised of completed assemblies with their own unique part numbers, sub-assemblies with unique part numbers, and component pieces with unique part numbers.
If I understand correctly there will be a many-to-many relationship because complete assemblies can use multiple components and components go to multiple assemblies. I don’t understand how to factor in sub-assemblies correctly.

I need to keep track of the amount we have in stock at each level, and where something is in processing. For example, let’s say part number 123 is comprised of components, 1, 2, and 3.
I need to know how many completed 123 parts I already have, what components 123 is made up of, and how many components I have in each classification of stock. I also want it set up so that if I process more 123 parts, the components are automatically removed from the system.
Below is a very basic take on what I think my tables need to be. I understand that these tables are almost definitely incorrect but I hope they convey at least a basic understanding of what I am trying to accomplish.

In summary, I need to know how to relate all this information to create a solid base for our inventory.

(PKEY) part_number

(PKEY) part_number

(PKEY) part_number


Please see attachment and make comments.
Anyone interested in the EPA rules for managing, reporting CO2 emissions (and much more) may find this useful.

Each Facility has equipment to measure Emissions (e.g. CO2 and much more).
A Facility ID - is already in place (not shown)
Each Facility ID can be issued a Permit. The permit has a list of Points.
The Points have a description of the item, emission limits, unit of measurement...
The Permit venison (a.k.a. Issuance) can be modified

The Issuance can Add, modify, or Inactivate a Point or the Point's values.

If a new Issuance comes out, the process would be to:
- Clone the existing Issuance - put the End Date on the old Issuance
- Insist that the User read the new Issued Permit and modify the Points values to reflect the changes.

For example: Issuance 2 - starts at a specific date - changes an existing Permit.
- existing Point can now emit only 80% of CO2 and 105% of NOx from last one
- Add another Point "008" of type (a.k.a Description) "Boiler" - values and Unit of Measure of emission are stated for this new point in the Permit.
- Old Point 003 is now inactivated - emission allowed is zero

The Point "Type" (a.k.a. Description) is a table (not shown) A point type can never change. Its values or activity can change.

This is just a top level view. The audits must correlated each Facility's sample readings (e.g. CO2 emitted that month) in relationship to the Permit / Issuance allowed during the permitted period, for each Point listed in the Permit.

All comments, observations (pro / con) are very welcome.

I'm trying to create a query combining multiple tables together to create a report, but I ran into an error when relationships are involved.

I have one table that holds address information for Facilities we do transports with, a table that holds our mailing address and billing address, & a 3rd table that handles the details of each individual trip.

The detailed trip Table uses a dispatch form to fill in information, and I'm using combo boxes to select pick-up & drop off locations, and each is connected to it's own subforum to show address/phone/ect information. My master field on the form is a number field, and my child field is the autonumber field that holds the unique id for the location to show the details in the subforum.

This has worked just fine with no problems, but now I'm trying to create a report to create individual statements for clients we transport using a query that pulls all the necessary information together. This is a problem as the pick-up & drop-off fields in the table for individual trips hold the ID # for locations, but I have the Relationships set to show the Facility names. This creates a mismatch error since the facility name is a text field, unlike the pick-up/drop-off fields are numbers.

I've tried playing around with the types of fields, but I'm locked into the current design for the dispatch form, and am unsure of ways to redesign this.

Can anyone point me to a knowledge base/faq or anything to help show me the way to fix this?

I'm designing this in '10, but is using a '03 database to work with access '03.

Edit: I stumbled upon "Joining" in Queries, which helped a lot

I am trying to finish an Access database that someone else started. In many of the queries, the design grid is missing.

In design mode, I can see the tables and relationships being used, but the bottom portion is simply not there. I have tried scrolling to the bottom to check and see if it is just "hiding" but it is not there either.

I can view the query fine in SQL mode but it is complex and therefore time consuming to make changes this way.

Any suggestions to where my design grid is hiding????

Hi all,

I have inherited a Database that was previously in Dbase/Paradox structure with reporting.
I have all the data – a single flat file in Access called companies.

The previous system had basic DOS data entry/edit feature but more importantly, had an easy to use, reporting tool.

i.e. for many of the key fields used, [name, country etc.] simply add a value to the fields you want to query, and select a report template to write to that uses the records that are true to the query.

It was a very non technical [and loved] tool.

Now I have this table in Access. I have a nice data entry form, plus have some very basic reports plus a slightly [and I mean slightly] more complex query that uses several dates [between two dates] and companies in a query [‘or’]

This query works but is in a raw state and hardly suitable for the end user. I end up having them send me the latest Access database and then make a query myself using the design mode.

I would LOVE to put a nice clean query entry form that then passes the query values to a query without the user needing to remember things like Boolean statements. I wanted also to support wildcards [i.e *]

On this note, [sorry so many questions – but I though Access would be quicker to understand than this] bearing in mind this is a flat file, I wanted to use drop down lists for several fields so they show only [unique] values for that field. i.e. for the country field – don’t list all 500 entries.. just the unique ones – of which there might only be 10.

Once all this is done – my next step it to report the results BUT… be able to embed fields in the body of the report. Seems this I cannot do and have needed to use Word and the mail merge feature… I was a little disappointed but will get over it. The Question then becomes, can I link the word doc to the submit button on the query so it opend the word doc and populates automatically?

Sorry for all the questions but I am [was] Delphi programmer and it has been MANY years since I worked with VB [even VB DOS…. Now there was a great tool] I really did think Access would hold my hand more for this as my impression is that there too many ‘bits’ are required to do things… probably just me of course. :-)

Thanks in advance.


I'm having a little problem with an Access Query. Simple table design, with relationships as normal, using a junction table for a many-to-many link.

Table1 Field: Area (Text)
Field: T1_ID (AutoNumber)Table2 Field: T2_ID (AutoNumber)
Field: Value (Double)Table3JoinField: Area_ID (Long Integer) *Rel: Table1.T1_ID
Field: Value_ID (LongInteger) *Rel: Table2.T2_IDTable4Field: T4_ID (AutoNumber)
Field: Area (Number) *Rel: Table1.T1_ID
Field: Description (Text)Query1
Sources: Table4, Table3Join, Table2, Table1
Fields: Description, Area, Value


	SELECT Table4.Description,Table4.Area,Table2.Value
FROM Table2 INNER JOIN ((Table1 INNER JOIN Table4 ON Table1.T1_ID = Table4.Area_ID) INNER JOIN Table3Join ON Table1.T1_ID =
Table3Join.Area_ID) ON Table2.T2_ID = Table3Join.Value_ID;

However, with this design as it is, I cannot "edit" the Description field, or change the Area field. The entire Query becomes read only. Why is this? And how should i re-write this to allow me to use the relationships which Link Table2.Value to the appropriate Table4.Area (and display them both in one query), while allowing me to edit all the sub fields.

Jaeden "Sifo Dyas" al'Raec Ruiner

Using: Access XP 2002 (10.4302.4219) SP-2

Hi im stuck on filtering a combo box (i am using an sql query to populate a combo box with a filtered selection, this is in Access but im after some design sanity checking). I have users who can be assigned a category. Jobs can be assigned a category and also have a list of people working on that job (in the JobDetails table). I have the following tables:

Categories Table:
CategoryID (PK)

Users Table:
UserID (PK)
CategoryID (FK on Categories.CategoryID)

Jobs Table:
JobID (PK)
CategoryID (FK on Categories.CategoryID)

JobDetails Table:
JobID (FK on Jobs.JobID)
UserID (FK on Users.UserID)

Then in the job details when listing users for a job (many users can be for one job) i would like to only show the users which have the same category as the jobs category. Is this possible? i tried the SQL below for the lookup column field JobDetails.UserID but it doesnt work:

SELECT Users.ID, Users.Username, Users.CategoryID
FROM Users, Jobs
WHERE (((Users.CategoryID)=[Jobs].[CategoryID]));

All the tables are linked with relationships but my SQL isnt so hot!
Any ideas as to how i would do this and get it working?

Even if it can be done, is this even recommended? I can see funny conditions happening if the job details category changes or the users category changes then even if they are existing in the job details list they will not be shown? Even so, i would be interested in the above to know how it is done (if possible).

Thanks in advance,


I tried to search for this but I am not sure how to search for this kind of problem as English is not my first language, sorry

I have this kind of situation:
I have a many-to-many relationship in my database and I have these three tables:

Now, I need to calculate the following value for each customer: balances to assets ratio which = SumOfCUSTOMER_BALANCE/SumOfASSET_VALUE. I can do this easily if there is a one-to-many relationship (eg. customer is connected to 3 assets and each of these 3 assets is connected to the same customer). Then I just use the totals query in query builder and check if the relationship is one-to-many...

Now how could I approach this problem if for example I have a customer, who is connected to 2 assets and one of these 2 assets is in turn connected to some other customers? There can be situations where 4 assets are connected to 10 customers etc...

So far I have done this manually in Excel by making this a table with the following fields (just search-copy-paste):

Then by summing all the customer balances and asset values, I calculate the correct balances to assets ratio. Now I know that it is stupid way to calculate it manually like this....

I have only experience using the design view to build queries

Thanks in advance!


I have created a subform where you select a vendor from a combo box and this then allows you to view all software of the selected vendor in a tabular view. When I use this form and then try to go into design view or close the form, i get the following error message:

'The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again'

I have tried removing the index and changing the index to permit duplicates but this has not worked.

Could anyone tell me abit more about this error and how to get rid of it?

Many Thanks

I have some tables that are set up as follows (these are not the actual names of the tables and fields, I'm using generic names so that I don't get a bunch of questions about the setup to begin with; I believe they are set up in the best possible way):

Table1 has a one to many relationship with Table2
Table1 has a one to many relationship with Table3
Table2 has a many to many relationship with Table3

Fields for Table1

Fields for Table2

Fields for Table3

Fields for Table4

Can anyone offer some advice on the best way to set up some forms for data entry with these tables? I think having Table1 be the recordsource for a parent form, having Table2 be the recordsource for one subform, and having Table3 be the recordsource of another subform would work for the one to many relationship between Table1 and Table2 and the one to many relationship between Table 1 and Table3. But I can't figure out how to then include the many to many relationship between Table2 and Table3.

Hi there

I've got 3 tables related to one another with one to many relationships. They are:

where the 23_Indicator table has 22_SubCategory as its parent and 21_Category as its grandparent.

The forms wizard in Access has nicely developed a form with 2 subforms contained on it which does the trick for maintaining all 3 of these tables. The form itself displays 21_Category, subform1 displays 22_SubCategory and subform2 displays 23_Indicator. When I change the active record for either Category or Subcategory the subforms change their display as expected. All good.

My concern is that the form looks a mess and not very welcoming, too much info on one form, so I'd like to replace the 2 subforms (or even form plus 2 subforms) with one Tab Control. So far I can get the linking working correctly to the first page within the Tab Control but cannot link the second page child records to the active record in the first.

I'm using the Link Child Fields and Link Master Fields to do this. Even straight copying of the entries from my subforms which already work cannot get the Tab Control to behave in the same way.

Without wasting any more time on this, does anyone know whether it is easily possible or I'm missing something obvious? I suspect I've got something slightly wrong with either the Source, Link Child Fields, or Link Master Fields syntax, or referring to the wrong objects. Alternatively, Tab Controls just aren't designed to show one to many type records??

I'm not a VB Developer so if this isn't possible without writing a whole heap of code then I'll just have to cut my losses and use the crowded form I already have, though I'm happy to insert a few lines to get the Tab Control to work.

Thanks for your help in this. Apologies if this has already been posted loads before. I'm doing this as voluntary work for a charity and I'm a fairly new user of Access and VB (though I'm familiar with databases and SQL).


I foolishly said I would develop a database for work, and I have been trying to teach myself and have become stuck. I am trying to build a database of patients and treatments that they have received in the service in which I work. Each patient can have many treatments, and each treatment can be given to many different patients.

So far I have 3 tables:

patientID (PK)

treatmentID (PK)
treatment (CBT, Group therapy, etc)

and a junction table

patientID (FK)
treatmentID (FK)

I have set up a many to many relationship via the junction table.

All I am trying to do at this stage, is have a form with patient details on it, and a subform where it describes what treatments the patient has had (with the option of editing and adding new treatments).

I have used the form design wizard to make the main form based on tblepatient_treatment, and the subform based on tbltreatment_history.

The subform is set up in datasheet view, and consists of treatment_historyID (PK) and treatmentID (FK). I have turned the treatmentID (FK) into combobox. I want this particular box to display the text descriptions of the treatments from tbltreatments.treatments, but add extra treatments to tbltreatment_history.

At the moment, it allows me to add treatments to tbltreatment_history, but displays them on the subform as the ID numbers from the treatment table, not as the names of the treatments - which is what I want.

Previously, I have tried setting up the subform based on the tbltreatments, and this allows me to have the treatments to be shown in text (e.g. CBT, group therapy, etc). However, if I try to make an addition to the subform, it creates duplicate types of treatments in tbltreatments, rather than make additions changes to tbltreatment_history. (Actually, if I try to make changes, access tells me that the field cannot be updated, but makes changes/additions to the tbltreatments anyway.
I reckon basing the subform on tbltreatment_history is the right way to go, but how to I get it so the user can choose between the text descriptions of treatments stored on tbltreatments?

Please note, that the service may develop more treatments in future, hence I thought it would be a good idea to have a separate table for treatments.

I would be most grateful for any help.


You're about to witness a grown man cry.

Thanks to this forum I've managed to put together my database. I believe it's fairly good (for me at least. I'm sure you Access MVPs could whip something twice as good in half the time!).

Anyway. My table done, I thought I'd design a form. Easy peasy!
So I thought.
Nothing has brought me quite as close to killing myself.

Let me give you a small example of my problem.





OK. So my relationships are all set up. Everything should work. I go into designing my form and that's where it all goes wrong. I'm going to take it slow and explain step by step. I'm not doing this because I think you're all as thick as I am (that's impossible) but because I want to make sure I explain it properly because the Samaritans are just about fed up with listening to me.

- I create a blank form.
- I insert the fields CharityName, FirstName and LastName.
- I go into Form View and check it works: it does. I try and enter some text. It does. I don't create any records though.

- On the right hand side of my window I see the Field list. Under "Fields avaliable in related tables" I see tblTitles and tblPosition. I click the little plus sign next to them and am shown the fields that those tables contain.

- I drag over Title and then Position.
- I go into Form View to test.
- I can enter information into the Title & Position fields. I cannot enter anything into the CharityName, FirstName and LastName. If I try to do so I am given the error message: "Cannot add record(s); join key of table 'tblCharity' is not in recordset'.

- I cry.

Now I have found that by using a subform I can input all the data needed but I think the subform layout is awfully ugly for something as simple as Title and Position (especially a "Mr" and then "Chairman" or something like that).

Now I've actually got 12 tables, but basically I need to know how I can put fields from different tables onto one form.

I hope I've explained that well. If not I'll zip up the DB and post it if it's easier.






hi please i need help seriously in buildng this db with the tables listed above.
the staff table is related to the dept(one-many). staff, dept, prog and org tables are connected by the attended table with one to many relationship

the db is for staff from various dept of an org that are send on programs offered by various organization.when it is complete, it should be able to keep record of who attended what program, what prog were provided by which organization etc.

the major problem i am facing is with the form. also iam not sure if the relationship is right as i am new to access. i want to be able to design a form that can hold information from all the tables so that when a new employee comes and is going on a course it can be entered directly into the form likewise , if for exampls emp 234 has been on a prog when i type his number on the form his details will come up

any suggestions will be greatly appreciated. thank you

I am currently working at a college with student records... I am trying to work with a newly formed Access DB. There are 5 Tables (Student Info, Admission Info, Certification Info, Employment Info, and Standards Review Info) All Tables were linked through the SS#... The only designated primary key was in the SS# in the Student Info Table.

The way that the person (who is no longer at the college, or I'd ask them directly) set it up, the relationship was one to many. I don't know WHY they set it up like this, because there is absolutely NO instance where there should be more than one record per table for each student.

PROBLEM #1. While in a ONE TO MANY relationship, I noticed something inconvenient about entering data into records within a QUERY. If you have a two table query, you can enter data DIRECTLY into the query. This is very handy when updating scores or GPAs. You just run down the list entering the info.

However, it seems that you CANNOT enter information in a one to many relationship when you use THREE OR MORE tables. This would be handy when you want to run a query using Student Names (Student Table), Set the criteria for everyone who graduated in 2005 (admissions Table), and enter information regarding test scores (Certification Table)

It works when I do set the relationships as one to one and I believe I understand the underlying logic.

If you have a query that has two tables, every field represents an existing field on an actual record on one of the tables... If you have three tables, then you will have query fields that don't acually exist to input info, so it doesn't allow you to. For instance, Table 1 has one record with the primary key, table 2 has two corresponding records to that key, table 3 has ONE record for the primary key... In a query Table three will look like it has fields that correspond with both table two records when in fact it only has one record field available. You can't input two different pieces of data into one field of one record and have them both exist at the same time. If this COULD happen the whole universe would implode into itself...

Okay... so no prob... just make it a one to one and be done with it you say... Well, when I've done that it totally solves the query problem... however it leads to the next problem, which I just can't get my brain around...

PROBLEM #2. Each Table has a form that corresponds to it. (Student Info, Admissions, ETC.) When the data entry person enrolls a new student... these are the only things they will see. The first form that comes up is a switchboard form that allows them to add a new student. This pulls up the Student info form.... After entering the basic stuff and the primary key... the way that it is designed, the person needs to go forward one rec and back one rec in order to save the info. All the corresponding forms are opened from buttons on the student info form. The next action the data entry person does would most likely be to hit a button that opens the admission form..

When you hit the button the Admis form opens, the SS# (Primary Key) is shown as well as the Name of the student. The SS# on the form is actually from an underlying Admissions FormQRY that includes the SS# from the student table. The button that opens the admission form has the following code...

Private Sub Command48_Click()
On Error GoTo Err_Command48_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "UpdatedAdmissionsFORM"

stLinkCriteria = "[SSN]=" & Me![SSN]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit Sub

MsgBox Err.Description
Resume Exit_Command48_Click

End Sub

Okay, so here's the thing. It works FINE with the ONE TO MANY relationship... All the info is saved into the admissions table...

When it's a ONE TO ONE, however... The Admissions form will open, the ss# will be present there, but AS SOON as you enter info into ANY other field on the Admissions Table, the ss# goes to 000-00-0000 ! The name, however STAYS the same... (of course all the info is still present on the Student Info Form)

I cannot get my head around why this should happen?

Does anyone have any suggestions to this problem?


I’ve never REALLY understood cascading combos, though I have muddled through before thanks to some incredibly helpful and patient people on this forum. I’m hoping someone can do it again. Maybe the problem is with my design or relationships this time?

Two of the fields in my main table tblRisks are “Category” and “subCategory”. I have a separate reference table for each of these:

CategoryID - autonumber
Category -Text
Current -Yes/No

SubCategoryID -autonumber
ParentCategory -a lookup to tblRefCategories
subCategory -Text
Current -Yes/No

I will be using the “Current” field to switch on and off some choices from time to time.

I have a form, frmRisks, based on tblRisks and when I’m entering data I want the subCategory combo row source to update, depending on what I choose in the Category combo. But I can’t make this work. I have attached a zipped version of my database and the AfterUpdate event of the Category combo on frmRisks will show you how far I’ve got. I’m sure there’s a fundamental error somewhere, and I really need to get this working soon.

I would be hugely grateful for some assistance.

Hello all,

I'm developing an Employee/Staff database and have done a lot of work trying to get the structure right on paper. I'm coming to now implement the design but there's something I'm stuck with.

Basically I have a SalaryScaleFrom and SalaryScaleTo field in the Post table, I want the user to use a drop down box from table tlkSalaryScale, however I'm unsure how to create this relationship as they both need to access the same table.

I've currently set the relationships up (in relationshp view) as two of the same table ie: tblPost to tlkSalaryScale on SalaryScaleFromID and tblPost to tlkSalaryScale_1 on SalaryScaleToID. This doesn't seem to be correct though.

Should this lookup be created as a relationship or as a link in the form? I only want to look the values up and store the selected ID in the Post table. (I've purposely avoided the table lookup feature as recommended in various best practise guides)

I have another query too but I'll do a separate entry for that.

Maybe I'm just having a blonde moment but many many thanks for any help, it's much appreciated.



So, for the first time, I have a 1-to-1 relationship between two tables. But, I'm having an issue with the form...

The Main Table is tblProjects and the related table is tblFees. Not all projects have fees assocated to it. With that said, I expect to have 0 records in the tblFees table if the related project record has no fees.

For the form, I have tblProject as the main form and tblFees as a subform. I have many different subforms and use a drop down box that acts as a "menu". It simply change the subform's control source to the selected subform.

When I choose the tblFees subform it seems to automatically try and create a record, even if no parent record (Projects) was created yet. This obviously triggers and error. If the Project record was created already it still seems to add a record with all null values.

Essentially, I dont want a record created in tblFees unless the user supplies data in one of the tblFees fields.

Is this just a poor form design or is this a common issue for 1-to-1 relationships.

Thanks in advance.


If i have 3 tables:

Now when I select all these fields and create a form I get one set of address details but i really want TWO! I can use "field list" in design mode and duplicate address related fields but how would access store them in the database and maintain relationship?

Hi everybody,

I am new here, and to MS Access as well.
I am using MS Access 2007, but saving everything so that it is compatible with 2000-2003 format (.mdb)

What I want to do seems simple enough, however I am finding it very difficult to use Forms. (Maybe there is more into this than forms, but I guess I will find out sooner or later.)

Here's what I want to do:

I am trying to create a simple database to keep track of *Users* who have access to certain websites in a Content Management System.
The users are managed by adding them into *Groups*.
And the Groups are added to each *Website* to grant the required access to the Users.

* One or More users may belong to One or More Groups
* One or More Groups may have 0 or more users
* One or More websites may belong to One or More Groups
* One or More Websites may have 0 or more Groups

User01 is in Grp_Admin, Grp_Editor, Grp_Publishers
User02 is in Grp_Admin, Grp_Editors
User03 is in Grp_Publishers

And we have the following websites:


To give access to Users, the desired groups are added to the corresponding Website.
Website_01 (add: Grp_Admin, Grp_Editors)
Website_02 (add: Grp_Admin, Grp_Publishers)

As you can see, I have TWO Many-to-Many relationships between 3 tables.

So I have the following design: (Please correct me if I'm wrong)


(PK) GroupID

(PK) SiteID

(FK-from "Users")UserUPI
(FK-from "Groups")GroupID

(FK-from "Groups") GroupID
(FK-from "Websites") SiteID

================================================== ===

If the above is correct,

All I simple want to be able to do is the following:

1) Have an FORM to INPUT
(A) I want to be able to Enter a new User, and allocate that user to a Group (either an existing or a new Group)
(B) I want to be able to Enter a new Group, and allocate Users to this group (either existing users or new users)
2) Have a display area for records.
I want to
(A) search for a group, and list all the Users that belong to that group.
(B) search for a group, and list all the Websites that belong to that group.
(C) search for a user, and list all the Groups that this user belongs to
(D) search for a user, and list all the Websites that this user, in tern, has access to. [This might not be needed.. not sure yet.]
I just cant seem to understand how the FORMS work in Access!
I have a bit of a programming back ground, and I can see how I can program such a thing using Objects etc... and how I can create a form that a user can input which will populate a DataBase Table etc.. and how I could do a query and retrieve whatever I want, and however I want.
but.. I thought MS Access might have all these type of simple things already inbuilt..
And in fact I think it does!
I just cant seem to figure out how to use them!

So,.. any help would be great?!!

I have attached what I have done so far, but if anybody could point me in the right direction, it would be much appreciated.

Thanks a lot!

Not finding an answer? Try a Google search.