DLookup in CrossTabQuery form

[Jim Dudley]

I have a form based on a crosstab Query.

I want to add some additional data from another table based on a key
identifier matching the same record. The Primary Key also exist in my source
table for lookup.

The Dlookup returns the correct data on the first record but also returns
the same data for all other records in the query.

The dLookup is contained in an unbound text control in my form.

=NZ(DLookUp("[L1]","WStuContacts","[Snum]=SNum"))

[Snum] resides in the form from the query and [SNum] is also a Primary Key
in the WStuContacts Table.

Any suggestions would be appreciated. I am a beginner at this....

Thank you.

 

[KARL DEWEY]

I would not use DLookup but join in the crosstab query.

BTW Access will consider [Snum] the same as [SNum] unless you define which
table they are in.

 

[Jim Dudley]

Thanks Karl,

I had added the fields that I wanted through an inner join earlier but when
I looked at the "Add Existing Fields" Control they did not appear. They are
there now. It seemed not to come through at first. Either I did not save
something or forgot to refresh maybe. The end result is your method works
partially.

Having been able now to view the data from this other table, I may want to
update that data from this form (if possible). Is there another method of
binding the form control to the second table so that I can edit the table
from this form?

This is my Crosstab Query in SQL view:

TRANSFORM Sum(WCredits.Credits) AS SumOfCredits
SELECT WCredits.SNum, WCredits.LName, WCredits.FName, WCredits.Init,
WCredits.UTSCEmail, WStuContacts.L1, WStuContacts.DateL1,
Sum(WCredits.Credits) AS [Total Of Credits]
FROM WStuContacts INNER JOIN WCredits ON WStuContacts.SNum = WCredits.SNum
GROUP BY WCredits.SNum, WCredits.LName, WCredits.FName, WCredits.Init,
WCredits.UTSCEmail, WStuContacts.L1, WStuContacts.DateL1
ORDER BY WCredits.SNum
PIVOT WCredits.Type;

The reason for wanting this is:

This form is the most common rerference point to our data. When a student
achieves a certain # of credits they earn a certificate. When we see that
they have earned a certificate we can issue it then and there and update the
underlying table at the same time instead of having to switch forms.

Look forward to your response.

Thank you.

I would not use DLookup but join in the crosstab query.

BTW Access will consider [Snum] the same as [SNum] unless you define which
table they are in.

I have a form based on a crosstab Query.

I want to add some additional data from another table based on a key
identifier matching the same record. The Primary Key also exist in my source
table for lookup.

The Dlookup returns the correct data on the first record but also returns
the same data for all other records in the query.

The dLookup is contained in an unbound text control in my form.

=NZ(DLookUp("[L1]","WStuContacts","[Snum]=SNum"))

[Snum] resides in the form from the query and [SNum] is also a Primary Key
in the WStuContacts Table.

Any suggestions would be appreciated. I am a beginner at this....

Thank you.

 

[Chegu Tom]

"[Snum]=SNum"
I assum that [Snum] is a field in WStuContacts ans SNum is a variable with
the key inforation that you want to find or a control on your form and that
both are numeric
I would rename the control name for SNum to be something distinct like
txtSNum (to avoid snum confusion)

"[Snum]=" & txtSnum

if they are text fields

