Filtering lookup lists

[MnR]

hi another neewbie question,

Currently working on a project where the user has to match turkeys to customers orders. the basic structure is tbl_customers, tbl_orders and tbl_turkeys. Now customers can have 5 orders via a repeating field etc. But the user has to match the turkeys to the orders. 5 turkeys to 5 orders. I want a way to filter out the already matched turkeys in order to stop the user matching more than 1 turkey to several orders. hope that makes sense

MnR

[Søren Dyhr]

. hope that makes sense

No it doesn't - I don't know if repeating fields in Access not are breaking 1NF which dictate "one fact per field"??? - whats the point in having a limit in the number of matches at 5 per turkeys/yorkshire puddings/bangers with mash??? Here is a many2many relation made by two one to many relations most often in a tree file/table configuration, where what you seems to be doing is one2five ...which reminds me of the shopping in russian stores before Perestroica where every family was entitled to a certain consumtion which fueled the black marked. Please explain the Access reasoning in it, since I admittedly know nout about Access!

--sd

[MnR]

diagrams might help you.

Firstly:

Entities: Customers, Orders, Turkeys. 3 seperate entities

one customer can have many orders, 1 order has 1 turkey.

Secondyly,

Structures is:

tbl_customers, tbl_orders, tbl_turkeys.

Thirdly:

tbl_customers fields: ID_customer, FirstName, SecondName, Address, TeleNo, Postcode.

tbl_order fields: ID_customer, ID_turkey (matching), WeightNeeded, Cancel?, Predicited bill.

tbl_turkeys:

ID_turkey, weightNeeded.

So when i enter a turkey id in the order table which is lookedup from the tbl_turkey table i want a way for it to stop a id being used more than once.

ps. there are no repeating field in access but you do have a 1 to many relationship which allows you to add more than one but this is not the case as i am actually making this in FileMaker pro so don't worry about access.

[SBG]

Set the Validation options for the field Turkey_ID in tbl_Orders:

Uncheck - Allow user to override

check - Unique Value

check - Display custom message; and enter "Sorry, this Turkey is already spoken for, please choose a different gobbler"

To filter Turkeys that are spoken for you could add another filed to tbl_turkeys named Available with a default value of "Y" which would be changed to "N" if a Turkey was assigned to a customer. Then add Available = Y to your relationship between tbl_Turkey and tbl_Orders.

[MnR]

How would i add that available=Y to the relationship and what to?

[Ugo DI LUCA]

You need a Join Table from Customers, Turkeys and Orders.

Orders actually currently has the structure of this Join Table, but you need an OrderID somewhere.

So change the Orders to be OrderLineItems and add a Table Order with only a serial in it as a start.

Now, in your OrderLineItems (the Old Order), drop a key OrderNum.

Relate the Order Table to the OrderLineItems with serial maching OrderNum, set the relationship to allow creation of related records, and start playing with a portal in Order showing all line items from OrderLineItems.

All you need then is a way to match any previous entries into the Line Items for this particular Order, then lock this entry.

A scripted approach would be straightforward with a multi-predicate relationship involving a global field gTurkeyID, but start by making the move to the portal way and you will probably understand what Soren meant in his answer.

[MnR]

I don't really understand what you mean, I understand I need another table to like match them but you have changed the names of my tables so I don't understand what you are getting at. Please can you use the names show in the picture above as it gets confusing. Plus having problems with trying to create bills, because they weight they need and the weight they get can't be shown together but please help first!

MnR

p.s. attaching file for you to have a look at.

sTurkeys.zip

[MnR]

I hope somebody will help me!

[Ugo DI LUCA]

Hi,

I have*no* idea what we are speaking about actually, now that I read back the thread. You said "Matching the Turkeys", then someone says "This Turkey was spoken for"...

I finally doubt what exactly a Turkey is and what the heck of a job you're doing.

May be I should wait to see if I have an epiphany.

[comment]

His question seems perfectly clear to me (unfortunately the answer less so):

He is selling turkeys. One turkey per order. He wants to see - in a portal, or in a value list - those turkeys that he has yet NOT sold. He doesn't want to sell the same turkey twice.

It seems to me that he has to reverse the order: instead of writing the TurkeyID into the Order, he has to write the OrderID into the record of the turkey being sold. Then, if IsEmpty(OrderID), that turkey is still available for sale.

But like I said, I don't really know.

[MnR]

Thanks solved that problem now just by using the unqiue value function, its not perfect but it does the job.