Hard problem with query (1 Viewer)

cheberdy

Member
Local time
Today, 23:04
Joined
Mar 22, 2023
Messages
77
I have a hard problem with the following data model: I have a Warehouse entity and a Bin entity. They are connected by a 1 : n relationship. Warehouse has as key attribute a warehouse number and bin has 30 bin numbers, because there are 30 bins, and a warehouse number, which is the foreign key. I would like to achieve the following: When a new warehouse is added with new warehouse number, there should be 30 new entries in a query with the new warehouse number and from 1-30 bin numbers. Is this possible?
 
If you don't care that the result is read-only, you can use a cartesian query.
 
don't see why not - what have you tried so far?
 
I have a hard problem with the following data model: I have a Warehouse entity and a Bin entity. They are connected by a 1 : n relationship. Warehouse has as key attribute a warehouse number and bin has 30 bin numbers, because there are 30 bins, and a warehouse number, which is the foreign key. I would like to achieve the following: When a new warehouse is added with new warehouse number, there should be 30 new entries in a query with the new warehouse number and from 1-30 bin numbers. Is this possible?
Can you upload your database?
 
Here's a sample database that might help. Pressing the generate button on the form adds the number of rows (bins in your case) to the table. Using the tally table allows you to easily vary the number of rows to add without having to use a VBA loop. Instead, you use an append query.
 

Attachments

Have a look at how a similar problem can be addressed much more elegantly without the insert queries.
 
Normally, I would not recommend adding "empty" records but I think that in the long run, having an actual bin record will make more sense and make other processes easier to model. We're talking about something physical in this case. We're not talking about adding rows for monthly payments that might never happen. When the warehouse is built out, the bins are also built.

If @cheberdy understands the method that adds a bin on the fly, then I don't object to it.
 

Users who are viewing this thread

Back
Top Bottom