Auto fill field in Linked tables

[BPS]

I'm trying to figure out the following in order to get around the 255 field
limitation per table:

Tableetails
Table:Apps
When a record is created in "Details" I would like to have a corresponding
and linked record created in "Apps" and have "Details:ID" (the automatically
generated Primary Key) copied to "APPSrog_ID"

Is this possible?

Thanks

 

[BruceM]

What is Prog_ID? Is it the Primary Key in tblApps? What is the real-world
relationship between Details and Apps? What do you mean by "corresponding
and linked record"?
In general you create a relationship between a Primary Key (PK) field in
Table1 and a Foreign Key (FK) field in Table2. The FK field is not defined
in table design view, but rather is the field in Table2 to which the PK
field from Table1 is related. With a form based on Table1 and a subform
based on Table2, the FK field is automatically populated with the PK value.
If the PK is 999, all related records in Table2 will have 999 in the FK
field.
I don't quite see what this has to do with the 255 field limitation, but if
you are pushing that limit your table design may need reconsideration.
Copying records from one table to the other is rarely appropriate.

 

[BPS]

"Prog_ID" is just a field, not the PK. What I need is a way to link records
in two (or more) tables. Yes, 255 fields is a lot and I am going to go past
that easily, hence the need for multiple tables. This is basically for an
inventory of servers and farms with installed apps, ip addresses, app owners,
server locations, etc. by project.

 

[BruceM]

You will need something like the following for tables:

tblProjects
ProjectID (PK)
Location, etc. (information specific to the project)

tblFarms (I assume you mean server farms)
FarmID
ProjectID (FK)
Other details specific to the Farm

tblServers
ServerID (PK)
FarmID (FK)
ServerName
ServerLocation
IP_Address
etc.

tblApps
AppID (PK)
ServerID (FK)
AppOwner
AppName

With so few provided details it is difficult to be specific, but it would
probably be something like the above. In general, each table should contain
information about a single real-world identity (I borrowed that phrase from
somebody, and would give credit if I could remember who said it). Another
way to look at it is that a table's purpose should be describable by a
single sentence without using the word "and". Projects and servers and apps
is too varied an assortment of information for a single table (if you are
relating a bunch of tables one-to-one just to get around the 255-field
limitation you are in effect using a single table).

In the outline above, each project is a record in a Projects table; each
server is a record in a Server table, and so forth. I am assuming from what
you have said that apps are being installed on servers. If they are being
installed on machines with desktop OSs then you will need records for those
machines, maybe in their own table, or maybe in a Computers table that
includes servers. Whatever the details, you can have any number of servers
in a farm, and any number of apps on a computer, without needing to redesign
the database. Another app is just another record in the apps table.

The layout you have suggested is appropriate to a spreadsheet, not to a
relational database. Access can be a powerful and useful tool for what you
need to do, but not if you try to make it behave as a spreadsheet. You could
look up one-to-one relationships and try to put together a large flat
database that way, but that is a very difficult solution to implement
efficiently and effectively.

 

[BPS]

Yeah, that's exactly the issue. All of this info is in a variety of
spreadsheets - 1 per project - unsearchable in any meaningful way. For
instance, 5 different "Projects" (read that as customers) may use overlapping
groups of servers. Query that returns which customers are on which servers is
impossible, and that's an easy one.

 

[BruceM]

It's a bit unclear what you mean by "overlapping groups of servers", but if
you mean that each server may be used by several customers, and each
customer may use several servers, then you have a many-to-many relationship
that requires another table, often called a junction table.
Once the relationships are designed, the listing of customers per server you
mention is pretty straightforward. Before I or anybody else can offer
specific suggestions it will be necessary to understand how the system works
in terms of customers, servers, apps, and so forth.
A book that has often been mentioned in this forum as a good source for
understanding design principles is "Database Design for Mere Mortals" (or
something like that). A search through the group for "Mere Mortals" should
turn up the exact title and author.

 

[BPS]

Got it - Ordered from Amazon. Thanks for your help.

b