Multiple Many to many same table/form

[Delid4ve]

Hi all, a long time since I've used access but having to build a database to store our company refrigerant logs and whilst I have the majority suss'd im struggling in one area which I hope somebody can shed some light because my brain is going into meltdown.

The purpose of the database is to store:
1:cylinder collection notes
2:cylinder returns notes
3:cylinder usage logs
4:hazardous waste consignment notes

Where I'm stuck is here:
1 consignment note can contain multiple cylinders
1 consignment note can contain multiple weights specific to a certain cylinder
How do a: I go about these relationships as this its what's stopping me in my tracks - 2x 1 to many relationships that also have to be linked by a 1-1 relationship and update data input on a single form when a consignment note comes in.
I maybe being real dumb here but can't seem to fathom it out.
Thanks in advance for any help.

 

[James Deckert]

>>1 consignment note can contain multiple cylinders
you'd set this up like
ConsignmentNoteTbl
id
other fields

CylinderTbl
id
ConsignmentNoteID (FK)
other fields

The ConsignmentNoteID field is related to ConsignmentNoteTbl.id in a one to many relationship

If the primary key from one field is related to the primary key of another table, then it will be one to one

>>1 consignment note can contain multiple weights specific to a certain cylinder
I don't understand this, but wouldn't the weight be related to the cylinder, not the note?

 

[plog]

Forget forms for now. Structure this properly then work out the user interface.

Without seeing the rest of your database structure its a little hard to give specific advice. But when has ignorance stopped an commentor on the internet?

#1 & #2 sound like the same thing. #3 might even be able to be hammered into the same table as well. You would store these in a table like this:

CylinderNotes
ID_Cylinder, number, foreign key to cylinder table
CNote_Type, text, used to denote if Collection/Return or Usage note
CNote_Note, text, actual note

#4 is probably going to need at least 1 more table. The simple way to do it would be one table for the consignment note then another table to define which cylinder/weight it applies to. Like so:

ConsignmentNotes
CNote_ID, autonumber, primary key
CNote_Text, text, actual note

CNoteAssignments
ID_CNote, number, foriegn key to ConsignmentNotes
ID_Cylinder, number, foreign key to Cylinders
Assg_Weight, text/number, used to define weight this is for

 

[Delid4ve]

A consignment note is for destruction of refrigerants as hazardous waste, 1 consignment note can transfer multiple cylinders each containing a weight of refrigerant present on the consignment note(this is where the weight is specific to a cylinder). Ie:

Consignment note number: abc123 (pk)

Waste transferred:
Cylinder123(fk) : 6.3kg (help): refrigerant type a(help)
Cylinder456(fk) : 9.1kg (help): refrigerant type b(help)

Engineer: a (fk)
Supplier: a (fk)
Vehicle reg: (fk)
Date: xx/xx/xxxx
End of consignment note

Note that multiple cylinders are present with multiple weights and a refrigerant type that have to be specific to the cylinder number(pk), im assuming I need to use some form of junction table but I can't find anything specific to this environment using goog, everything references the product to order scenario which this is not.

 

[Delid4ve]

I'll be home in hour so I'll post the database setup to show more. Thanks for looking guys and feedback. Most appreciated

 

[Delid4ve]

Current setup attached.

So here is a quick breakdown:

Collection note - collection of a or multiple cylinder/s from a supplier by an engineer (initial weight of contents discovered at this point)

return note - return of a or multiple cylinder/s to a supplier by an engineer

consignment note - the return of a or multiple cylinder/s to a supplier that contain waste and have to have this form instead of a returns note (waste weight within cylinder logging requirement)

engineers log - a log created by the engineer containing all uses out of/or into if waste of a cylinder (tare weight discovered at this point)

other tables - engineers, refrigerant types(whats in the cylinder), f-gas category(type of engineer qualification), cylinders(list of cylinders with serial number), hazardous waste producer(not worried about this just yet), suppliers, vehicles(required for consignment note)

Thanks again all if anyone can help :banghead:

 

