More Relathionship Questions

[82280zx]

I finally got my database mostly working, took me a minute to figure out relationships I was just over complicating them, anyways can you join once table with two other tables? I want a one to many from my table Asset to Network and to Power? Is that possible? I think I can do it with a many to many but as for seeing the data linked together I would have to do a query right?

[JoeM]

Yes, you would join your tables in a query and select the fields you wish to see. Queries are one of the most important tools in Access, and you will use them a lot.
Queries can be used as the Source of Forms, Reports, and Exports, just as same as Tables are.

[orange]

It would be easier for readers to answer your question if you provided more description of WHAT you were trying to do in plain English. We know nothing of your set up, your business, your experience..... So just tell us a bit in real simple terms. Once readers understand the issue/opportunity someone will provide more focused response.

I suggest you work through this tutorial.

Good luck

[82280zx]

I'll do my best to simplify this, I have a table called assets, the asset can have multiple power supplies and multiple ip address's. I have a table that is called Assets, a Table that is called Power containing power information and a table called Network that contains network information. I want to link my assets table to both of the tables. Does that make sense? Sorry I'm new here I'm still learning the ropes. Right now I can relate one of the tables using a One to many relationship, but I can't relate both, when I hit the + by assets it will show the one I link but if I try to link both it pops up a window?

[orange]

An Asset can contain 1 or more PowerSupply
An Asset can be assigned 1 or Many IP address
An Asset can be attached to1 or Many Networks
A Network can contain 1 or many Assets

Are these statements true?
Adjust as necessary.

Good luck

[82280zx]

An Asset can contain 1 or more PowerSupply
An Asset can be assigned 1 or Many IP address
An Asset can be attached to1 or Many Networks
A Network can contain 1 or many Assets

Are these statements true?
Adjust as necessary.

Good luck

An Asset can contain 1 or more Power Supplies
An Asset can contain 1 or Many Networks

These two are true.

[ssanfu]

Hey, 82280zx. Glad to see that you are back with more questions.

The "AssetID" (PK) should link to the tables Network and to Power? Tables Network and to Power each would have a field (long integer) that is the foreign key to the Assets table.

This is how I would name the fields

the one-----------------------------the many
Assets.AssetID_PK ----------> Networks.AssetID_FK
Assets.AssetID_PK ----------> Power.AssetID_FK

In my databases, Asset.AssetID_PK would be an autonumber and Networks.AssetID_FK and Power.AssetID_FK would be long integers.

How do you feel about posting your new database for review??

[82280zx]

Hey, 82280zx. Glad to see that you are back with more questions.

The "AssetID" (PK) should link to the tables Network and to Power? Tables Network and to Power each would have a field (long integer) that is the foreign key to the Assets table.

This is how I would name the fields

the one-----------------------------the many
Assets.AssetID_PK ----------> Networks.AssetID_FK
Assets.AssetID_PK ----------> Power.AssetID_FK

In my databases, Asset.AssetID_PK would be an autonumber and Networks.AssetID_FK and Power.AssetID_FK would be long integers.

How do you feel about posting your new database for review??

Hi there and thanks for having me here! And for helping me! I'm hoping to help people in the future once I get the hang of this. I actually did what you explained below

the one-----------------------------the many
Assets.AssetID_PK ----------> Networks.AssetID_FK
Assets.AssetID_PK ----------> Power.AssetID_FK

but if you click the + by Assets in the Asset table you get a pop up that asks for the table? I don't think it's working right. I have the relationship severed with power right now and only have assets attached to my Network table at the moment. I will attach what I have thanks. I have to use my box again because my db file won't fit in the forum upload. Here's what I have so far. https://app.box.com/s/o7bhjou423ojnzg96gw0

[82280zx]

Btw I'm still working on the naming standard... Sorry about that, I'll clean it up when my mind settles down on some other things I'm working on, I know it's good practice and helps keep it together better.

[ssanfu]

I'm still working on the naming standard... Sorry about that, I'll clean it up when my mind settles down

The sooner, the better. The longer you wait, the more you'll have to change.

Remember, no spaces in object names; fields, tables, queries, reports or modules.

I never use lookup fields (different from look up tables). Lookup fields are not compatible with SQL Server.
Lookup fields can be useful - IF you use datasheet views a lot.Very rarely have I used datasheet view. I use continuous form view and "create my own" datasheet views. If I need a combo box, I have to configure a combo box control. But I have more control. This was I can convert the BE to SQL Server Express if.when I reach the 2 GB file limit in Access. I don't have to redesign the database.

Be aware that attachment fields (especially with pictures) will cause dB bloat. You will reach the 2 GB file limit a lot sooner than necessary.

Also, I never use calculated fields. Here I mean in tables Customers and Employees. In general, you should never store the results of a calculation. (There are exceptions, but this is not one.)

Attached is your database with the spaces removed....

[orange]

8280zx

Looks like you have things well underway, and Steve has provided some naming approach. Good work.
I am recommending you work through the tutorial I suggested in post #3. Also, pardon my preaching here, but when you have a business with a number of entities
it is helpful to readers (an invaluable to you the designer/developer) to build a scenario about your business, its entities and how things fit together generally.
You will see a business scenario in the tutorial, and I recommend you build a similar one for your situation.

This will help you identify the FACTS/Business Rules with which you can test your data model. You take some historic or fictional test data (good and bad) and then test your model. We used to call that "stump the model". The idea was to take scenarios and then to work those scenarios with their data and run them against the model. Whenever something doesn't work, you must reconcile why it doesn't work --either your data scenario is incorrect or the model is incorrect --reconcile every such incident. Once your model supports your business rules, then build your data base.

The model will show managers, maintenance, developers, data base personnel how the business uses its data. It cn be a blueprint for future activities, adjustments etc.

Good luck, you're off to a great start.

[82280zx]

Here is another question for you all, I see how ssanfu linked my tables together, I tried that earlier but had the same results. My Question is in Datasheet View in access how can I add both Power and Network records to an asset? And Orange can you provide me a link to the tutorials you was referring to? I went and looked around but I found a tutorial on comments? Was that the one you was referring to? Thank you guys.

Let me give a little bit more details on this.

Asset Table--------------------Network Table
AssetIDPK------------------> AssetIDFK

Asset Table-------------------Power Table
AssetIDPK------------------> AssetIDFK

In Datasheet view I click the + to expand to the other datasheets it pops up a Insert Subdatasheet window.

[ssanfu]

Again, I never use datasheet view. While it does (might) have its uses, I prefer to use continuous forms view and make it look like a datasheet.

My Question is in Datasheet View in access how can I add both Power and Network records to an asset?

Have 2 subforms on a form. The main form would select the Asset, one subform would be for Network and the other would be for Power. The forms/subforms should have queries as the record sources. (I always use queries as record sources, never tables).


In addition to the tutorial Orange provided, there are more tutorials at Rogers Access Library site:
http://www.rogersaccesslibrary.com/f...s_forum46.html

[orange]

The tutorial I recommended is http://www.rogersaccesslibrary.com/T...lationship.zip

The tutorial is divided into a number of steps and files. You have to work through it.
Good luck.