Question A few questions, basic I am sure?

[neil-uk]

Hi Guys,

I am currently working non a new database, which is designed around electrical fuse boxes.... I must admit, I am very much a beginner at this, so please go easy on me

Currently, we have a few different fuse boxes, they are 36, 48, 72 and 96 way. What I plan to do eventually, is create a database where I can input information about the way in which they are wired etc...

My first question is this. I plan to create a table for each 'type' of fuse box. The table would be as follows;

Way|Load Type|Voltage|Current|Hookup 1|Hookup|

Those would be the columns, and each line represents a 'way' of the fuse box. I would then use a Form to enter the information.

The Load Column, will cross reference to another table, that knows the voltage and current already, so in the form I just enter the Load Type and Hookup next to the way. The hookup information will also come from a separate form, with the hookups already in. A hookup is effectively the cable its terminated to.

So, the question. IF I have a job, which requires more than one 48 way (for example), is there a way I can get the table to re-produce itself and copy its contents (bar the 'data') to a new table. So for example, the first 48 way is called "48 Way One" the second "48 Way 2"....

I plan to have an initial 'config' page, where a I write in how many of each type of fuse box I have. What I would really like, is for the form for each fuse box (ie 48 Way One, 48 Way Two) to create themselves Tabs in the form, and obviously work with the table...

Does anyone know if its possible do something like this, and what the best way to do it would be?

Many Thanks.

Sorry for the long post!

 

[RainLover]

Neil

It appears that you are looking for the basics of how do design.

Start with naming conventions. What I use is as follows.

For Tables, tblNameOfTable
For Queries qryNameOfQuery
For Forms frmNameOfForm
For Reports rptNameOfReport

Note that there are no spaces and no special Characters. You can use Numbers if you need to but I avoid them. You can also use the underscore "_" and some people do like qry_Name_of_Query

With Queries I even take it further to describe the use of the query, like

qryFrmNameOfForm or qryRptNameOfReport

Then with forms and Reports, if they are a Sub Form or Sub Report it would be frmNameOfFormSub. Note the Sub is at the end not the beginning. This helps to keep like forms together when sorted alphabetically.
Now for your Tables.

You do not create a separate table for each of your Fuse Boxes but rather keep them in the one table.
You need a table of fuse boxes first.

tblFuseBoxType
FuseBoxTypeID as Autonumber. This is your primary key
FuseBoxType as Text This is where you enter all the different types like 36, 48, 72 etc

Your second Table
tblFuseBoxDescription
FuseBoxDescriptionID as Autonumber. Primary Key
FuseBoxTypeID as Number Long. This is the foreign key to tblFuseBoxType and you store the number of the ID for the appropriate fuse box type here.
LoadType
Voltage
Current

What we have achieved here is not to store the same data twice. We only store a reference.

This is just the beginning. I suggest that you create your tables then post a copy of your database here so that I and others can give you some further assistance.

 

[Preyzar]

I recommend you use a Job table that describes to the header portion (ie. JobID, job name, customerID, etc.) and then you'll be able to have many line items relating to a single job in another Job Details table (ie. JobDetailID, JobID, etc.) You can then build a query that will return all line items belonging to a single job.

 

[neil-uk]

Hi Guys,

Many thanks for your replies, in particular to NedKelly

So, I have created the tables, and have uploaded them.

I am guessing that, for this to appear on the table, each of the fuse boxes will have a unique ID?

So, "36WFB1" for a first 36 way fuse box and then "36WFB2" for the second and so on? I guess this to be the FuseBoxTypeID

However, if we do an Autoumber, will this not have the way/circuit going up one every time data is entered. This is fine for the first box, as we will get 1-36. However, for the next box, is this going to start at 37?

So anyway, I have created the tables, so, some extra advice on where to go next would be lovely

Cheers
Neil

 

[RainLover]

I found some Names of fields with Spaces. Although permissible it is ill advised. You will find why later when you start serious coding.

I got rid of the default values. Again permissible but not advised.

