Show Multiple Subdatasheet in One Table

[ray143]

I an new to MS Access.

I have 3 Tables namely TableSimCards, TableTablets and TableCellPhones. My main table is the TableSimCards where I used the field of Sim Card ID as a primary key. The other 2 tables which is TableTablets and TableCellPhones also contains a field for Sim Card ID and I also used it as primary key for both tables. I did not use Foreign keys because I want a one to one relationship.

So the primary key on TableSimCards is linked to the same primary key to the TableTablets. As well, the primary key on TableSimCards is also linked to the same primary key to the TableCellPhones. In other words, the TableSimCards is linked to both TableTablets and TableCellPhones. There is no link between the TableTablets and TableCellPhones.

After linking the tables, when I am on the Datasheet view of TableSimCards, I can only view one subdatasheet at a time. I want to be able to view both subdatasheet of the TableTablets and TableCellPhones, but the Insert Subdatasheet pop up window is only allowing me to choose only one subdatasheet at a time.



So if I choose the TableTablets subdatasheet, that is the only one that shows up. And for me to see the TableCellPhones subdatasheet, I have to remove the TableTablets subdatasheet and choose TableCellPhone subdatasheet.

Is this a Microsoft Access limitation? That you can only show one subdatasheet at a time? I wanted to create a Form (1 form only) where both subdatasheets will show instead of creating two separate forms.

Thanks and would appreciate any help or response.

[June7]

Can't have what you want with datasheet mode.

A table can be a subform container SourceObject but you will have greater control if you use a form.

[ray143]

Thanks for the prompt reply. So I guess this answers my question, that it is a limitation of MS Access to view 2 subdatasheets in Datasheet View.

That was my reason for getting both subdatasheets in the table, because I want to create a just 1 form where I can see 2 subdatasheets on the form. Did you say that I can create just one form for the TableSimCards with both subdatasheets of the TableTablets and TableCellPhone in that one form? Would appreciate knowing how? I tried but I have to create 2 forms to achieve that.

[ArviLaanemets]

MS Access is a tool mostly meant for group of users to work with certain set of data. This means, there will be someone (mostly a single person) who designs the app (database and a tool to work with it), and a group of persons who will use it. The database may be separate part of it (an Access Back-end, SQL Server DB, etc.), or it may be integrated into app.

Users working directly with tables of database is a security hazard, and a risk of data corruption - because tools to control such things are limited at table level. Additionally, when several users work with app with integrated database, there will be high risk the database will be corrupted. So whenever there will be several users, the app must be split to front-and back-end, with every user having his/her own front-end.

The logical conclusion is, users will work with data only through forms (in front-end). All tables meant for use by several users must be placed into back-end. Regardless the app being designed with tables integrated or in back-end, it is a good practice to hide all tables from users.

[June7]

As I said, more control with forms.

However, could have a main form with 2 subform containers with tables (or queries) as SourceObject. Organize the containers on pages of a Tab control.

Why the aversion to building forms?

[orange]

Ray,
I'm not understanding your set up.
How about describing in plain, simple English what you are trying to automate -no database jargon?

What exactly is/are the business rule(s) involved?
There is no link between the TableTablets and TableCellPhones. Yet "...Sim Card ID and I also used it as primary key for both tables.."

[ray143]

Good to hear from a moderator. So I will explain in in plain English and make the fields and table names as simple as possible as per your request:

I have 3 tables which are tableA, tableB and tableC. My main table will be tableA which I have assigned a field for example called "customer ID" as the primary key. The other two tables, which are tableB and tableC will also have the same exact field called "customer ID" and have assigned it as well as primary keys for both tableB and tableC.

I established a relation between the 3 tables. So tableA has a link to tableB using their primary keys which is "customer ID". I want a one to one relationship. As well I also established a relation between tableA and tableB to use the same exact primary key - again, the "customer ID on a one to one relationship. In other words, tableA is linked to both tableB and tableC using the same primary keys which is present in all the 3 tables. There is no link between tableB and tableC.

