Understandind Relationships and queries

josephff

Registered User.
Local time
Yesterday, 18:29
Joined
Oct 25, 2011
Messages
72
so im trying really hard to understand relationships and kinda tough from the outside in. Ive stop coding this program to concentrate on learning how to query with relationships and see how they interact etc., but i dont know where to start. if i can see it working then i will understand it.

What ive done so far is create 3 brand new tables.


Branches
Customers
Jobsites

************
BranchId
Branch

CustomerId
BranchID
CustomerName

JobsiteID
CustomerID
JobsiteName
*************

ive set up relationships in this way...

Branches.BranchID one-to-many Customers.BranchID
Customers.CustomerID one-to-many Jobsites.CustomerID

so in theory (i guess) its saying that each branch can have many customers and each customer can have many jobsites. Still maintaining the jobsite relation to branch through the customer, right?

My problem so far is how do i start my sql with these.

Branches will never change but i want to (firstly) input a customer.

So typically i would do something similiar to

Code:
sql = "INSERT INTO Customers ([BranchID], [CustomerName] VALUES (" & globals.branch & ", '" & me.customertxt.value & "')"

the problem in this case is i actually need the branchid to go into the customers table to keep up the relationship and not the actual branch name, so how can i grab the branchid based on the branch name.
 
Last edited:
maybe i should do

Code:
dim branchidcheck as integer
 
branchidcheck = nz(dlookup("[BranchId]", "Branches", "Branch = " & globals.branch))

then come back to my insert query

Code:
sql = "INSERT INTO Customers ([BranchID], [CustomerName]) VALUES (" & branchidcheck & ", '" & me.customertxt.value & "')"
 
This will lookup the BranchID if you know the Branch name.

DLookup("BranchID", "Branches", "[Branch]='BranchNameHere'")

Replace BranchNameHere with the actual text of the branch name. Or to convert it to a variable use this:

DLookup("BranchID", "Branches", "[Branch]='" & VariableNameHere & "'")

Now the real question is, how do you know the Branch Name but not the BranchId? If this is a form, then you need to reconfigure the Branch input to a combo box and have it based on the BranchId, not BranchName.
 
This will lookup the BranchID if you know the Branch name.

DLookup("BranchID", "Branches", "[Branch]='BranchNameHere'")

Replace BranchNameHere with the actual text of the branch name. Or to convert it to a variable use this:

DLookup("BranchID", "Branches", "[Branch]='" & VariableNameHere & "'")

Now the real question is, how do you know the Branch Name but not the BranchId? If this is a form, then you need to reconfigure the Branch input to a combo box and have it based on the BranchId, not BranchName.

OK that sounds like a path to start on. How can i hide the ID in the combobox but still reference it.

I created a combobox and set its rowsource equal to a query pulling the BranchID and BranchName from the Branches table.

Now if i change the combobox to 2 columns i can see both the ID and the BranchName and reference is using the .column(0) property, however i only want to show the BranchName and still pull the BranchID.
 
scratch that, i just set the first column width to 0...

Thanks for your help so far..
 
Im having a hard time understanding the SQL for related tables....

How would I do an INSERT INTO statement for this:

I need to create a new customer under a branch

Code:
 INSERT INTO Customers ([BranchID], [CustomerName]) VALUES (" & me.branchcombo.column(0) & ", '" & me.customertxt.value & "')

so this should work for inputting a customer based on the combobox which has the branchid from the related table.

but then,

i'll need to insert a new jobsite under a customer where that customer is under a branch.

i dont understand how to do this...Ive tried to find some links with SQL in reference to related tables but cant find anything of decent recognition. I understand regular SQL just not how to do it where the table is related to another table.
 
Last edited:
When tables are related to other tables, you use queries.

see this for some samples http://www.w3schools.com/sql/default.asp

look at SELECT APPEND UPDATE queries

and JOINS Inner, Left, Right http://www.w3schools.com/sql/sql_join.asp



You may find this article of useful importance. You can use the process to build/confirm your relationships.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip


i appreciate the link, however Im already aware of common SQL language, just not with appending into 2/3/4/5/6/7 related tables at once. I did read the joins portion and that explains some of the SELECT statements, but again no help with INSERT INTO
 
i actually didnt see the last link until now. Ill read over it also
 
still havent really figured it out, but this works. Just not sure if its the proper way to do it. I did try to go into the access query design but it produced this:

INSERT INTO Customers ( CustomerName, BranchID )
SELECT Customers.CustomerName, Customers.BranchID
FROM Branches INNER JOIN Customers ON Branches.BranchID = Customers.BranchID;

