Relationships

[LarryE]

And here's another design that can be just as effective. Instead of each child table holding a single foreign key from the parent table, we have one table that holds all the foreign keys. It's called a Junction table for obvious reasons. Data input can be faster using junction tables because you get to the point of creating input forms, the form for inputting data into the BackupFile table holds all 4 foreign keys so each foreign key would have a combo box for selecting the key and you can quickly move from one to another. I'll attach an example later for you to see.

And appending a 'FK' to the end of foreign key names as June7 suggested is a popular way to identify foreign keys in queries etc. ACCESS does not care, but it might be easier to keep track of foreign keys for you. So, the ComputerID field in the BackupFile table would be renamed ComputerIDFK or ComputerID_FK.

 

[David44Coder]

I have corrected the ID naming error. Whatever design you choose I'll use. Sounds good, thanks.
An example of a query would be useful.

 

[LarryE]

I have corrected the ID naming error. Whatever design you choose I'll use. Sounds good, thanks.
An example of a query would be useful.

Please attach your ACCESS file if you can so I won't have to try to re-input every record for testing. Thanks.
Also, what does the Serial field and Location field represent? Is it the Volume Serial Number and Volume Location?

Please confirm the meaning of Serial and Location

 

[Pat Hartman]

I advise not to use same name for primary and foreign key fields. One way to fix that is to use suffix, like: ComputerID_FK.

It is not wrong to use different names but it isn't necessary. Using the same name is not a problem. Why? No query will need BOTH the PK and the FK. Any query that joins a parent table with a child table only ever selects the FK field. The PK field is never selected. It is not needed. For purposes of documentation, I have no problem with using FK as a suffix though.

 

[David44Coder]

@LarryE There's only empty tables as per the design prior to your Junction design. (I don't think that'll be what you want, but will attach it. No, I'm told it's too large).
I started with a table and fields I thought would be used/needed. It's all changed since then. Serial number is the physical drives serial number (which I thought might be a good way to identify each one). Location held whether it was internal or external. This was early planning - and maybe not the best idea as one computers external drive on LAN may be anotehrs internal. There's some not connected/powered up as offline backup. When time to add files I'd choose which ones to use.

 

[LarryE]

@LarryE There's only empty tables as per the design prior to your Junction design. (I don't think that'll be what you want, but will attach it. No, I'm told it's too large).
I started with a table and fields I thought would be used/needed. It's all changed since then. Serial number is the physical drives serial number (which I thought might be a good way to identify each one). Location held whether it was internal or external. This was early planning - and maybe not the best idea as one computers external drive on LAN may be anotehrs internal. There's some not connected/powered up as offline backup. When time to add files I'd choose which ones to use.

OK. I'm working on some forms and reports and will attach a file for you to learn from and try out for testing soon.

 

[David44Coder]

Thanks Larry appreciate that. Don;t do it for me though ! Although I'd learn either way, all going well.

 

[LarryE]

Thanks Larry appreciate that. Don;t do it for me though ! Although I'd learn either way, all going well.

I tend to help by creating examples, so here is an example of how to implement a very simple backup file inventory tracking project. I only ask that you at least study the relationships and how I used a series of sub-forms for data input. Maybe just use it as a study guide if that's what you want to do.

 

[Pat Hartman]

Serial number is the physical drives serial number (which I thought might be a good way to identify each one).

As you gain more experience, you will have a better understanding of why you should NOT be using "other system's" unique identifers as your PK. When the time comes to change them, you want to have to change one and only one table and not all your FK's everywhere. The primary reason is you have not control over the values. Look at the bad thing that happened a couple of years ago to any application that had stored SSN as a number rather than a string when the SSA issued new "numbers" that included letters. I saw this one coming in my first application that had to store SSN back in the 80's and I made the field text. Then there's things like Manufacturer's Part Number. They all have different formats and they may even duplicate a PN from a different company so you would need MfgID + PartNumber to ensure uniqueness.

 

[David44Coder]

Thanks Larry, Opening it now.
Pat, I was reading the serial number from the drive. It seemed a good way to identify it,as presumably it'd be unique.
But early days to see issues and time to fix.

 

[The_Doc_Man]

Just remember that a drive can possibly "host" multiple logical volumes or partitions. Therefore, if you have "volume" and "drive" in the same table, DriveSerial cannot be a UNIQUE field.

 

[LarryE]

Just remember that a drive can possibly "host" multiple logical volumes or partitions. Therefore, if you have "volume" and "drive" in the same table, DriveSerial cannot be a UNIQUE field.

In the file I gave the OP, the Drive table was a child of the Volume table and it's Serial number was field only and not linked to anything. I always use Autonumbers ONLY as Primary keys. I hope the OP will learn from that. Maybe Volume should have been a child of the Drive table.

 

[LarryE]

In the file I gave the OP, the Drive table was a child of the Volume table and it's Serial number was field only and not linked to anything. I always use Autonumbers ONLY as Primary keys. I hope the OP will learn from that. Maybe Volume should have been a child of the Drive table.

I used the term "Volume" to define the storage environment each computer has access to, such as OneDrive, Internal HD, External Network, external CD drive etc. Volume really should be re-named Environment. Then each Environment has multiple Drives and each drive has multiple folders etc.

 

[David44Coder]

That's a very impressive front end @Larry. You didn't use your 'junction' method in msg 41?
I would next populate the tables then see if I can follow how it all works? I did think there might be more queries and get a idea how they used the tables. This is a bit of of a jump from where I was. Any advice on usage?

 