The link is successful, but what I am trying to find out if that if I use the Datasheet view on tableA, I can only see one subdatasheet at a time by clicking on the plus sign to the left of every record on tableA, and it pops up the Insert Subdatasheet where I can choose which table I would like to see a subdatasheet (whether the subdatasheet for tableB or tableC). Unfortunately, it does not allow me to select both tables and either I can only choose tableB and it will show the subdatasheets for tableB on the record. And if I want to see the subdatasheet for tableC, I have to remove tableB subdatasheet in order to select tableC subdatasheet. I am not able to see both subdatasheets at the same time - only one at a time. So I was wondering if this is a limitation for MS Access.

The reason I wanted to see both subdatasheets for tableB and tableC is because I want to create a Form using tableA, where both subdatasheets for tableB and tableC will allow addition or editing of record entries for other users. Without being able to see both subdatasheets, I have to create 2 forms - one for tableA with subdatasheet of tableB and a second one for tableA with subdatasheet of tableC. I was hoping I can create a form for tableA with both tableB and tableC subdatasheets so that I only need to create 1 form.

I want to minimize creating too many forms because in actuality, I will be creating over 10 tables with hundreds of records per table. Having much less form in my opinion is better for our users when they do their form entries than having over 10 forms to fill up.

Thanks and I hope I explained it as plain english as possible.

[ray143]

Thanks again for the reply. I just want to be clear that I do not have anything against building forms. I only gave a simple example of using 3 tables. In reality, I am building an inventory database with over 10 tables with hundreds of records per table. I chose the tableSimCards as my main table because all of our devices in the inventory has SIM cards (cell phones, tablets, GPS trackers, etc). So each table will have a field for SIM cards which will be the primary key for every table. And my intention is to have this tableSimCards linked to each of the other devices tables using the same primary key (Sim Card ID) - all one to one relationship.

My tableSimCards has about 500 records where the first 50 records are the Sim cards for cell phones, then the next 50 records are the Sim cards for tablets and it goes on and on.

What I was hoping for is to minimize too many forms to create. I probably have not explored at lot yet on creating forms. All I was able to do is after linking the 3 tables, I tried to create a form using the tableSimCards and because I can only see one subdatasheet, I realized that I have to create two forms which will be the tableSimCards form with the the subdatasheet of tableTablets. And then create a second form of the tableSimCards with the subdatasheet of tableCellPhones.

So if I will have 10 tables, that means I have to create 10 forms as well. We have users in our organization that are kinda picky when it comes to filling up forms and if they have too many forms to choose to fill up for every device they want to add or edit, it kinda confuses them and rather than filling up the forms, they will instead skip it and now, the inventory is not updated. Or they could also make a mistake of filling up the wrong form - for example, they are supposed to enter a new entry on cell phone and they instead used the form for tablets to fill it up. Since I am new, I wanted to make sure that I am aware of the limitations of what Access can do.

[ray143]

Thanks as well for the reply. I am aware of that and that was really my intent - I design the database to reside in the backend. And I will have users in the front end which will have restricted access and not be able to access the tables. Users in the front end will only access forms, queries and reports.

Though I am confused as your reply has nothing to do with the question that I asked on my post. Good advise though.

[June7]

Number of forms is irrelevant to the user. User shouldn't even know or care what the db structure is. They shouldn't even see the Navigation Pane.

Build the forms then manage them to do what you want. A Navigation Form is an easy way to give the appearance of a single subform because only one subform displays at a time. Subforms are loaded/unloaded when tab is clicked. This behavior can be emulated on a normal form/subform arrangement with VBA.

[Minty]

It sounds like you were planning on having a different table for each piece of equipment.
I don't think this is a good design unless there are no common fields between those items.

