Access One-to-many Relationship

Question

I am an EW3 newbie, trying to create a site that will bring an Acces Database to a web page.  My question is rather complicated, so I will try to provide as much detail as possible:

1.  User enters a customer's drivers license number into a textbox to search a customer table.  The DL number is the index.

2.  The results page displays the correct customer record, but I would also like the page to display all of the vehicles the customer has checked out which are stored in another table in the same database.  The two tables have a one-to-many relationship with the DL number as the link.

I was able to accomplish this by creating two GridViews on the results page by using the search form control as the WHERE clause for both separately.  However, when enabling editing on the vehicles gridview, a blank page would be displayed instead of changing the selected record to textboxes for editing.  I am not sure why this is happening, but I assumed it was because of the way I was searching and displaying the data in the results page.  Does that make sense?

TIA!

Answer 1

1.  User enters a customer's drivers license number into a textbox to search a customer table.  The DL number is the index.

I hope your working under an SSL cert for this and that you have taken all the required precautions when taking sensitive data from a user.  Access database would need to be secured or placed outside of the root web folder - I would switch databases to something like MySQL where you can control user access.  If not you will be open to some extremely heavy fines if there is a security breach.

2.  The results page displays the correct customer record, but I would also like the page to display all of the vehicles the customer has checked out which are stored in another table in the same database.  The two tables have a one-to-many relationship with the DL number as the link.

Sounds like a simple LEFT JOIN would work for you. 

Something like:
SELECT CustomerTable.CustomerID, CustomerName, VehicleName
FROM CustomerTable
LEFT JOIN VehicleTable ON CustomerTable.CustomerID = VehicleTable.CustomerID

Using a LEFT JOIN ensures that customers are returned who have no vehicles checked out.  If you want only customers with vehicles checked out then change the LEFT JOIN to a JOIN.

If the Vehicle table contains all of the information your looking to return then you won't need a JOIN and could just use a SELECT with a WHERE statement or JOIN in the other direction.  It really comes down to what the result set is used for and how it will be displayed.

In your situation its important to have an understanding of normalizing a database.  If done correctly the SQL statements, joins, become rather easy.

---

--
Chris Hanscom - Microsoft MVP
On Facebook | On Twitter | Resource Center | Veign's Blog | Web Development Help

Get a Complete Website Analysis by Veign

Answer 2

If you are working with a one-table Access database, then when you configure your Access Data Source, you can click on the Advanced button and select the option to generate Insert, Update, and Delete statements, in which case the Insert Query, Delete Query, and Update Queries are all generated automatically for you.

When you have  a relational database with more than one table, Expression does not automatically generated those queries for you , and they are needed in order to edit and update the database.

Now -- I hesitate to refer you to this tutorial since it is quite a bit different from your situation -- it uses a SQL Express relational database and different field names. But it does show how to set up the database and what the queries need to look like.

In your situation, Expression automatically generates only the Select Query for your Access database. YOu can compare that to the Select Query of the SQLExpress database of the tutorial -- they are similar enough so that you can use that comparison and, using the same analogies, understand how to write the Insert, Delete, and Update Queries for your case.

Also, please note that the VIEW talked about in the Tutorial corresponds to a Query in Access, and before you import your Access database into Expression, you want to have made a Query that brings up the proper fields using the Query Builder of Access. When you configure your data source in Expression, use that Query as the data source -- not an individual table.

OK -- you have some homework in front of you. The tutorial is at:

http://www.homepagedoctor.com/expressiontutorials/MultiTableRelationalDBSetup.htm

---

ClarkNK, A.K.A. HomePage Doctor
HomePageDoctor.com -- Expression Web database tutorials
Ownertrades.com -- Created with FP, Access, Bots and Wizards
MyNumbersTracker.com -- Created with Expression, VWDExress, SQL Express, and ASP.NET.

Answer 3

Well, I feel like a complete idiot.  Maybe I have bitten off more than I can chew here.

I just tried the tutorial and I still get a blank page when I attempt to update a record.  It's probably something simple, but when I have to start installing additional web developer applications because EW3 "Isn't enough", my frustration level begins to rise.

At several hours of messing with this, I have so much SQL ____ going through my head I am having trouble focusing.

Like I said.  I just want to be able to do a search on a customer table by drivers license number, return the customer information, as well as any vehicles the customer has had loaned out to him.  The vehicle information is pulled from a different table with a relationship to the customer table by the drivers license number.  Lastly, I would like to uodate the vehicle record that he has returned.

In the future, I would like to include an insert page if the customer DL number is not found, and the ability to print the newly entered record to an agreement for customer signature.  Having the ability to search the database by plate number and date is must, but shouldn't be a problem.

I wonder if I should just do what I originally planned and build this whole thing in Access 2007 and deploy the application using Access Runtime...

Answer 4

On the Web? I thought that this was meant to be a Web application. Does the runtime have the hooks to be able to operate interactively via http requests? And if so, is there a hosting provider willing to let it be installed? If it works, I wonder why we haven't heard about it here before. Mind clarifying a bit? Thanks.

cheers,

scott

---

Plural's don't have apostrophe's. It seem's sometime's that any word's ending in "s" get a gratuitous apostrophe. Apostrophe's are used to indicate possessive's and elision's (contraction's or abbreviation's).

Answer 5

Are you trying to do this using Access 2007?  Because I don't think that is supported by Expression (I'm still back at Expression1 so things may have changed).

If you are using Access 2007 -- try Save As and Access 2000 or 2003 database and see if that helps.

---

ClarkNK, A.K.A. HomePage Doctor
HomePageDoctor.com -- Expression Web database tutorials
Ownertrades.com -- Created with FP, Access, Bots and Wizards
MyNumbersTracker.com -- Created with Expression, VWDExress, SQL Express, and ASP.NET.

Answer 6

I was planning on deploying this via our Intranet - not the Internet.

Using Access 2007 and Access Runtime would be an appropriate alterantive if I struggled with EW as the database would be located on a shared drive.  I just thought that a web deployed application would be faster and easier to maintain and update...