There are some discussions on Natural V Surrogate Primary keys. Do a Google and decide for yourself. I prefer Surrogate (Autonumber). The sole purpose of the Primary key is to identify a particular record. It is not for the end user to see.

Let’s look at your table tbl_Jobs. It is not normalised. I.e. it has data that repeats and therefore is redundant. Namely Client. I would think that you could have many jobs for the One Client, so let’s put that into a separate Table.

tblClient
ClientID as Autonumber primary key
Client as text
ClientAddress as text
Contact as Text
Phone as Text
Town or City

Let’s look at town or maybe you might call this City. Again this is repeating Data, so now yet another table.

TblTown
TownID as Autonumber Primary Key
Town as Text
PostalCode as Text.
Keep repeating this process whenever you see a field that could repeat. However I would not create a Table for people’s names. Although John most likely would repeat I would not class this as a case for a new table. So simply think this process through.

Now to revisit tbl_Jobs. You had one field for Client. I have expanded that to include a half dozen attributes associated with that Client. Yet the data is stored once and referenced via a Number. And numbers are easier to search on and faster. Also when the user selects London you get London, not Londin or any other misspelling of the word. So you now have some Integrity in your Data.

Keep going on this until you have designed all your Tables. Extra time spent here will save a lot of heartaches later.

Next go to > Tools > Relationships. Connect all your Primary Keys to your Foreign Keys. Enforce Referential Integrity but do not select either of the cascade boxes. By doing this you cannot have a Child without a Parent. In other word if the Town of London does not appear in tblTown then it cannot be selected in tblClient

let me know when you want to move on to the next part of your design

 

[neil-uk]

Thanks for that, Ned.

I feel I have the right tables in place, and have attached the db.

So I am wondering whats next....?

Cheers
N

 

[RainLover]

Neil

I made a mistake. Sorry about that.

In tblFuse_Box_Type Rename the Primary Key to FuseBoxTypeID and then join it to FuseBoxTypeID in tblFuse_Box_Description.

tbl_Hookup. The primary key should be HookupID not LoomNo

In your relationships view you have TblFuseBox_Wiring related to tbl_hookup in two different ways. This is not correct.

My understanding of electrical systems is poor so it is difficult to advise what would be correct.

What is the relationship between these two tables. One Fusebox can have many Hookups or one Hookup can have many Fuseboxes.

You now need to educate me.

 

[RainLover]

Please also explain what Output is.

Be kind to me, as I don't speak electrical. Well maybe just a little but only 12 Volts.

 

[neil-uk]

Hi Ned,

Thanks for all this The relationships I broke myself! I managed to create them 'playing' and can't seem to get rid of them...


So, the way it works is this:

A Hookup is a cable, it has 6 smaller cables inside it. Each Hookup has a colour and a label for ID. Colour representing the area, the label representing the individual hook up. A loom should at this point be ignored, its simply multiple hook up cables. For example, a loom may contain 4 Hookups, this giving 24 small cables.

For this reason, as you will see the Fuse Box has Hookups, and a Circuit number. The circuit number is between 1 and 6 and the Hookup references the hookup list, and will be a drop down item in the form....

Currently, up to two circuits of a Hookup can link to a 'way' of the Fuse Box. For example, Red 1 Circuit 1 and Blue 2 Circuit 6, may plug into Way 1 of the fuse box. The, Red 1 Circuit 2 and Blue 2 Circuit 5 may plug into way 2.

Does that make sense?

 

[neil-uk]

Oh, an Output (also referred to a circuit) is one of the six little wires in the Hookup, if you get me?

 

[RainLover]

So it would appear that One FuseBoxWiring could have many Hookups.

If One FuseBox could have 6 different Hookups then you need 6 different records in Tbl_Hookup. One for each Output. I think you did say that each of these have different Colours etc so therefore they must be separate records.

Now who is confusing who.

I know your structure is incorrect, still working on what is correct.

It is 5:00 AM in the UK. When do you sleep.

The time here is "Watch Cricket Time". South Africa is giving us a hard time and that is just not Cricket.

 