If you add the 11th type of equipment you will have to add another table and forms, queries etc to cope with that.
That isn't how a relational database should work.

Your equipment table should have an equipment type, and that in itself should be stored in an EquipmentTypes table.
When the next clever bit of tech arrives, you simply add a record to that table and you can add those new bits of tech to your existing equipment table.

EDIT: To answer you question about the sub datasheets, by using the correct data structure you would only need one sub Form to enter the data for any type of equipment.
A subdatasheet on a table or datsheet view is a nice feature but not very helpful in most circumstances.

[orange]

My post/response was to find out what the business issue is that you are attempting to solve/support with an Access database.
From experience, I can assure you that many people in various forums, new to Access, have somehow been "marketed by M$oft" to think a database will be magically created for them with the purchase of Access.
Designing a database is much like designing a house. You start with the who, what, where, when, how much and how often sort of questions to identify scope and priority given a budget. Of course you have to be aware of local policies, sewers, electrical, set backs, building code, utilities.....

I don't know your experience with database or Access. So I'm trying to offer advice to help with the business context and your question.

As I understand your post (please edit/correct as necessary)

-you have a number of devices (tablets, cell phones...) (possibly laptops, desktops, servers, printers....)
-some devices have SimCards
-devices are assigned to users
-you need to record details of devices, and identify the Simcard involved for those that have Simcards
-there is a need to maintain the devices and Simcards and the respective user(s)

From an Access perspective, all user interaction with the database should be via Forms. Users never get direct access to the tables.

Do you have a list of the things the users should be able to do when they interact with/use the database? Add, modify, delete?

I think the first thing to review is Normalization which is key to database design.
I would also recommend that you work through 1 or 2 of the tutorials from RogersAccessLibrary(below) to experience the database design process. Each will take 45-60 minutes, and you will experience a process that can be used with any database.
XYZ Labs and/or
Class Info System

[Gicu]

I totally with Minty's post, I think you should only have one table holding all equipment along with a qualifier field EquipmentType. But if there is not a lot of overlap among the various types and you still want to have them in separate tables please review the attached sample that shows you how easy it is to do that in one form.

Cheers,
Vlad

[ray143]

I think that is what I already said earlier, that users will have restricted access and they will never have access to the tables..

[ray143]

Thanks for the reply. Exactly as you said, I have a table for each equipment. The reason I did that is because all inventories came from different worksheets on Excel. One worksheet per device. Since there are hundreds of records, and manual entries of records will take a very long time, I imported the excel worksheets into Access, creating a separate table per worksheet.

Your proposal about using one table for all equipment is the perfect design if the devices will be using the same fields. But it will not be in my case.

Each of my devices have different required fields, which will not be applicable to another device. For example, cell phones will not use the same exact fields as a GPS tracker or vice versa. A cell phone and GPS tracker may both have a Sim Card and cell phone number as a common field. But a GPS tracker have other information like 5 or 6 iridium satellite information that a cell phone will never have. A cell phone will also have fields that will not be present in a GPS tracker. Hence, the separate table, as well as separate worksheet when Excel was first initially used. If I am going to combine all devices into just one table, I will have a very long list of fields that will span maybe 40 fields. Which means a record of a cell phone entry will have fields that are only used by GPS tracker record and will be left blank instead because it is not applicable. So imagine I have 10 different types of devices with 6 fields each not related to the other devices. My table would be so long horizontally that I have to scroll to go through the 40+ fields to see them. And there will be so many blank records per fields because different devices does not share the same fields.

But because all of the devices or tables share one common field (Sim Card ID and cell phone number), I thought that using SIM Card ID as primary key is good enough to link the different tables. All of my devices have Sim Cards, so I chose this as the primary key for all tables.

As mentioned in one of my post, I will try your suggestion about using one sub Form to enter the data for any type of equipment - by using the correct data structure. I have not done too much work on forms yet so that is gonna be my next step and see if it is easy to do.