Show Multiple Subdatasheet in One Table

[June7]

Then why do you say "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."? As I said, how many forms are built to meet requirements is irrelevant to users. The only one who benefits from not building forms is you (the developer) because you do less work.

[ray143]

Thank you Vlad for the attachment. I will review it once I get back to work tomorrow. As per my reply to Minty:

"Minty's 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. 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."

[ray143]

Thank Orange.

As mentioned in my first post, I am a newbie at Access and have learned it by reading tutorials and watching Youtube videos in the last 2 months or so.

But I am a techie person, in fact, I am a network engineer who designs network using Cisco Routers and Switches for the last 25 years. I have all the professional Cisco certifications to add to my skills as well a few Microsoft certifications. I just do not have experience working with Access. And it took me years to learn my skills and have to keep on learning new network technology.

So I am not that person who is marketed by Microsoft to think that a database will "magically" create by itself. It took me many, many, many hours watching Youtube videos like Access Learning Zone by Richard Rost, Access 2016 by Kirt Kershaw, GFC Learn Free, 2016 Access Tutorial by Sali Kacelit. And I spent hour and hours building my database. It is just that I never found in the video tutorials something similar to what I am building as most tutorials uses 2 tables example which is easy. Have seen 3 tables examples but different setup from what I am trying to build.

Though it is not part of my job to work on database, our work group's inventory system is a mess. All our inventory is done using Excel. So I volunteered to create a database system using Access to organize our inventory. I thought that learning database programming is a challenge and a new set of skill to learn.

To answer your questions:

1) I have a number of devices (tablets, cell phones...) (possibly laptops, desktops, servers, printers....)
Correct. I have around 10 different devices - where all devices uses Sim Cards. These devices are cell phones, tablets, different models of GPS trackers that uses cellular and iridium satellite technology, etc - just to name a few. No servers desktops, laptops or printers.

2) Some devices have SimCards
All devices uses Sim Cards. I mean all of them. That is the reason I used Sim Card IDs as a primary key to all tables.

3) Devices are assigned to users
Correct. Devices are assigned to different users (or clients) every now and then depending on requirements. Rest of other devices not assigned to users are stored for use when needed by other users. No same user uses the same exact device. One user named John may need a cell phone for a week and then maybe next week, user John will return the cell phone and take a tablet this time for another week.

4) I need to record details of devices, and identify the Simcard involved for those that have Simcards.
Sort of but not only that. Since all devices have Sim Cards, the Sim Card ID and phone number is part of the fields per device. But there are other fields required like IMEI numbers, Serial Numbers, model numbers, Sim Card Status whether cancelled, suspended, active and date they are cancelled or suspended or cancelled, Sim Card Plan. Other fields include location, which client currently has it, and for GPS trackers - information related to iridium satellite, subscriber, etc.

Some fields from other devices is not present in other devices. For example, a GPS trackers will have like 6 fields related to iridium satellite info that will never be in a cell phone or tablet device. Vice versa, a cell phone will have several fields that will never be a field in a GPS tracker. That is the reason I did not go for creating one table for all devices and chose to create one table per device - and link them instead using the only thing that is common to them - the Sim Card ID. Or I can also use the Phone number because every Sim Card has a phone number.

5) There is a need to maintain the devices and Simcards and the respective user(s)
Yes. Because at times, Sim Cards are cancelled or suspended especially if the device where that Sim Card is installed is never being used for some time. It is not worth paying monthly fees on Sim Cards if they are not being used. We have over 600 Sim Cards on over 600 different devices. Some Sim Cards are cancelled, some are suspended, some are active. If a Sim Cards is suspended, it can be reactivated again without having to get a new cell phone number. And this is a process that goes on daily and since the devices are assigned to users at random, that also needs to be tracked in the database.

My intention is to build the database and give restricted access to users only using the forms, conduct queries and make reports. So if a user takes a device with a Sim Card and gives it to a client, I want that user to be able to go to the form and update that information on that device like which client took it, location, date, etc. And when it gets returned, it needs to be recorded again on the database so that it shows that it is again available for other clients.

[orange]

@ray,

I have read through your posts and have identified the following "business rules" that include questions/comments that need further clarification.
NOTE: I appreciate your being a network engineer --I am not and have little detailed knowledge of same- so some of my question/lack of understanding may appear as naivety. But we're trying to get the requirements identified clearly before jumping into physical database.

Business Rules - Ray Simcards

-there are many deviceTypes(cell phones, tablets, different models of GPS trackers that uses cellular and iridium satellite technology, )
-there are many devices of each DeviceType
-all devices have/use SimCards
-some devices are assigned to users (is there an active loan/use period for each?)
-some devices are put into storage (for use when needed)
-device assignments to users vary (No same user uses the same exact device. One user named John may need a cell phone for a week and then maybe next week, user John will return the cell phone and take a tablet this time for another week.)
-the Sim Card ID and phone number are attributes of every device
### need to identify where each of these belong --details
IMEI numbers
, Serial Numbers
, model numbers
, Sim Card Status whether cancelled, suspended, active, reactivated and date they are cancelled or suspended or activated/reactivated
, Sim Card Plan (probable hidden entity with hidden attributes?)
Other fields include location, which client currently has it, and for GPS trackers - information related to iridium satellite, subscriber, etc.
a cell phone will have several fields that will never be a field in a GPS tracker
###

-a GPS tracker will have approx unique 6 fields related to iridium satellite info (not relevant to cell phones, tablets etc)
-a GPS tracker will also have subscriber info
-every Sim Card has a phone number(always paired or can change?)
-a Simcard may undergo timestamped transactions (cancelled, suspended, active, reactivate)
-+An IMEI stands for International Mobile Equipment Identity. Think of it as your phone's fingerprint — it's a 15-digit number unique to each device. Phone carriers and manufacturers share IMEI numbers to enable tracking of smartphones that may be stolen or compromised. ( I looked this up)
-If a Sim Card is suspended, it can be reactivated again without having to get a new cell phone number.(always or conditionally?)
-devices are assigned to users at random

For consideration/database concepts:

A Table represents a Person, Thing or Event about which you want/need to record information.
Every table has a Primary Key which uniquely identifies every record in that table.


This high level data model from Barry Williams' site may give you some insight in how tables relate. It is intended as a starting point to help guide design. Your requirements are more detailed and focused, but the general subjects shown and their relationship to one another apply. These are the business facts for that model.

[Gicu]

Hi Ray,
Yes, I understand that sometimes you need to split them in multiple tables, that's why I created the sample to illustrate how to program the (sub)forms; you will still need to create one (sub)form for each type of equipment, but the users interact seamlessly with one form with a dynamic subform.
Here is an updated sample to help with enforcing the one-to-one relationship.
Cheers,
Vlad