Table field lookup bound to column 1. Can I filter using unbound column 3?

[PeakH]

I have a table tblCommission with a lookup field. Since we can pay commission to either an entity or an employee, the lookup field uses a union:

SELECT EntityID, Entity, "tblSaleEntities" AS tbl
FROM tblSaleEntities
UNION ALL
SELECT EmployeeID, Employee, "tblEmployees" AS tbl
FROM tblEmployees
WHERE ((Current = True) AND (SalesTeam = True));

The field is bound to column 1, but since it's taking from two tables, an entity and an employee could share the same ID number, so when I report I want to filter based on the bound column as well as the [tbl] column. Is this possible if it's not the bound column or do I need to figure out how to store the information in a new field in the table?
If it is possible, how do I reference it in a report query or VBA where clause?

Thank you in advance!!

[ranman256]

in a union , the 1st query gets the field name. (it ignores any other field names )
if the rpt uses the union query...

docmd.OpenReport "rpt",acViewPreview ,,"[field]='" & me.txtbox & "'"
or for numerics:
docmd.OpenReport "rpt",acViewPreview ,,"[field]=" & me.txtID

[June7]

Reference columns of combobox by index. Index begins with 0 so if the value you want is in third column its index is 2.

Does the report RecordSource also use a UNION query? As ranman pointed out, the first SELECT sets the field names and is not necessary to repeat alias on other SELECT lines. If you want, use an alias that makes sense for both tables.

SELECT EntityID AS EntOrEmpID, ...

DoCmd.OpenReport "reportname", acViewPreview, , "EntOrEmpID=" & Me.combobox & " AND tbl='" & Me.combobox.Column(2) & "'"

Otherwise, post the report RecordSource SQL statement.

[PeakH]

I haven't created the report yet, but I don't imagine it will need a union query. It will just look up order details from tblOrders, tblOrderdetails, and tblCommission. I just didn't know if the field in tblCommission stores the data from the unbound columns. Also the query that populates the field will have new records added as well as old records omitted. So employee "Sarah" with ID#3 on tblEmployee moved off the sales team so she will no longer be included in the query, but I want to make a report for all of the sales she made. The problem being that sales company "XYZ" has ID#3 on tblSaleEntities, so both Sarah and XYZ have "3" stored in the tblCommissions.Entity field.

If I use the
DoCmd.OpenReport "reportname", acViewPreview, , "EntOrEmpID=" & Me.combobox & " AND tbl='" & Me.combobox.Column(2) & "'"

as suggested, will there be a way to reference tbl if that is an unbound column in a field which uses a query?

[June7]

Don't understand that question. Field does not use a query. Field holds a single value. tbl is a calculated field that has value "tblSaleEntities" or "tblEmployees".

If the report is not based on UNION of both tables then the tbl field and criteria are not relevant.

[PeakH]

In tblCommissions design view, the field EntityID has a Data Type of number. In the field properties under the lookup tab, display control is Combo Box, the row source type is Table/Query and the Row Source is:

SELECT EntityID AS EntOREmpID, Entity AS EntOREmp, "tblSaleEntities" AS tbl
FROM tblSaleEntities
UNION ALL
SELECT EmployeeID, Employee, "tblEmployees"
FROM tblEmployees
WHERE ((Current = True) AND (SalesTeam = True));

Bound column is 1 [EntOREmpID]


I get the feeling this is not the right way to go about this, but I'm not sure of a better way.

[PeakH]

It may also be useful for me to let you know that I'm also putting this field on a form as a combo box, so that an order can be easily attributed to the employee or entity. The list will need to only display those that are current, but have the old records store information that no longer appears on the list.

[June7]

Exactly what are these 'other entities' that could receive commission? If they aren't employees, what are they?


You are correct, saving just the ID is not a complete solution. Because the ID is duplicated, Access will return two sets of data for each ID when both tables are included in query. One approach is to also save a value identifying the source and use IIf() expressions in textboxes to pull the correct values based on the source.

Also, I expect the combobox will be confused as to which alias value to display for existing records. Perhaps you need cascading (dependent) comboboxes. In first combobox select "Entity" or "Employee", second combobox presents appropriate list to choose from.


Another possible approach is a UNION query that has all the relevant fields (address info ?) and calculates a composite ID. Save that ID then include the UNION in any report RecordSource where you need to retrieve the payee info.

SELECT EntityID & "ENT" AS EntOrEmpID, Entity AS EntOrEmp, "tblSaleEntities" AS tbl, <additional information fields>
FROM tblSaleEntities
UNION ALL
SELECT EmployeeID & "EMP", Employee, "tblEmployees", <additional information fields>
FROM tblEmployees
WHERE ((Current = True) AND (SalesTeam = True));

Combobox can draw from that UNION query:
SELECT EntOrEmpID, EntOrEmp FROM [EntitiesUNION];


Regardless of approach, combobox displaying an alias with filtered records will not then display the alias value for the excluded items on a continuous or datasheet form. This is a known complication with using cascading (dependent) comboboxes and topic of numerous threads.

[PeakH]

Other entities are usually sales companies. So in their table I store information about the contract details.

I'm thinking that it might work to not use an alias. I like your idea of using cascading comboboxes, and maybe I can condense the other information into a single field. I'll only need the ID and the Name in the bound column since the table name is saved in the first combobox. Users will just have to ignore the number.

SELECT EntityID &" "& Entity AS EntorEmp, "tblSalesEntities"
FROM tblSaleEntities
UNION ALL
SELECT EmployeeID &" " & Employee, "tblEmployees"
FROM tblEmployees
WHERE ((Current = True) AND (SalesTeam = True));

Do you see any possible problems with this?

[June7]

I do not recommend including the entity/employee name as part of the identifier. But if you really want to get away from alias, unless there is a chance of multiple entity/employee with the same name, just save the entity/employee name without compounding with ID. Long text strings also perform poorly as indexes for sort/filter processes.

Names of people really should be split into multiple fields - LastName, FirstName, MidName.

Trying to reference organizations and people in the same context is always a challenge. Previously, I've only seen it with customer info. This is the first time I've seen employees mixed with organizations.

Another approach you might consider is not using autonumber ID as the PK/FK. Assign each entity/employee an identifier that would be unique across both tables. Maybe entities get 10000 through 89999 and employees get 90000 through 99999. Assigning these numbers can be managed by code. You will still have the alias lookup combobox but probably could get away without cascading aspect.

[PeakH]

I don't think there will be repeating entity/employee names so I took out the ID and am using the name and keeping the cascading comboboxes so that it's easier to look at all entities on one report and all employees on another if needed.

On my employee table I have a FirstName, LastName, and Employee (fullname). A bit redundant and probably not necessary.

I think this will work just as I need it to.
Thank you for all your help!