VLOOKUP Alternative

[djmixer135]

Hi there,

I have a table containing a number of organisations. One of the fields on this table contains each organisation's postcode.

There is also a second table containing a list of postcode districts and the name of a person whom that district is allocated / assigned to.

Please can someone help me to automatically assign an individual from the district table to the organisation table. As the district allocations change, these changes should automatically update in the organisation table.



The third table below shows my desired final result.

Many thanks in advanced!

ID	Name	Postcode
1	Org1	AL10 8TU
2	Org2	W1 8HA
3	Org3	N10 69A

District	Allocated to
W1	John
N10	Bill
AL10	John
AL11	John
AL12	John

ID	Name	Postcode	Allocation
1	Org1	AL10 8TU	John
2	Org2	W1 8HA	John
3	Org3	N10 69A	Bill

[JoeM]

One way:

Create a query on the first table which includes a calculated field to strip off the District from the Postcode.
Then create a new query that joins the first query to the second table on the District fields, and returns the Fields that you want.

Alternatively, you could use a DLOOKUP. See: https://www.techonthenet.com/access/...in/dlookup.php
(you would just need to use a calculation to strip District off of Postcode here too).

[Minty]

Provided your postcodes are properly formed you can do this in one query;

SELECT ID, Name, Postcode, AssignedTo 
FROM Organisations , Allocations 
WHERE District = Left([Postcode],Len([Postcode])-4) AND Postcode Is Not Null

Caveats - Correctly formed postcodes, and your two tables are called Allocations and Organisations

[djmixer135]

Provided your postcodes are properly formed you can do this in one query;

SELECT ID, Name, Postcode, AssignedTo 
FROM Organisations , Allocations 
WHERE District = Left([Postcode],Len([Postcode])-4) AND Postcode Is Not Null

Caveats - Correctly formed postcodes, and your two tables are called Allocations and Organisations

Thank you both for your responses - both look really useful.

Minty - I like the idea of being able to run this all in a single query to eliminate the requirement for an additional field showing the first half of the postcode.

I am quite new to Access so sorry for any stupid questions. Does this mean that my 'working table' would be the query or will this directly insert the names of the individuals on to the organisation table?

Many thanks again!

[djmixer135]

SELECT ID, Name, Postcode, AssignedTo 
FROM Organisations , Allocations 
WHERE District = Left([Postcode],Len([Postcode])-4) AND Postcode Is Not Null

I guess I need to just convert this to an update query and run it every time the allocations change? Any ideas on how I can achieve this?

[JoeM]

Does this mean that my 'working table' would be the query or will this directly insert the names of the individuals on to the organisation table?

I guess I need to just convert this to an update query and run it every time the allocations change? Any ideas on how I can achieve this?

No! It is a violation of the rules of normalization to have table fields that are dependent upon other fields. It can undermine the dynamic nature of the database.

Since the values can be gotten by the query above, there is no reason to store then back at the table level. A general rule of thumb is to never store a value that can be calculated easily on-the-fly.
Pretty much anything that you would use a Table for (control source of Form, Report, or Export), you can use a Query for. So there should seldom be a reason to have to store it back at the Table level.
(There are some execptions, like if you are creating a historical table to track the value of changing fields at a specific point in time).

[Minty]

Joe's advice is spot on.

By using the query as your source for the form or report, you never need worry about keeping things up to date.
You are always using your tables current data.

[djmixer135]

Hi both,

Whilst I understand what you are saying, I cannot add additional information to records from a query table as it says 'This Recordset is not updateable'. The purpose of this exercise is to allow me to filter by an individual's name and THEN add notes to those records as required.

Hopefully that makes sense. Do you have any futher recommendations?

Thanks for all your helps so far.

Kind regards,


Dominic Muir

[Minty]

Left join this query to the table you want to update in another query.

That should give you and updatable record set.
You can then display the Allocation Name, but make it so that it can't be edited.

[djmixer135]

Hi Minty,

That sounds like the perfect solution! Please can you explain how I would go about this? Sounds very complicated

[Minty]

Do you have a small sample of your data and the form you are trying to use.
If you can compact and repair it, then ZIP it (Change any personal data, dummy data is fine) and post it here it will be a 2 minute job to do then you can see.

Alternatively, save the query above as qryAreaAlocations.

Create a new query in the designer, add organisations table and qryAreaAlocations.

Drag the ID field from tblOrganisations to the ID field in the query. It should join them with a line.
double click the join line and select the option to show all the organisations and only the matching qry records.

Pull all the Organisation fields into the qry output, and the Assigned to field from the qry .

Try this query as your forms record source.

[djmixer135]

Got it! Thanks again for all your help on this.

[Minty]

You are welcome - good luck with your project.