[LarryE]

That's a very impressive front end @Larry. You didn't use your 'junction' method in msg 41?
I would next populate the tables then see if I can follow how it all works? I did think there might be more queries and get a idea how they used the tables. This is a bit of of a jump from where I was. Any advice on usage?

There is no need for any queries actually. I only made one for the report. I used the original design because it was better for your project.

I used subforms linked together with Master/Child fields. ACCESS automatically inserts foreign keys into the proper tables for you when do that.

Please look carefully at how subforms were used. It's important in understanding how ACCESS works.

There are 2 computers
Each has multiple Volumes which are actually environments which have multiple Drives
Each Drive has multiple Folders
Each Folder has multiple backup files

The BackupFile form is a subform of the Folders form
The Folders form is a subform of the Drive form
The Drive form is a subform of the Volume form
The Volume form is a subform of the Computer main form.

Very basic ACCESS. Simple and uncomplicated.

Make sure you study the table designs. Primary Keys are automatically assigned. Always use Autonumber Data Types in Primary Keys. They are used by ACCESS as references only and for no other purpose.

 

[David44Coder]

I may be missing something but If there are no queries how would you find a particular backup file? I can see how you could query the table holding the filenames, but how are the other tables included?
I've haven't ever used a report and I'm not (yet) sure of the subforms functions. They follow the same sequence as the tables in the Relationships screen?
Should I add some of my data into the tables? Okay on using Autonumbers.

 

[LarryE]

I may be missing something but If there are no queries how would you find a particular backup file? I can see how you could query the table holding the filenames, but how are the other tables included?
I've haven't ever used a report and I'm not (yet) sure of the subforms functions. They follow the same sequence as the tables in the Relationships screen?
Should I add some of my data into the tables? Okay on using Autonumbers.

1. I am not sure what you mean by "find a particular backup file". You navigate to the Computer, then then Volume you want, then the Drive, then the Folder and finally the backupfile name. Use the navigation buttons to navigate forward, backwards etc. Each form has these buttons except the BackupFile name form because it is a continuous form.
2. If you want a form that displays all the backup files and their locations then here is an opportunity to create your own form and insert it as a sub-form at the bottom of the Computers form. You can copy the FrmBackupFile and use it as a new form. Name it FrmSummary. Then open the FrmSummary in Design mode, open its Record Source, and insert QryReport into it. Add all the fields you wish to display into the forms Record Source and add those fields to the form. Each form (and the report) has its own query as a Record Source. The only query that I specifically named is the QryReport. All the other queries used to edit forms, I did not name, so they do not show as named queries under the Queries section of the Navigation Bar.
3. The forms and sub-forms do follow the same sequence as the table relationships show. Computers>Volumes>Drives>Folders>Backup Files.
4. You can open the report by simply clicking the Report button at the bottom of the form. The form will close but re-open when you close the report.
5. My main point in giving you this file is so you learn how forms and reports work in a very basic simple ACCESS design. There is limited VBA code. I used VBA code so navigation buttons work and so you can quickly go to selected records on each form. Try the Find drop-down arrows on each form to select a particular Computer or Volume or Drive or Folder. You can open the VBA code window with alt+F11.

 

[David44Coder]

It's good to get a bit of an overview, This is all very new and unknown, How you've done things is different to what I know or understand so it will take me a while to figure it all out (he says optimistically).
I'll have a go at frmSummary as you've outlined. Might not succeed but can always try again.
Will this also search and display any same files or folders wherever they are?
I'm still keen to add my data as it may become more familiar and help nail down whats happening. And is this done manually (entering data into the tables) or is there some other mechanism ? (I suspect the former).

 

[LarryE]

It's good to get a bit of an overview, This is all very new and unknown, How you've done things is different to what I know or understand so it will take me a while to figure it all out (he says optimistically).
I'll have a go at frmSummary as you've outlined. Might not succeed but can always try again.
Will this also search and display any same files or folders wherever they are?
I'm still keen to add my data as it may become more familiar and help nail down whats happening. And is this done manually (entering data into the tables) or is there some other mechanism ? (I suspect the former).

You Add records on each section of the form using the

button. To add a new Computer, click the symbol on the Computer section for example. To Delete a record click the

button. You can delete the Laptop computer I entered for example. You can also delete the Volume One Drive I entered under the Win11-PC computer. It was a test record. Note that when you delete the Laptop computer I entered, all records related to that computer are also deleted automatically. That is how Relational Integrity works.

You use the form for adding, deleting and editing records and ONLY the form. Never ever enter data directly into a table by opening the table itself. Always use forms to add, delete and edit data.

Try adding your own real data. For example, Win11-PC has a Volume LAN that has a Drive A and Folder Hub G, but does not have a Backup File Name. Enter your backup file name.

The best I can do is give you an example of what you wanted to do.

 

[David44Coder]

I'm getting the hang of what to do. Have deleted Laptop and entered one of mine incl all the details/fields except Filename.
I see the Green text info.
Where is the data entered, e;g; If I wanted a browse Dialog to pick up all the details automatically?
Then a method to add all the files in a folder. Or are these tasks it doesn't do, or can't do yet ? Something I would add?
You say Never ever enter data directly into a table so I'm mindful of that.
If I hover over Report the balloon it says Delete this Account. I did click it and got something I couldn't return from.
I haven't studed the inner workings too closely... have looked at everything though. As expected a bit mysterious to start with!