and im not sure how to read this. It seems to be saying insert customername and branchid into customer but select them from branches where customerid and branchid are the same and
that is the confusing part....


Code:
    Dim StrJobsiteCheck As String
    StrJobsiteCheck = Nz(DLookup("JobsiteName", "Jobsites", "JobsiteName = '" & Me.JobsiteTxt.Value & "'"))
 
    If Not IsNull(StrJobsiteCheck) Then
 
        On Error GoTo ErrorHandler
        Dim sql As String
 
        sql = "INSERT INTO Customers ([CustomerName], [BranchID]) VALUES ('" & Me.CustomerTxt.Value & "', " & Me.BranchCombo.Column(0) & " )"
 
        DoCmd.SetWarnings False
        DoCmd.RunSQL sql
        DoCmd.SetWarnings True
 
        Dim IntCustomerID As Integer
        IntCustomerID = Nz(DLookup("CustomerID", "Customers", "CustomerName = '" & Me.CustomerTxt.Value & "'"))
 
        Dim sql1 As String
        sql1 = "INSERT INTO Jobsites ([BranchID],[CustomerID],[JobsiteName],[JobsiteAddy], [JobsiteAddy2], [JobsiteCity], [JobsiteSt], [JobsiteZip], [JobsiteCompletionDate], [JobsiteBeginDate]) " & _
        " VALUES (" & Me.BranchCombo.Column(0) & ", " & IntCustomerID & ", '" & Me.JobsiteTxt.Value & "', '" & Me.Address1Txt.Value & "', " & _
        " '" & Me.Address2Txt.Value & "', '" & Me.CityTxt.Value & "', '" & Me.StateTxt.Value & "', '" & Me.ZipCodeTxt.Value & "', '" & Me.CompletionTxt.Value & "', '" & Me.StartDateTxt.Value & "')"
 
        DoCmd.SetWarnings False
        DoCmd.RunSQL sql1
        DoCmd.SetWarnings True
 
        DoCmd.Close acForm, "Jobsite Info", acSaveYes
 
    Else
        Exit Sub
    End If
 
' error handling
Exit_SomeName:
     Exit Sub
ErrorHandler:
    MsgBox Err.Number & Err.Description
    Resume Exit_SomeName

^ this does what i want to do, but im just trying to do things correctly based on the principle of table relationships
 
Last edited:
Your main purpose of creating this thread was to sort out your tables and properly normalize them, but you've managed to deviate it from the main topic by making reference to your INSERT query in your first post and then referencing code in your final post.

I would advise that for the purpose of this thread, forget about your final goal and get your tables properly normalized and linked together.
 
Your main purpose of creating this thread was to sort out your tables and properly normalize them, but you've managed to deviate it from the main topic by making reference to your INSERT query in your first post and then referencing code in your final post.

I would advise that for the purpose of this thread, forget about your final goal and get your tables properly normalized and linked together.


Actually the INSERT query is the problem of understanding. I know how to make tables related. In fact they are all related in a chain event to each other. What i never understood was how to link back to a table 3 of 4 tables deep. Hence the title of the post " Understanding Relationships and Queries". I would advise that you look at this thread as a whole and not in singularity. I have figured out a way to make this work on my own, and although i may not uderstand completely the procedure for linking to tables deep in a relationship, it gets the purpose done while maintaining referential integrity.

Thanks
 
In terms of linking to tables "3 to 4 deep" in the structure you basically need to do just that. it's not uncommon to want Data from two tables, related via a third.

With regards to your jobsites table query:

Code:
        Dim sql1 As String
        sql1 = "INSERT INTO Jobsites ([b][BranchID],[CustomerID][/b],[JobsiteName],[JobsiteAddy], [JobsiteAddy2], [JobsiteCity], [JobsiteSt], [JobsiteZip], [JobsiteCompletionDate], [JobsiteBeginDate]) " & _
        " VALUES (" & [b]Me.BranchCombo.Column(0) & ", " & IntCustomerID[/b] & ", '" & Me.JobsiteTxt.Value & "', '" & Me.Address1Txt.Value & "', " & _
        " '" & Me.Address2Txt.Value & "', '" & Me.CityTxt.Value & "', '" & Me.StateTxt.Value & "', '" & Me.ZipCodeTxt.Value & "', '" & Me.CompletionTxt.Value & "', '" & Me.StartDateTxt.Value & "')"

I think this highlights a potential issue with your structure. You're storing the BranchID in this table AND the Customer Table.

