Question How to deal with a known list and unknown and unknown list of names? (1 Viewer)

[captgnvr]

How to deal with a known and unknown list of names?

D/SIRS

I have completed the tables, queries and reports to a major extent thanks to this forum.

Pity is that after I have almost completed the project, I realise that I have two sets of people. One is staff list and another is unknown list which will have pilots, agents and officials.

Everything figured out for staff and sales to staff.

I tried to use the same combo box which has staff names to enter the pilots or agents name when some store items are given but the combo box does not accept even after setting limit to list as 'no'.

Can you pls guide me how to incorporate both - known names list and unknown names under one sales tables. I am thinking of making extra field to indicate 'staff' as default and enter 'others' if it is pilot/agents etc.

Would prefer to know best way to do this. brgds/captgnvr

 

[namliam]

well, just make your existing combobox as "limit to list" to no.

Anything added will have to be confirmed to be "non-staff" or something
y mke ur curr cbo as "ltl" st it to no?

a/thing + 'll ve tbc as 'non-st' or 'unkn' or s/thing

 

[captgnvr]

Dear Mister Namliam

Thank you for the guidance. I have already tried with -limit to list- set to 'no'. But when I enter a name not in the list, I get a message saying "text entered is not an item in the list". What must I do?

Somehow managed to read y o u r white fonts.

So you do advice me to create one more field to enter 'staff' or 'others'.

This reply is only for your sake with pains taken to cut out all abbreviations.

 

[DCrake]

Do you want to make the new name a known name and save it in thetable for reuse? If so look at the Not In List Event

 

[captgnvr]

D/David
Thank You. The names are not needed to be stored. But needed to retain for monthly accounting of sales and presentations.

For example: Charlie and smith are staff and entries are fine bcos their names are in the list. But if it is outsiders then I have to enter like 'pilot at Rotterdam' or 'agent at Istanbul' and the likes.

If I understood your question well the answer is I do not want the names to be stored in the staff list but should be able to retrieve items gifted to pilots and agents along with sales to the staff.

 

[DCrake]

Somewhere you are going to have to save the info for historic purposes and for reporting. If you are using a combo box of known names and this is linked to a table using PK and FK relationships you are going to struggle using unknown names. As unknowns do not have primary keys.

What you could do is to have a known name named Other. When the user selects this known name it enables the user to enter a name in say an input box which could then be used to store the unknown name as text into a different textbox/field in the table.

If you bring up the record again at a later date you would need to be able to see the unknown name so by referring to the extra field for unknowns you will be able to view/amend it.

 

[namliam]

I would handle this by 'simply' adding in the names into the pilot table, this makes for a single point of reference and potentially allowing for 'repeat unknown' customers.

Alternatively you can hide the unknown customers from the list by selecting only the 'real' pilots for the combolist.

 

[captgnvr]

D/David

I thought of the same and was planning to have separate tables for outsiders which will be maximum of 5 to 10 entries a month. If I have separate tables then I have to have all the queries I made for staff also available for outsiders. That means I will have two sets of tables, queries, forms and reports. So I sought the forum help to find a better way to tackle a known list and unknown names list.

I was reading up the 'not in list' event to have macr etc and in the mean time ur response came and I am answering so that I dont delay.

 

[captgnvr]

I would handle this by 'simply' adding in the names into the pilot table, this makes for a single point of reference and potentially allowing for 'repeat unknown' customers.

Alternatively you can hide the unknown customers from the list by selecting only the 'real' pilots for the combolist.

Dear Mr. Namliam
Pilots are not the only category. It will be agents, pilots, port officials, boat or tug crew and so on. So cant make exclusive 'pilot table'.

Can you elaborate on how to hide the unknown customers from the list by selecting only the 'real'...... combolist??

I am thinking it will be better to go to the main table and enter under names of staff the outsiders as and when the gifts are given with an extra field to mark them as 'others' but with default value as "staff". So that can filter them out by using staff/others field. Please advice.

 

[namliam]

