Question Grouped query and sort numbers (1 Viewer)

Hi there,

i made a small database where i can assign unloading stations to trucks. It works well, but i am now in a dead end.

I rarly tried to normalize my tables and didn’t bother caring for the referential integrity. I somehow managed all the functionality with vba and sql.

I have below grouped query:


If a unloading station has multiple trucks assigned, i get them with a subquery. Thats all working fine.

So, an unloading station can have multiple trucks. And also, a truck can have multiple unloading stations. What if i want to number the order of unloading. Do i need a „number_order“ column in the shipment table? Or where can i save this data. What happens if i remove a unloading station from a truck and assign it to another one. This is the point where i don’t know how to handle this.

One solution which came in my mind is to create a temporary table by selecting all the unloading stations for one truck where i can input the order. But again what happens if i move unloading stations between trucks. I don’t want to have dead data entries in the db. Or do i need an extra column named „unloading order number“ which i update on all actions via sql?

I hope this is the right forum to ask. Please excuse my english. It is not my native language.

Thank you

Jessy,

Tell us in simple terms what your business is about. Who does what, when, where, how much and how often? You know your business and the database, but we do not. I recognize you are unloading trucks. I think you are unloading a quantity of "Pieces" each of which has a weight and volume. There may be many unloading stations and many trucks. There may be some constraint that some trucks may only work at certain stations, or some trucks can only hold so many pieces.... and that would be important to know.
As for English not being your native language, your English is suitable for communication here. However, if you write your material in your native language, then you can use google translate to create an English version for posting.
To see how good the translation is --copy this post to Google translate,then convert to your native language.
Here's a draft model from your post info. Need more info to understand your business and relationships.

Good luck.

Whenever you have many-to-many situations (in your case, many trucks and many stations) you OFTEN have something called a JUNCTION TABLE, which is something you can look up in this forum and on the web. In anticipation of needing one once things get clearer here, my I respectfully suggest a little bit of reading on that topic?

Technically, Access cannot do a many-to-many relationship between two tables. But it can SIMULATE a many-to-many using a third table as an intermediate, and this third table is the junction table which I named. If there is an ordering of assignments, it might be that it resides in the junction table.

Until we can see your problem better, this suggestion is based on a GUESS that you will need to know about junction tables. However, I think it is a good guess.

jessy. Welcome to awf!

From what you have posted, you should have the following tables/records

In tblStations, you should have the following records;
Station 1
Station 2
Station 3
Station 4

In tblTrucks, you should have
Truck 1
Truck 2
Truck 3

In a Junction table, you should have the parent records for
Station 1 / Truck 1
Station 2 / Truck 2
Station 3 / Truck 2
Station 3 / Truck 3
Station 4 / Truck 1

This means you have 4 records in your station table, 3 in your truck table, but 5 in the junction table that links stations to trucks.

This is based ONLY on what you have posted. If your have a structure different than this you may be running in to many problems.