"[Snum]='" & txtSnum &"'" (notice single and double quotes

 

[KARL DEWEY]

You cannot update a query that is grouping. I do not know what field you
want to update but I would use a left join instead of inner and use a subform
to update the table.
I think that the form/subform Master/Child links would use WStuContacts.SNum
and WCredits.SNum.

Thanks Karl,

I had added the fields that I wanted through an inner join earlier but when
I looked at the "Add Existing Fields" Control they did not appear. They are
there now. It seemed not to come through at first. Either I did not save
something or forgot to refresh maybe. The end result is your method works
partially.

Having been able now to view the data from this other table, I may want to
update that data from this form (if possible). Is there another method of
binding the form control to the second table so that I can edit the table
from this form?

This is my Crosstab Query in SQL view:

TRANSFORM Sum(WCredits.Credits) AS SumOfCredits
SELECT WCredits.SNum, WCredits.LName, WCredits.FName, WCredits.Init,
WCredits.UTSCEmail, WStuContacts.L1, WStuContacts.DateL1,
Sum(WCredits.Credits) AS [Total Of Credits]
FROM WStuContacts INNER JOIN WCredits ON WStuContacts.SNum = WCredits.SNum
GROUP BY WCredits.SNum, WCredits.LName, WCredits.FName, WCredits.Init,
WCredits.UTSCEmail, WStuContacts.L1, WStuContacts.DateL1
ORDER BY WCredits.SNum
PIVOT WCredits.Type;

The reason for wanting this is:

This form is the most common rerference point to our data. When a student
achieves a certain # of credits they earn a certificate. When we see that
they have earned a certificate we can issue it then and there and update the
underlying table at the same time instead of having to switch forms.

Look forward to your response.

Thank you.

I would not use DLookup but join in the crosstab query.

BTW Access will consider [Snum] the same as [SNum] unless you define which
table they are in.

I have a form based on a crosstab Query.

I want to add some additional data from another table based on a key
identifier matching the same record. The Primary Key also exist in my source
table for lookup.

The Dlookup returns the correct data on the first record but also returns
the same data for all other records in the query.

The dLookup is contained in an unbound text control in my form.

=NZ(DLookUp("[L1]","WStuContacts","[Snum]=SNum"))

[Snum] resides in the form from the query and [SNum] is also a Primary Key
in the WStuContacts Table.

Any suggestions would be appreciated. I am a beginner at this....

Thank you.

 

[Jim Dudley]

Thanks Karl,

I will work on that tomorrow, never used a sub-form before but I have a
reference book on Access 2007 and one of the chapters deals with creating
sub-forms. I will let you know how I make out....

JD

You cannot update a query that is grouping. I do not know what field you
want to update but I would use a left join instead of inner and use a subform
to update the table.
I think that the form/subform Master/Child links would use WStuContacts.SNum
and WCredits.SNum.

Thanks Karl,

I had added the fields that I wanted through an inner join earlier but when
I looked at the "Add Existing Fields" Control they did not appear. They are
there now. It seemed not to come through at first. Either I did not save
something or forgot to refresh maybe. The end result is your method works
partially.

Having been able now to view the data from this other table, I may want to
update that data from this form (if possible). Is there another method of
binding the form control to the second table so that I can edit the table
from this form?

This is my Crosstab Query in SQL view:

TRANSFORM Sum(WCredits.Credits) AS SumOfCredits
SELECT WCredits.SNum, WCredits.LName, WCredits.FName, WCredits.Init,
WCredits.UTSCEmail, WStuContacts.L1, WStuContacts.DateL1,
Sum(WCredits.Credits) AS [Total Of Credits]
FROM WStuContacts INNER JOIN WCredits ON WStuContacts.SNum = WCredits.SNum
GROUP BY WCredits.SNum, WCredits.LName, WCredits.FName, WCredits.Init,
WCredits.UTSCEmail, WStuContacts.L1, WStuContacts.DateL1
ORDER BY WCredits.SNum
PIVOT WCredits.Type;

The reason for wanting this is:

This form is the most common rerference point to our data. When a student
achieves a certain # of credits they earn a certificate. When we see that
they have earned a certificate we can issue it then and there and update the
underlying table at the same time instead of having to switch forms.

Look forward to your response.

Thank you.

I would not use DLookup but join in the crosstab query.

BTW Access will consider [Snum] the same as [SNum] unless you define which
table they are in.

:

I have a form based on a crosstab Query.

I want to add some additional data from another table based on a key
identifier matching the same record. The Primary Key also exist in my source
table for lookup.

The Dlookup returns the correct data on the first record but also returns
the same data for all other records in the query.

The dLookup is contained in an unbound text control in my form.

=NZ(DLookUp("[L1]","WStuContacts","[Snum]=SNum"))

[Snum] resides in the form from the query and [SNum] is also a Primary Key
in the WStuContacts Table.

Any suggestions would be appreciated. I am a beginner at this....

Thank you.

 

[Jim Dudley]

Thanks, I will give your suggestion a try...
JD

"[Snum]=SNum"
I assum that [Snum] is a field in WStuContacts ans SNum is a variable with
the key inforation that you want to find or a control on your form and that
both are numeric
I would rename the control name for SNum to be something distinct like
txtSNum (to avoid snum confusion)

"[Snum]=" & txtSnum

if they are text fields

"[Snum]='" & txtSnum &"'" (notice single and double quotes

I have a form based on a crosstab Query.

I want to add some additional data from another table based on a key
identifier matching the same record. The Primary Key also exist in my
source
table for lookup.

The Dlookup returns the correct data on the first record but also returns
the same data for all other records in the query.

The dLookup is contained in an unbound text control in my form.

=NZ(DLookUp("[L1]","WStuContacts","[Snum]=SNum"))

[Snum] resides in the form from the query and [SNum] is also a Primary Key
in the WStuContacts Table.

Any suggestions would be appreciated. I am a beginner at this....

Thank you.