Solved: Database design

[JustJerry]

Well, this newbie is back again, and what I thought would work, won't. I swear I'm trying to learn!!

Here is my scenerio:

Form1 opens and stays open, other forms that open from here are pop ups.
On Form1, there are options for the user to check. Each time they check an option, a related form will open to fill in required data. Each form has it's own table.

For simplicity:
Table1 has fields: SiteID, SiteName, Customer, Manager
Table2 has fields: SiteID, SiteName, Customer, TowerType
Table3 has fields: SiteID, SiteName, Customer, Foundation

Form1 (sourced from Table1), selecting OptionA --> opens Form2 (sourced from Table2). When finished with Form2, it closes back to Form1

Form1, selecting OptionB --> opens Form3 (sourced from Table3)

Each form is set to OnOpen to go to a new record

On Forms 2 and 3, I use the following code for the following fields; SiteID, SiteName, Customer

[VBA]Me!SiteID = [Forms]![Form1]![SiteID][/VBA]

I use the same code for the other two fields.

However, on Form3 I am trying to access something stored in Table2 and store that value in a textbox on Form3 just for informational purpose only.

I am trying to look up the value for [TowerType] stored in Table2, and base it on finding the value of the [SiteID] on the open Form3 and related to the data row in Table2 that has the same [SiteID], then have that value placed in TextBoxC on Form3.

On Form3's recordsource, I tried using the query builder to add the field to the form, but it's always blank on a new record.

I keep searching Access books to learn how to do this, but am clueless.

Any help is greatly appreciated, and I DO hope I again explained myself well enough.

[Norie]

Jerry

My first thoughts on this is why do you have 3 tables with basically the same data.

[JustJerry]

Well, I was trying to keep certain data seperated into different tables.

The identical fields in each table, in my mind, was for me to be able to access the data in each table related to those three fields.

We may have the same customer, the same sitename, but a different job with a different SiteID.

This is my second attempt at doing a database project, and therefore I apologize for my ignorance in the coding, but I am trying to learn this on the go, and I will make many many mistakes I am sure.

[xCav8r]

I think that Norie was right to question the table structure, but if this is something that's going to remain small, it probably won't make much of a difference. On the other hand, if this is something that you see growing, you might want to reconsider the way that you've structured the tables.

I would suggest that you create a copy of the database to share here on the forum, so you can get the best help possible. The tables in the copy only need to contain representative data (meaning enough for us to get an idea of what you're wanting to capture), and of course, if anything in it is confidential, you'll want to replace that with innocuous sample data.

[JustJerry]

Ok, here is a copy of the database in progress. It said the zip file was too large to upload, so here is a link that you should be able to access the zipped file.

http://home.earthlink.net/~gm.miller/Job Data.zip

I was looking at the Northwind sample database, and even there it shows tables sharing the same fields.

You'll notice that on the TwrFndInstall form, there is a textbox there that I'm seeing if I can insert the data from the Towertbl that is based on the same SiteID that is to be displayed on the TwrFndInstall form. The Recordsource code is wrong, I just put something in there to send for now.

The TwrFndInstall Subform is something that I just added yesterday and am trying to make work too, just fyi.

One more thing, for a person like me 'trying' to learn VBA code, what is a good book to get that explains well enough to learn. We have some access books here, but they are written more for those who 'know' more than I do.

Thanks for the help.

Jerry

[Norie]

I was looking at the Northwind sample database, and even there it shows tables sharing the same fields.

What fields in which tables?

[JustJerry]

If you are referring to the Northwind database, for example:

OrderID is listed in the OrderDetail table as well as the Order Table
ProductID is listed in the Products table as well as the OrderDetails table

To name two of them, and there are a few others as well

[Norie]

Jerry

In the Order Details table OrderID and ProductID are foreign keys.

They refer to the primary keys in the Orders and Products tables respectively and are use to represent the many-to-many relationship between those 2 tables.

[JustJerry]

Hey Norie,

I was wondering what 'foreign' keys were yesterday when I saw that somewhere, and I was picking up on it today as I wasmessing around with the tables. Haven't quite figured everything out, but at least it's a start

Thanks for the reply

[Norie]

Jerry

A foreign key in a table is normally a field that defines the relationship between 2 tables.

In one table it is normally the primary key and is used in the second table to describe/indicate the relationship between the 2 tables.

Of the top of my head I can't think of any links that will explain this concept further, but it is a 'standard' method of linking tables and data.

Have you looked (in the NorthWind db) at Tools>Relationships...?

By the way I've downloaded your attachment and will have a look at it when I have a chance.

[xCav8r]

You might find this helpful...

http://support.microsoft.com/?scid=kb;EN-US;209534
http://www.microsoft-accesssolutions.co.uk/tables.htm
http://www.15seconds.com/issue/020522.htm

I'll look for some more Access specific links. There are, however, lots of sites that deal with modeling data in general. If I can't find any more on Access, I'll post some of those.

[JustJerry]

Thank you Norie and xCav8r. I am now attempting to 'normalize' my tables realationship wise, and am taking a different approach to what I am trying to create.

[xCav8r]

Jerry, how's it going? Need help?

[mdmackillop]

Question retitled to make it "searchable"

[JustJerry]

Hey xCav8r,

It's going ok, thanks. I bought a couple of books over the 4th holiday weekend, and got a whole new look on things. I started over completely, and am slowly making my way to completion.

I am glad you pointed out about table relationships. Once I got that all straightened out, things started making sense.

Thanks again,

Jerry

[xCav8r]

Keep us up-to-date, Jerry. We're here to help.

[geekgirlau]

While you're reading, see if you can get hold of an excellent book entitled "Database Design for Mere Mortals" (name of the author escapes me at the moment). It's not specific to any particular software, and gives a great explanation of database structure without assuming that you have any formal experience in database design.

[xCav8r]

http://www.amazon.com/exec/obidos/tg...books&n=507846