One field with two different attributes?

[samah]

Access beginner. Access 2003 on WinXP.

In Sales Order Processing Database I have the following tables.

tblProducts
ProductID pk text
ProductName
ProductPrice
<other fields >

tblDepartments
DepartmentName pk text

tblEmployees
EmployeeID pk text
EmpFullName
EmpLastLame
DeparmentName fk
<other fields>

tblCustomers
CustomerID pk text
CusotomerName
<other fields>

tblOrders
OrderID pk number
OrderDate
CustomerID fk
EmployeeID fk

tblOrderDetails
OrderID cpk
ProductID cpk
Qty
QuotedPrice
<Other fields>

In the orders main form where Record Source property is set to a query
based on orders table, I have a combo box that provides employee names
based on a query that returns sales department employees.

Now to the question. In the company there some orders/sales that are
treated as 'Direct' and can not be attributed to any sales person. So
for the above combo box I need one more value 'Direct' added along with
the employees' name. One thing that came to my mid first was to add a
new record to tblEmployees with a single field value 'Direct' under
empLastName field and proceed.

The value 'Direct' doesn't belong to the tblEmployees but on a scenario
like this, can we use it as an exception and break the rules?

I have a few acquired databases where situations similar to this has
been handled this way!

I am very much curious to know how you, the developers and experts
handle this kind situation?

Thank you to all in advance.

- samah

 

[fredg]

Access beginner. Access 2003 on WinXP.

In Sales Order Processing Database I have the following tables.

tblProducts
ProductID pk text
ProductName
ProductPrice
<other fields >

tblDepartments
DepartmentName pk text

tblEmployees
EmployeeID pk text
EmpFullName
EmpLastLame
DeparmentName fk
<other fields>

tblCustomers
CustomerID pk text
CusotomerName
<other fields>

tblOrders
OrderID pk number
OrderDate
CustomerID fk
EmployeeID fk

tblOrderDetails
OrderID cpk
ProductID cpk
Qty
QuotedPrice
<Other fields>

In the orders main form where Record Source property is set to a query
based on orders table, I have a combo box that provides employee names
based on a query that returns sales department employees.

Now to the question. In the company there some orders/sales that are
treated as 'Direct' and can not be attributed to any sales person. So
for the above combo box I need one more value 'Direct' added along with
the employees' name. One thing that came to my mid first was to add a
new record to tblEmployees with a single field value 'Direct' under
empLastName field and proceed.

The value 'Direct' doesn't belong to the tblEmployees but on a scenario
like this, can we use it as an exception and break the rules?

I have a few acquired databases where situations similar to this has
been handled this way!

I am very much curious to know how you, the developers and experts
handle this kind situation?

Thank you to all in advance.

- samah

You can adapt the following SQL as teh rowsource for the combo box.

Change the table and field names to your actual table and field names.

Note that the symbols <> surround the word "Direct". That is to have
the list sort with the word <Direct> at the top of the list before any
other listing.

First add "Direct" to the drop down list.

As Combo box Rowsource:

Select YourTable.[SalesmanID, [SalesmanName] From YourTable
Union Select Null, "<Direct>" from YourTable Order by [SalesmanName];

The above shows the SalesmanID as well as the SalesmanName fields,
leaving a blank in the SalesmanID field where <Direct> is shown.

<Direct>
15 Jones
3 Smith
20 etc...

Or¡K Do you wish to show just the SalesmanName column?

Select YourTable.SalsmanNames From YourTable Union Select
"<Direct>" from YourTable Order by SalesmanName;

<Direct>
Jones
Smith
etc...

Change the table and field names to your actual table and field names.

 

[samah]

Access beginner. Access 2003 on WinXP.

In Sales Order Processing Database I have the following tables.

tblProducts
ProductID pk text
ProductName
ProductPrice
<other fields >

tblDepartments
DepartmentName pk text

tblEmployees
EmployeeID pk text
EmpFullName
EmpLastLame
DeparmentName fk
<other fields>

tblCustomers
CustomerID pk text
CusotomerName
<other fields>

tblOrders
OrderID pk number
OrderDate
CustomerID fk
EmployeeID fk

tblOrderDetails
OrderID cpk
ProductID cpk
Qty
QuotedPrice
<Other fields>

In the orders main form where Record Source property is set to a query
based on orders table, I have a combo box that provides employee names
based on a query that returns sales department employees.

Now to the question. In the company there some orders/sales that are
treated as 'Direct' and can not be attributed to any sales person. So
for the above combo box I need one more value 'Direct' added along with
the employees' name. One thing that came to my mid first was to add a
new record to tblEmployees with a single field value 'Direct' under
empLastName field and proceed.

The value 'Direct' doesn't belong to the tblEmployees but on a scenario
like this, can we use it as an exception and break the rules?

I have a few acquired databases where situations similar to this has
been handled this way!

I am very much curious to know how you, the developers and experts
handle this kind situation?

Thank you to all in advance.

- samah

You can adapt the following SQL as teh rowsource for the combo box.

Change the table and field names to your actual table and field names.

Note that the symbols <> surround the word "Direct". That is to have
the list sort with the word <Direct> at the top of the list before any
other listing.

First add "Direct" to the drop down list.

As Combo box Rowsource:

Select YourTable.[SalesmanID, [SalesmanName] From YourTable
Union Select Null, "<Direct>" from YourTable Order by [SalesmanName];

The above shows the SalesmanID as well as the SalesmanName fields,
leaving a blank in the SalesmanID field where <Direct> is shown.

<Direct>
15 Jones
3 Smith
20 etc...

Or¡K Do you wish to show just the SalesmanName column?

Select YourTable.SalsmanNames From YourTable Union Select
"<Direct>" from YourTable Order by SalesmanName;

<Direct>
Jones
Smith
etc...

Change the table and field names to your actual table and field names.

Thank you, Fred.

-samah

 

[Tony Toews [MVP]]

Now to the question. In the company there some orders/sales that are
treated as 'Direct' and can not be attributed to any sales person. So
for the above combo box I need one more value 'Direct' added along with
the employees' name. One thing that came to my mid first was to add a
new record to tblEmployees with a single field value 'Direct' under
empLastName field and proceed.

I'm a bit confused. You state that the sales can't be attributed to
any sales person but then you state "one more value 'Direct' added
along with the employees' name". So I'm a bit confused as to your
over all objective here.

Why not just add a check box called Direct?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/