General setup of DataBase

[MrDummy]

I am quite new to Access but managed to have make a look up form where a record is shown with pictures, hyperlinks and the result of some calculation. Next problem arises:



I have several standards goods which have to be transported in a car from A to B. How to get one form which combines all the data?

In the attachement I have made it clear (I hope).

TblStandard gives all the standard goods (like printer, Harddisk etc). These have also some variables (called Var1, VAR2 etc) these variables do not have standard values. eg. a printer can be packed in a carton box and sometimes in a paper bag. So these variables have to be filled in by the user.

The combination of the Standard goods with the variables is packed in a box. Several goods can be packed in one box. So a printer (packed in a carton box) can be packed together with a harddisk (packed in a paper envelop) in one box. The box will get an ID like BOXSEV1 or BOXSEV2 and some documentation (of course, it's 2015). Several boxes are packed in one car. The box can be transported with sevaral box specific conditions like temp, coluor indication, stickers etc. The car will make a journey from A to B. Each journey will have some specific info like date and sender/receiver info. When the car arrives in point B the car is unloaded and here it ends.

I have tried queries and forms; however no luck so far.

[Ruegen]

looking at your relationship only one box can be added your journey

if you held a journey_id in the box's table then you could have many box's to each journey
if you held a car_id in the box's table you could also have the related car to that box (optional)

if it is only one car per journey then you would just store the car id in the journey table

It really depends on how you want to set it up. You don't want to set up the boxes in the car table because you want that to change and be reused (and look up history).

Does any of what I have suggested help?

[MrDummy]

Dear Ruegen. Your suggestions help. Many thanks for your reply and makes things clearer so I am in the learning process. I have made a few changes.

One is the BoxID. I think more boxes can be added to one Journey in this way. The CarID I added. I don´t see the need for it at this stage of development but maybe it is handy in the future so following your advise.

The last thing is that I added is the BoxID also in the tblStandVer because more of the same items from tblStandard can be added in one box, only then with different variations. eg One printer packed in a carton box and one printer packed in a plastic box but then in one box.

Only not sure if I have to leave the StandVerID in the tblBoxes.

_________________________________________

[Ruegen]

I'd create a field in tblStandard and put BoxId in the field

then every tblStandard I create I record the BoxID to put many of them in the same box

If I needed StandVar in the same box with the Standards then I would put BoxID in tblStandVar too

but If I didn't need StandVar then I would remove tblStandVar

does tblStandard and tblStandVar need to be directly related?

If you had to pick the type of Var standard to put in the box then your db is correct.

Not sure why you are using the word StandVar - using Var or Variable in table names is very confusing because variables are a code thing and naming a table variable, var etc is not a good idea. I'd say it's a bad idea (but I might be corrected by others).

Also you don't need to name NameCar - give it something like model or name but not NameCar

When you create a field the label will be NameCar - having it name will save you time without needing to rename the label on the form (Access will give you a label for the field when you drop a field in a form).
When you need to refer to it in proper SQL you will say

SELECT tblCars.CarID, tblCars.Name FROM tblCars;

One Journey can have many boxes and each box can contain many tblStandVarsand standards I notice your tblStandard is not tblStandards (which is plural) and since the table is going to contain rows and rows of tblStandard - it wouldn't be one.

also tblStandVar (again singular instead of plural!) contains Var1, Var2, Var3 - what are those for? What happens if you require Var4? Can that be made into a table?

Plural and Singular is becoming more and more important in database frameworks - maybe not in Access but it is still good practice.

Access has it's own conventions which you seem to have no problem with since you have "tbl" before the table names

[Ruegen]

Just in case
http://access.mvps.org/access/tencommandments.htm

[MrDummy]

Dear Ruegen,

Thanks for the 10 commandments. Printed and taped to my wall.

A little bit of explanation on the tblStandard and the variables of the tblStandVar (now renamed tblStandPack).

Step 1:
The tblStandard is a list of Standard items. The list itself changes about every 2 years and it is not intended to be changed by the user. Only by the developper. The records (all Data type "short text") in the tblStandard are limited to about 3400 and fields are limited to 30. Every single one of these standard records, can be sent in different packages like packed in a carton box, packed in plastic and/or have specific names added to each of these standard records. This latter, let's call it additional info (pack1, pack2 etc Data Type can be "Short text" or "Yes/No") is limited to 8 fields and can never be more but are given by the user. So the user is allowed to add Additional info depending on how he would like to send to. This additional info can even be blank. Together, the standard record and the Additional Info, go into the box. It is also possible to have the same tblStandard records in one box with each having its own and/or different additional info.

I choose to do it like this because a record in the tblStandVar (now tblStandPack) is now a combinatoin of BoxID, StandardID and the Additional info and not to have a seperate tbl for the Additional Info. But maybe it is handy to have the additonal info in a separate tbl. I followed a similar way working for the tblJourneys and tblBoxes.

Step 2
After the combination (Standard + Additional info) has been made the records go into the box. The box itself can contain several of these combined records (max200). One box (with the multiple combinations) can also be sent under different conditions. Like cooled, have a special colour marking or label or a special boxnumber. All these are also user depended. This additional Box info is also added in the tblBoxes.

Step 3
Then the box is put in a car and the box will make a journey from A to B. Never C or D.

So I think I am pretty much going in the right direction. But advise is very much appreciated.

So many words but hope it will make things a little bit clearer. I stopped at Step 2 with providing too much info. It is already a lot to digest.

For the tbl..... item. A friend of mine gave me the advise and it took some time to get used to it (same as leaving the Space out) but now I see the advantages.

[Ruegen]

Cool

I've got an additional link here you should use for Access (and really only Access, other db frameworks would find this bad naming)
http://access.mvps.org/access/general/gen0012.htm

[MrDummy]

yes indeed. Makes it clear. Adjusted my tables. only two look up tables and the rest are "normal"tables where the user can add info.