Access Multiple Vertical Lookup Query

[elusiveluderick]

Hi,



I work for a Local Council and trying to streamline the DA process a bit by using Access. I have two tables.

tbl_Conditions which has ConditionID(PKey) and ConditionDescription Fields.

tbl_DA has the field DA (PKey) followed by numerous conditions for simplification let's assume I have only five conditions each with there own field. SC363, SC449, SC106, S105, SC32.

For each DA in tbl_DA I go through and if condition SC363 applies I input SC363 if it does not apply I input NO. This is repeated for other conditions.

Everything above this point I have working like a charm. However from here on in I am seriously struggling been stuck for two days with no success!!

I would then like to create a new table ready for merging into word. The table would be the following

tbl_DAMerge would have fields DA (Pkey), SC1ConditionDescription, SC2ConditionDescription....., SC5ConditionDescription.

The condition descriptions would be coming from tbl_Conditions the description would only be inputted if the condition applied otherwise it would be left blank.

I have tried Dlookup, and update queries with multiple joins but it always comes back doing something random. If anybody can help would be greatly appreciative.

Thanks,

Ron

[recyan]

Perhaps, below table structures would be preferable :

tblConditions

ConditionID - PK
ConditionDescription

tblDA

DA - PK
DA_Description


tblDAConditions

DA_Condition_ID - PK
fk_DA - FK
fk_ConditionID - FK

Thanks

[elusiveluderick]

Thanks for the comment. This is a logical solution however is there a way to automatically add 5 entries into the tblDAConditions whenever you add a single entry to the tblDA. I ask this because If lets say I had 100 conditions against every DA then this would not be feasable unless automated?

[June7]

Yes, records could automatically be added but with what data?

[elusiveluderick]

DAForum.mdb

I am trying to produce a final table that for each DA has all the conditions descriptions from the conditions table that apply to it ready to mail merge and for those conditions that do not apply a blank field is produced.
I hope the attached databse makes it clearer. Previous post made the good point to make a table that connects the DA's to the conditions however it is not viable to do this manually and am not sure how to go about autmatiing it.

[June7]

The tbl_Conditions has a field ConditionCombined with no data. What is this field for? Will the values of ConditionTitle and ConditionDescription be the same for every mailmerge? What determines which Conditions apply to each DA?

This query will show records of each Condition for each DA.
SELECT tbl_DA.*, tbl_Conditions.* FROM tbl_DA, tbl_Conditions;

[elusiveluderick]

I have simplified the tables for uploading to forum. I was using the ConditionCombined field to concatenate the ConditionID, Title and Description ready for Mail Merge. I have deleted data to make the database filesize smaller and not filled them back in.

The query you put forward comes back with 9 records I ideally want 1 record. For field SC363 I would like the condition descriptiion for SC363 from tbl_conditions and the same for the other conditions. As a result of it all being against one record I can then input straight into word. Hope it is becoming a bit clearer.
Thanks alot for the ideas so far.

[June7]

Okay, the DA table is not normalized which is why the two tables can't be easily joined on the common value of ConditionID. The query would have to join tbl_Conditions to each of the condition fields of DA table. Not a nice picture but does work.

SELECT tbl_DA*, tbl_Conditions.ConditionDescription, tbl_Conditions_1.ConditionDescription, tbl_Conditions_2.ConditionDescription, tbl_Conditions_3.ConditionDescription, tbl_Conditions_4.ConditionDescription, tbl_Conditions_5.ConditionDescription, tbl_Conditions_6.ConditionDescription FROM tbl_Conditions AS tbl_Conditions_6 RIGHT JOIN (tbl_Conditions AS tbl_Conditions_5 RIGHT JOIN (tbl_Conditions AS tbl_Conditions_4 RIGHT JOIN (tbl_Conditions AS tbl_Conditions_3 RIGHT JOIN (tbl_Conditions AS tbl_Conditions_2 RIGHT JOIN (tbl_Conditions AS tbl_Conditions_1 RIGHT JOIN (tbl_Conditions RIGHT JOIN tbl_DA ON tbl_Conditions.ConditionID = tbl_DA.SC363) ON tbl_Conditions_1.ConditionID = tbl_DA.SC449) ON tbl_Conditions_2.ConditionID = tbl_DA.SC106) ON tbl_Conditions_3.ConditionID = tbl_DA.SC105) ON tbl_Conditions_4.ConditionID = tbl_DA.SC32) ON tbl_Conditions_5.ConditionID = tbl_DA.SC116) ON tbl_Conditions_6.ConditionID = tbl_DA.SC31;

Alternative is DLookup. Example for one of the conditions:

SELECT tbl_DA.*, DLookUp("ConditionDescription","tbl_Conditions","C onditionID='" & [SC363] & "'") AS SC363Desc FROM tbl_DA;

Repeat the DLookup expression in the query designer for each condition field of the DA table. Domain aggregate functions can be very slow for large datasets.

[elusiveluderick]

Absolutley Fantastic Exaclty what I was after. I was trying to use this for a whole day and was sending me crazy. Could you please explain what is going on in the DLookup in the criteria Section of the function. That is "ConditionID="'&[SC363] &"'")
Questions are:
Why is there both quotation marks " " and apostraphies '. What are the &'s for ??

Thanks so much for your help.

[June7]

The WHERE CONDITION argument of domain aggregate functions follows syntax rules of the WHERE clause of an SQL statement.

Quote marks define a literal string.

The ampersands are concatenating the content of a variable (in this case, the table field) into the WHERE CONDITION argument.

The apostrophes are required because the field is text data type. The apostrophes delimit the text criteria as a continuous string value, so that spaces, if any, don't break up the compiled statement. Date values would need # which assure that the value is treated as a date and not just a set of characters. Number data type does not require delimiter.

[elusiveluderick]

Ok i sort of understand but not fully but as long as it works I'm stoked. If your ever in Wollongong Australia you've got a cold beer waiting.
Thanks,
Ron