DLookup all fields with same criteria

[AccessNewbie2007]

Hello,

I have an inquiry about using dlookup to retrive all fields from a table that are using the same criteria. Here's the table structure

Table1:
ID (Autonumber)
Number
Code
Grade

I have the following data:
Number Code Grade
4 15 68
4 52 78
4 17 69
5 85 83
5 28 17

I am using this to retrive values for number 4 but I only get the first row
DLookup("Code", "Table1", "[Number] = 4")
DLookup("Grade", "Table1", "[Number] = 4")

I get only as a result 15 and 68, how can I retrive all the rows that have Number 4?

Thanks in advance for your help

 

[RuralGuy]

DLookup() *only* returns one value. You will need a query to return a recordset with all of the records that match your criteria.

 

[AccessNewbie2007]

I cannot use a simply query because the queried data needs to be stored in a seperate table, I tried an update query and append but it doesn't work.

The second table has one additional field that will be filled from a form text box

 

[RuralGuy]

You are planning to store identical data in more than one table in a relational database? Do you know how to use DAO and walk through the returned recordset with MoveNext?

 

[AccessNewbie2007]

yes exactly but honestly I don't know how to use DAO your help will be very much appreciated

 

[RuralGuy]

What is the SQL for the append query you tried?

 

[AccessNewbie2007]

I tried now again the append query while I added manually the 1st record to the table and it works successfuly. If I delete all records, the append query didn't work. Any suggestions for that or maybe I must keep one record for the append to work?

 

[RuralGuy]

As I said in post # 6:

What is the SQL for the append query you tried?

 

[AccessNewbie2007]

INSERT INTO Table2 ( Code, Grade, User )
SELECT Table1.Code, Table1.Grade, [Forms]![frmTest]![User] AS Expr1
FROM Table1, Table2
WHERE (((Table1.Number)=[Forms]![frmTest]![Number]));

 

[RuralGuy]

Why is Table2 in the FROM clause of the query?

INSERT INTO Table2 ( Code, Grade, User )
SELECT Table1.Code, Table1.Grade, [Forms]![frmTest]![User] AS Expr1
FROM Table1, [COLOR="Red"][B]Table2[/B][/COLOR]
WHERE (((Table1.Number)=[Forms]![frmTest]![Number]));

 

[DannySeager]

because the queried data needs to be stored in a seperate table,

Why? Why are you storing this information in 2 seperate places?

 

[Moniker]

He's on a triple dog dare to denormalize his DB.

 

[AccessNewbie2007]

Because the first table contain the default values and the 2nd table have each User with the given values, I query the values based on the Number criteria from the 1st table and store them in the users table (rather then manually input them)...
Do you have an alternative method?

 

[RuralGuy]

I believe if you remove the reference to Table2 in the FROM clause, your code will also work when Table2 has no records. I am pretty sure that the reference to Table2 is not needed in the SQL you posted.

 

[AccessNewbie2007]

thank you