[neil-uk]

Hi Ned,

Each fuse box is limited two circuits of a Hookup....although those circuits could be in Hookup Red 1 and Green 50 - and any colours/numbers between. But thats easy, just referencing and typing it in

The Hookup table does already have 6 outputs. It has obviously the name for the Hookup, and Output 1, Output 2, etc etc up to 6. Its important not to get confused between Hookups and Circuits (also called Outputs in other tables)...

A Hookup contains six circuits (also known as outputs).. Hence why the Hookup has a colour/label etc, and each of the outputs doesn't; because they're in the Hookup which already does.

Does that make sense?

It is indeed 5am here in the UK. I don't sleep much!

 

[RainLover]

If I am getting this right.

Delete all references to hookup and Hookup Circut in tbl_Fuse_Box_Wiring.

Create a Foregin Key of FuseBoxID in tbl_Hookup

Now one Fusebox can have two (Actually 0 or more) Hookups.

I am reasonably confident on this.

Now explain to me what data goes into Output. These may need to go into a separate table.

 

[neil-uk]

Yes. 0 is an option for a spare or empty Fuse 'way'.

The data that goes to the output is the data that is entered in the 'way'. So, in TBL_Hookup we have entered a Hookup called Red 1, for example.

Obviously, firstly, we need to tell the system which fuse box we have, how many of each etc. But, once this information is in, we firstly tell the Fuse Box which Load Type it has, this will come from our table called Tbl_Units. We tell it that the Unit/Appliance type is (for is example) a Washing Machine, and this then copies the information of Voltage and Current over automatically to the Fuse Box 'way'.

Once this information is in, we need to then tell it the first hookup to go to. This could be Red 1, so we enter that under "Hookup1". Under "HU1_C" we write the circuit , which will be between 1 and 6... We copy the same procedure for "Hookup2" if necessary.

We also at this point need to enter a channel number. A channel number is for paperwork purposes, so we can write a report about which Channel Numbers go to which fuse boxes. If, for example we have 2 48 way boxes, thats 96 'ways', BUT, each box will be labeled 1-48, so a channel number, is for reference - so fuses will be known by the channel number for the paperwork (fault finding) end. But obviously, for a wiring end, we need to keep the 1-48 for Fuse Box 1 and 1-48 for Fuse box 2 etc..

The data which needs to go to output is "LoadType"+"Channel_Number". This, for example may show in Red 1, Output 1 to be "Washing Machine 48".

Make sense?

Its pretty much the same as the data we entered in the Fuse Box, it just displays it differently.

All this information is easy to com across I am sure. Its how we tell the system via a form How Many of each Fuse Box we have, and how many 'ways' to display, and obviously how to make the form 'auto respond' to this, that I think will be the hard part?

 

[RainLover]

Neil

I am going to have a break.

I will have another look tomorrow.

Time for a warm beer and a cold pie.

PS. In the works of Zigfreid "We do not copy from one table to another here." We link.

 

[neil-uk]

Thanks for that. Have had a look over the last couple of days, but can't seem to find any documented info, your your knowledge is in my hands

 

[RainLover]

I am still have an issue with Output1 2 3 etc

I believe these should be in a separate table.

Update your tables as much as you can and send me a copy.

Appoligies for being away for so long.

 

[neil-uk]

Hi Ned,

I think I have made the changes you recommended...?

So whats next?

Cheers
Neil

 

[RainLover]

Neil

The next step is to work out how these Fuse Boxes relate to jobs.

You need to do this as I have no idea.

 

[neil-uk]

Hi Ned,

Well, you may have on job 1:

1 x 72 Way
2 x 48 Way
1 x 36 Way

and on job 2 maybe two boxes in total, as its smaller etc.

Essentially, the information that needs to to 'tie' in with the job is how many (and which) fuses boxes are on the job, what power supply they come from, and obviously all the info from the load/current in each fuse box... But in terms of how they relate to a job, basically, you could have any number of any time on a job.

With that information 'there', the information about ways, looms etc relates to each fuse box.