Lagerflytning - opdaterings quiry

can you try explain the relationships between table 1 and table 2?

If table1 lok and type are the primary key for the table and table 2 lok and type are the foreign key for the table and these two fields are the relationship between the two tables. Then you should just make a query in Accesss wher you have both tables and then using drag and drop join the two fields.

The only comment value in the 2 tables are the Type. The type tell us that a warehouse location in Tabel1 have a size "1" and that a location in Tabel2 have the same size.

can you try explain the relationships between table 1 and table 2?

Terry, I don't know how to explain it in another way, then already done.
Let me try to give you the situation
I have a warehouse with 1000 locations, and must of the are filled with commodities.
I want to move this commodities to another warehouse with 1000 locatons.
Each location on have it own unike number.
My goal is to create a pickliste, telling the picker in the old warehouse: article no - locataton and quantity. When he have picked the item, put the picklist on the box and sent it to the new warehouse, we they from the same picklist can read the location where the article should be in the furture.

To do this I need to make a table giving all locations in the new warehouse, together with a location type ex pallet size - box A size - box B size.

From the old warehouse I will get an inventory list, with article name, location, qty and location type.

The I want to generate a report, where I have article name, qty, old location, and that the quiry add a location in the new warehouse, where the choosen location have the same location type as in the old warehouse, and where each location in the new warehouse only are used onces. 

I hope this give sence.
rgds
Emanuel

You can write in danish, I understand almost everything

But how do you decide which location in the new warehouse has to be used? If there is no direct relationship then you cant do it in  aquery, you will need to do it in code.

If you don't mind, and understand my english, I will prefer to keep the communication in english, thats easier for me when you are write in english.

Regarding decision of locations to be used - in fact i dont care, so long that the system take one with the same location type as in the old warehouse.

then you can not do it with a query.

and it will require a redesign of your database so that you have some way of allocating a location in the new store for a specific item from the old store.

Yes, that is what I am looking for - but i don't have a clue of how I should do. It will not be a problem to redesign the database.

A database is normally designed based what is required and other than what you have said here I dont know any more.

Is the move from the old store to the new store a one off thing, or is it something which happens regularly?

Is it possible to give the new stores locations the same name and type? Then its just a case of moving from one store/location to another store/location.

What if the new store doesnt have the same number of locations with th esame type? This would meen that some items cant be moved?

As you can see there are a number of questions and I'm sure lots more.

So its up to you to ask yourself these questions, then it seasier to decide what you want and then its much easier to make something.

designed based what is required = designed based on what is required

No, it is not possible to give them same name. And might be there wont be enough with the same location type, all this senarios have been evaluted. And for your info i will have other informations in the tables - but the issue here is.

Do access have a function where it can read in table 1, line 1, saying type 1, and then look for the first line in table 2 with a match on type. Then write the wanted information to a new table or show it in the quiry. Next set a mark in table 2, telling that this product now have been moved (in the system) so do look at this.

The other way around, take the first line in table 2, look at the type, find a lokation in table 1 with same type, and write the location to the report, mark is in table 1 so it not is used for the next loop.

So I am looking for a way handling this issue

Access doesnt have a standard function for doing this, it will need to be made in code. But if the database is used from ASP or Visual Basic then the code should be put there and not in the Access database.

How are you using the dB?

If you are using it together with forms in Access then I can maybe put some code together if I get time over the weeekend.

But I would like you to send me your database so that I can see what you have already.

You say
"... all this senarios have been evaluted ...."

What do you do in the case of there not being enough locations

First - their will be enough location.
I have found another way to handle the problem - by do a merge in excel of the 2 kinds of locations, together with the type - that is a little manual work, but it will only take 5 min to do, and then i have the link between the needed informations, and can easy make the rest in access, when the data is imported again.
Thanks for the dialog.