Dlookup Question 1

[TanmedIT]

I have a table that has the following fields:

Report_Number
Requirement_Number1
Requirement_Number2
Requirement_Number3
Requirement_Number4
Requirement_Number5
Requirement_Number6
Requirement_Number7
Requirement_Number8
Requirement_Number9
Requirement_Number10
Requirement_Number11
Requirement_Number12
Requirement_Number13
Requirement_Number14
Requirement_Number15
Requirement_Number16
Requirement_Number17
Requirement_Number18
Requirement_Number19
Requirement_Number20
Revision1
Revision2
Revision3
Revision4
Revision5
Revision6
Revision7
Revision8
Revision9
Revision10
Revision11
Revision12
Revision13
Revision14
Revision15
Revision16
Revision17
Revision18
Revision19
Revision20
Report Focus

I need to have a dlookup in a report that will give me the right Revision# that Corresponds to a particular Requirement#. Currently I have one, but it returns -1 as an answer. I am a newbie, so laugh softly. Here is my dlookup function:

=DLookUp(("[Revision1]" Or "[Revision2]" Or "[Revision3]" Or "[Revision4]" Or "[Revision5]" Or "[Revision6]" Or "[Revision7]" Or "[Revision8]" Or "[Revision9]" Or "[Revision10]" Or "[Revision11]" Or "[Revision12]" Or "[Revision13]" Or "[Revision14]" Or "[Revision15]" Or "[Revision16]" Or "[Revision17]" Or "[Revision18]" Or "[Revision19]" Or "[Revision20]&quot,"Report_Entry_Table",("[Requirement Number] =" & [Reports]![R - Database Report (Report Numbers)]![Requirement Number]) And ("[Report_Number] =" & [Reports]![R - Database Report (Report Numbers)]![Report_Number]))

 

[DougP]

you need to re-think you database design

If you have 20 requirements then create a second table

main Table fields
ID Description Date created
123 Big part 10/1/01
234 Small part 10/2/01
689 Other part 9/28/01

Second table is the requirements table fields
ID Requirement Date of Requirement
123 this is requirement 1 10/5/01
123 this is requirement 2 10/6/01
123 this is requirement 3 10/7/01
234 this is requirement 1 10/3/01
234 this is requirement 2 10/4/01
689 this is requirement 1 9/29/01
123 this is requirement 4 10/5/01

Third Table
ID Revision Date Revised
123 this is revision 1 10/5/01
123 this is revision 2 10/6/01
689 this is revision 1 10/3/01
234 this is revision 1 10/5/01
234 this is revision 2 10/7/01
689 this is revision 2 10/5/01
123 this is revision 3 10/9/01

So all Revisions are kept in a separate Revsion table and all Requirements are in a separate Requirement table.
It 100 times easier to find anything. That's called "normalization" in database design.

OK then to find a last revision or requirement is simple
you have the ID number then sort the table backwards by Date and the last one is on top.

Select * From [requirements table] Where ID = 123 Order By [Date of Requirement] DESC;

Select * From [revisions table] Where ID = 123 Order By [Date of revision] DESC;

Or the Dlookup becomes just as simple

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[TanmedIT]

I completely understand the Normalization process (which is a huge pain). But with the form layout that the user wants, I am in a bind. I am e-mailing you the layout and maybe you can help me find out an easier way to do it.

 

[TanmedIT]

Whats you e-mail Address? That would help a little