Dear Mr. Namliam
Pilots are not the only category. It will be agents, pilots, port officials, boat or tug crew and so on. So cant make exclusive 'pilot table'.

Appologies, I meant the "known" table....

Can you elaborate on how to hide the unknown customers from the list by selecting only the 'real'...... combolist??

Add the unknown customers to the existing tables with a category of "Unknown" or something that you can identify them in your table.

Then in your combobox displaying the known customers, change your recordsource from being the table or something like:
Select * from Yourtable
make it something like
Select * from YourTable where Category <> 'Unknown'
Or what ever way you deside to identify the unknown customers.

I am thinking it will be better to go to the main table and enter under names of staff the outsiders as and when the gifts are given with an extra field to mark them as 'others' but with default value as "staff". So that can filter them out by using staff/others field. Please advice.

There are many ways to "skin this cat", as long as you mark the 'visitors' as such in any way shape or form you can easily store all in one table and 'hide' them from being visible, while keeping the simplicity of only having one table for 'customers' known or unknown.

 

[captgnvr]

Dear Mr. Namliam

Started to make headway. I have made few entries in the staff table as pilots, agents, surveyors and also added a lookup field as category with staff/charts/owner category.

Also planning to give access to add more of the outsiders if need be by providing a command button which can also be used to add for example 'pilot' as 'pilot at Sydney'.

When the items given is entered as per auto sale-id, then to compute without commission if the field is not staff.

Started to work on this and will see what I am going face. But definitely like you suggested keeping it under one table will save me from too many queries, tables and forms.

 

[DCrake]

If you have a table of knowns and a table of unknowns you can then create a union query of both tables with a flag as to the source of the person. You can then use this union query for all your other queries. That way you do not need duplcate queries as you indicated.

 

[captgnvr]

D/David

I am caught up with this very nice suggestion of yours. I am not able to view far as I am first time to venturing into. But fearing if I use this union query will I have to redo all the other forms, reports and queries. I have started to study on your suggestion and get a hang of it and implement. Thank you.

 

[Banana]

Do be aware that union queries are non-updatable so they may not be appropriate for forms where you want to edit the names there. It'll be fine as a combobox rowsource or report recordsource. For the form that handles editing the both table, you probably will need a separate query to edit one of either tables. OTOH, if the only editing you do is via combobox's NotInList, you could probably get away with a little unbound popup form that then insert into appropriate table and the combobox can still use the union query.

Best of luck!

 

[captgnvr]

D/Banana
Thank you for the valuable input. I will keep in mind when I get down to do it.

 

[captgnvr]

D/Sirs

I felt having the unknown people also in the same staff table with a category-field and entry for the people for known and unknown is ok.

But my fears came true in a query when I am calculating the amount, the formula has gotten complicated to construct; sale-amt for three category; sale-amtA if it is staff with 10pct; sale-amtB if it is others; sale-amtC if it is owners category.

So shall I place three expressions for sale-A, B and C, in the query??

Will be eager and interesting to find the way out of this. Thanks to all of you, whole day gone very interestingly with so many feedback and help on this thread. Pls help.

 

[captgnvr]

D/Sirs

Can I put this "SaleAmtA: If ([CAT]='CHARTS' THEN [QTY]*[AV_PRICE])" in a query?

Now I have known and unknown ppl all under one table with 'cat' field to identify staff or charterer or owners.

Since the calculations are required for the items and amount sold, I was trying to make three expressions as saleamtA, B and C.

When I tried with the above formula, getting error "you might have entered an operand without an operator"

 

[DCrake]

SaleAmtA: IIf([CAT]='CHARTS' , [QTY]*[AV_PRICE],0)

In this example if the cat is charts then perform calulation other wise return 0

 

[captgnvr]

D/David

Thank you very much. It works. So I have almost gotten another of the problem of having known and unknown names in the same table issue solved, thanks to all of you.

The only thing is for example if from the unknown names 'agent' is selected, I have to provide a command button to open the main table in database mode and edit 'agent' to 'agent at sydney' or 'agent mr. smith'.