What if the customer moves branches later on down the line? If you don't also update the jobsites table you have a potential to return differing results depending on how you query your information. The customer table might say that Customer "Fred" uses "Doncaster" Branch, but the jobsites table says "Fred" is using/used the "Dorchester" branch.

You could make a case that the customer record should contain only the Current branch used by the customer but the Jobsites table should contain the branch they were using when that jobsite entry was created but in some respects you simply can't know whether that's the case or someone just got it wrong when they created the jobsite entry, especially as it appears that the branchID is coming off a form rather than the customer record. I'm sure there are other cases where you could legimately want a jobsite record and a customer record, despite being directly related, to refer to separate branches but it sets of warning bells when I see it.

It perhaps also illustrates getting the information from tables related "3-4" deep.

if you want all Jobsites by Branch, for example (pretending there isn't a branch id in the jobsites table).

Code:
SELECT b.branchname, j.Jobsite
FROM Jobsites as J
INNER JOIN Customers on
  c.CustomerID = j.CustomerID
INNER JOIN branches AS b ON
  b.branchID = c.branchID

You don't actually want any information direct from the customers table, but in order to establish the Branches linked to jobsites, you need it to form that reference between the other two.
 
Last edited:
Thank you for the detail and the time it took to write it. Thats what ive been looking for.

i actually saw that i had the branchid inside the jobsite table and inside the customers table and i removed it last week. It is no longer in jobsites.

What if the customer moves branches later on down the line? If you don't also update the jobsites table you have a potential to return differing results depending on how you query your information. The customer table might say that Customer "Fred" uses "Doncaster" Branch, but the jobsites table says "Fred" is using/used the "Dorchester" branch.

Tables are linked in this way
Branch To Customer
Customer To Jobsite

No branch should have the same jobsite, as branches are in different cities. So if a customer does go to another branch in another city then it will be fine, a new record will be created using the new branch and existing customer with a new jobsite. As much if a customer has multiple branches then that customer will have multiple records in the customer table, each assigned to its own branch.

I could have easily setup the jobsite table to house the customer name and branchID, however in all my reading it always says to put each seperate thing into its own table so thats what i did.

if you want all Jobsites by Branch, for example (pretending there isn't a branch id in the jobsites table).


Code:
SELECT b.branchname, j.JobsiteFROM Jobsites as JINNER JOIN Customers on c.CustomerID = j.CustomerIDINNER JOIN branches AS b ON b.branchID = c.branchID
You don't actually want any information direct from the customers table, but in order to establish the Branches linked to jobsites, you need it to form that reference between the other two.

This is exactly what i couldnt understand: I needed to pull a jobsite but i need to be able to reference the CustomerId and BranchId that it is related to in order to ensure that i have the correct one. The problem for me other than the SQL was using recordsets to hold data (which i can figure out on my own). I've just always used a lookup to retrieve data.

The SQL for relationships is confusing and not as simple to parse through as standard SQL is. I can obviously see in a regular SELECT statement that "SELECT * FROM Table WHERE BranchID= 441" means exactly that.
Howver with inner join SQL its hard to read it and understand it (for me anyways)

To me the SQL statement above says "select branchname and jobsite from jobsites table" and then shows the links to the 2 tables (which btw is exactly how they are setup).

In my basic thinking, i say there is no branchname in the jobsites table so why is that in there.

Now as this is simple enough for reports of branch jobsites etc, what I needed to do was find a jobsite where the branchID was something and the CustomerID was something. So i thought how am i gonna find out what the branchid and customer id is, that way i can make sure i get the right jobsite.

So what i did was learn to use global variables for branchid, customerid, and jobsiteid to manipulate my queries.

So, whenever an existing jobsite is selected from the main menu (which shows the branch, the customer, and the jobsite from a listbox) the info is saved for future use.

And upon new creation, they are done in sequence. The branch is picked from a column box and the customer is saved first with the branch id reference. Once the customer is created, that CustomerID is moved to a global variable to create the jobsite and so on.

Again this may not be pro style but it does work. My code is forcing referential integrity and so far maintaining accurate data grabbing from the tables.

Thanks again for your time and effort on this. If you have any links that i can read up on i woudl surely do so. Ive just not been able to find any relevant ones for this particular situation (maybe im just not googling correctly)
 
To me the SQL statement above says "select branchname and jobsite from jobsites table" and then shows the links to the 2 tables (which btw is exactly how they are setup).
Once you get your head round it it'll be a lot easier. If the table in your FROM statment is the main table of information that you need perhaps the concept of a JOIN will become easier.

So when you have

Code:
SELECT mt.column, mt.column, mt.column (never use * in your final query if you can help it)

FROM TheMainTable

perhaps it will help to think of a JOIN as essentially saying "AND get me stuff from this table as well" (there's more to it than that, but let's just start at the beginning).

So

Code:
SELECT mt.column, mt.column, mt.column, [i]AND[/i] tt.Column 
FROM TheMainTable AS mt
[i]AND[/i] INNER JOIN ThisTable as tt ON ....

Even when you aren't showing information from the table in your select Columns you're still basically telling the database that you need information from that other table (to be able to link other tables into the information that you want).

So once you have a jobsite ID you can extrapolate from that both CustomerIDs AND branchIDs and therefore any information from those tables that you need and any other tables therefore that have a relationship to Jobsite, Customer or Branches.

At a basic level, I think This Article illustrates the concepts of JOINS pretty well.


As much if a customer has multiple branches then that customer will have multiple records in the customer table, each assigned to its own branch.

Noooooo!

If a customer can have multiple Branches then you now have a many to many relationship. You don't want two "Fred Bloggs" with different BranchIDs in your customer table. How do you know whether you're referring to two different people who happen to have the same name or the same guy using two different branches?

If this is valid state, you want a Junction Table Between Customers and Branches with a unique index on CustomerID AND BranchID (ie the same customer/branch combination cannot appear multiple times).

Now "Fred Bloggs" still only appears once in your customer table, but he can utilise multiple different branches. Instead of linking to the customer ID in your jobsite table it may be prudent to link to your Customer/Branch junction table instead, that way you know Fred Blogs, using the Doncaster branch, used that jobsite, not the same Fred Bloggs going to the Bristol Branch.

While they may be plugging a gap at the moment I'd really try and disuade you from using global variables, they will bite you on the backside eventually. The data is in your database and it is much, much better at managing data that you might want to use again than Global Variables are.
 
perhaps it will help to think of a JOIN as essentially saying "AND get me stuff from this table as well" (there's more to it than that, but let's just start at the beginning).

So


Code:
SELECT mt.column, mt.column, mt.column, AND tt.Column FROM TheMainTable AS mtAND INNER JOIN ThisTable as tt ON ....

this is def better. As with a lot of things it just takes some understaning. I like to be able to visualize what im coding and with Join SQL i have never been able to lol. Ive attached a screenshot of my relationships for you to look at if you have a spare moment. I have added 2 tables plainly named atm for the connectors for the many-to-many relationships. One will be branch-customer and one will be customer-jobsite(as one jobsite may have many customers also. How should i connect the many-to-many? should it be BranchID to CustomerLinkID and then BranchLinkId To CustomerID?
 

Attachments

  • relationships.jpg
    relationships.jpg
    97 KB · Views: 174
defining a relationship is more of an administrative nicety. your dbs will work perfectly well if the table structure is correct without any relationships

let's say you have a customers table, and an orders table

since each customer can have many orders, then you have this relationship

customers 1 ....... many orders

now if you set this up as a predetermined relation, then whenever you put these two tables in a query (say) access will automatically connect them according to the pre-set relationship - but you can do this manually

now also, since in this system, you would not want orphan orders (an order with no related customer) having the relationship pre-defined would halp because it

a) would not let you create an order for a no-existing customer and
b) would not let you delete a customer while orders are present

but again, you could check for either of these situations manually, in code, anyway.

Defining the relation does not actually affect your database design, or functionality.
 
thats exactly what i did before. i just always had to remember that if i deleted a customer that i would also have to manually delete jobs etc etc on down the line. I have it working fairly well with relationships however it doesnt cut down on my code as much as i thought it would. it def does cut it down some just not significantly.

Thanks for the replies
 
In some respects it won't significantly cut down on (superfluous) code but without a relationship, you HAVE to check whether related records exist in other tables. With a formal relationship, enforcing Referential Integrity, you can be safe in the knowledge that the database won't let you accidentally delete a customer who has orders (to continue the example) unless you've deleted the orders first.

What you get therefore, rather than necessarily shortcuts, is an assurance that the integrity of your data will be correctly maintained and the chance of accidentally buggering it up, to use a technical term, is greatly reduced.

Dave's right that you can design and implement a database without using formal relationships, but because the database doesn't "know" that Orders relate to Customers and that an Order MUST belong to a valid customer its ability stop junk getting into the tables is greatly reduced.
 

Users who are viewing this thread

Back
Top Bottom