[plog]

Your relationships are a big mess. You should only be able to trace one path between tables. For example, there should only be one way to get from Cylinders to Suppliers, in your relationship view I can easily trace 4. That's not right.

You really need to better define how your data fits together. I don't know how Cylinders and Suppliers should be related, but I do know it should only be one way. You need to clean up the spider web you have.

That's the biggest issue. You should also do a better job naming your tables/fields. You should only use alphanumeric characters and underscores. It just makes writing code later easier. So instead of [Collection Notes] I would call it [CollectionNotes]. You should eliminate spaces and all special characters (parenthesis, dashes, etc.)

 

[Delid4ve]

I did say it had been along time (6th form 15 years ago) Thanks for the pointers
Having a read online(prob not the best tuts) most of the emphasis was breaking the data down as easier to bring back together, hence the web
Thanks for the help.

So for example:

Cylinder_a could be picked up from supplier_a but then returned to supplier_b how would this simple thing be represented in tables with different dates but applying to the same cylinder.

 

[plog]

You actually did a very good job of putting the right fields in the right number of tables ( a lot of people don't make it that far). It's just the way you strung them all together that's incorrect.

Cylinder_a could be picked up from supplier_a but then returned to supplier_b how would this simple thing be represented in tables with different dates but applying to the same cylinder.

Modeling that, and just that. I would have this:

Cylinder Table
Supplier Table
CylinderTransaction Table

The first 2 tables are simply understood. The third would have these fields:

CylinderTransactions
Trans_ID, autonumber, primary key
ID_Cylinder, number, foreign key to Cylinders
ID_Supplier, number, foreign key to Suppliers
Trans_Date, date, date transaction occured
Trans_Type, text, type of transaction (Return, Pick Up)

That's just modeling that one sentence you stated. I'm sure your situation is probably more nuanced and complex than that.

Rather than, you propose situations like this and I explain them generically, you should give an explanation of what your business is about. Without using database jargon, explain what you are doing and keeping track of. Again, pretend databases don't exist, just explain what you are doing in your business.

 

[Delid4ve]

Right,

So we are a refrigeration company.
Part of our work involves removal and addition of refrigerant gases to fridges/freezers etc into or from a cylinder.

Under the regulations we have to log certain information and keep these records. At present this is just filled in folders with excel spred sheets and takes up space, is a headache to work with and needs to be simplified so others can carry out the task.

We have two types of cylinder - a full one of a refrigerant gas, albeit there are different types of refrigerant for adding to fridges, and an empty one(recovery cylinder) for removing from fridges.

An engineer will collect a cylinder/s from a supplier (collection note), create a log for the use of the cylinder(engineers log) and then return the cylinder/s(return note for empty cylinder that started full)(consignment note for full cylinder that started empty)

Now, there maybe multiple logs for a cylinder if it is transferred between engineers. There may also be multiple cylinders on each type of note.

A cylinder has 4 weights:
start gross weight (tare weight for recovery(0 contents +tare weight), all different weights for other gases until I recieve the collection note and log(tare weight + gas content)
Start net weight (cylinder contents) 0 for recovery, varying contents levels for gases) I don't know this until I recieve collection note
Tare weight: the weight of the empty bottle(I don't know this until I recieve the log once the bottle is returned)
Return gross weight (I don't know this until I recieve the log once the bottle is returned)
The main purpose of the logging system is that I and the governing bodies/environment agency can check that all gas is accounted for from start to finish which I will eventually set up in a report using calculations.

Hopefully this isn't to confusing, this is just the basics

 

[Delid4ve]

Thanks for the help on this guys.

Thanks to your comments I believe I have sorted this now by
A: sorting out the relationships and including a multitude of transactional tables between my many to many relationships
B: Moving certain fields into the transaction tables - I believe entity normalisation (read a whole blog about the different types of normalisation)
C: Deleting all relationships created using the lookup method and creating instead in the relationship window

Now my tables work correctly, onto stage 2, data entry forms with some vba thrown in.

Thanks again