Query Quandry

[Matt Winberry]

Hello all,
I've got a form which links to a table, called Input, with a subform
which links to another table, UpdateTable. The subform table only contains 2
fields, one called UpdateStatus, and the other called ID. I'd like to be
able to use this subform to add comments to the StatusComments field of my
Input table. I've considered an append query, but I don't want to add a new
record to the table for each comment, just update the StatusComments field of
the record with new comments. I've also considered an update query, but I
don't want to update all of the records in a given table, just the records
that I'm trying to add comments to.

So I've got a subform, a button, two tables, and an open mind. What
kind of query should I use? Or would I be better off using the UpdateTable
table to store comments keyed on the ID, and then just modify my reports to
pull all of the comment fields for each record from that table? If I did
this, would it be possible to pull the ID field data from the parent table,
instead of forcing the user to type the ID in 2 different fields?

Thanks for putting up with the newbies.

 

[Evi]

You can copy data directly into the current control of the Main form easily
enough. For instance, if you are clicked on a record in the subform and want
to copy the contents of a field called MySubField into StatusComments the
code behind your button in the main form can say

Me.StatusComments = Me.[NameOfYourSubForm].Form.[MySubField]

or if you wanted to add what was in the subform field to what was already
stored in the StatusComments field you could use

Me.StatusComments = Me.Parent.StatusComments & " " &
Me.[NameOfYourSubForm].Form.[MySubField]



If you want to combine 2 fields in linked tables (eg you want to combine the
ClientName field from the ClientName table with the ClientAddress field in
the ClientAddress table, then you can add both tables to a query grid and in
a third column type

ClientDetails: [ClientName] & " " & [ClientAddress]

I have got an uncomfortable feeling from your comment about modifying your
report that there may be a knowledge gap somewhere.
I'm not sure what you mean by ' would it be possible to pull the ID field
data from the parent table, instead of forcing the user to type the ID in 2
different fields? '
I could be wrong but I suspect that you are trying to do something which can
be done much more easily.
Can you explain what you are trying to do and why the user should need to
'type the ID in 2 different fields'

Evi

 

[Matt Winberry]

Hi Evi,
Can I just email the file and explain what I'm trying to do to you? I
fully admit to a large knowledge gap, and this is starting to make my head
ache.

You can copy data directly into the current control of the Main form easily
enough. For instance, if you are clicked on a record in the subform and want
to copy the contents of a field called MySubField into StatusComments the
code behind your button in the main form can say

Me.StatusComments = Me.[NameOfYourSubForm].Form.[MySubField]

or if you wanted to add what was in the subform field to what was already
stored in the StatusComments field you could use

Me.StatusComments = Me.Parent.StatusComments & " " &
Me.[NameOfYourSubForm].Form.[MySubField]



If you want to combine 2 fields in linked tables (eg you want to combine the
ClientName field from the ClientName table with the ClientAddress field in
the ClientAddress table, then you can add both tables to a query grid and in
a third column type

ClientDetails: [ClientName] & " " & [ClientAddress]

I have got an uncomfortable feeling from your comment about modifying your
report that there may be a knowledge gap somewhere.
I'm not sure what you mean by ' would it be possible to pull the ID field
data from the parent table, instead of forcing the user to type the ID in 2
different fields? '
I could be wrong but I suspect that you are trying to do something which can
be done much more easily.
Can you explain what you are trying to do and why the user should need to
'type the ID in 2 different fields'

Evi

Hello all,
I've got a form which links to a table, called Input, with a subform
which links to another table, UpdateTable. The subform table only contains 2
fields, one called UpdateStatus, and the other called ID. I'd like to be
able to use this subform to add comments to the StatusComments field of my
Input table. I've considered an append query, but I don't want to add a new
record to the table for each comment, just update the StatusComments field of
the record with new comments. I've also considered an update query, but I
don't want to update all of the records in a given table, just the records
that I'm trying to add comments to.

So I've got a subform, a button, two tables, and an open mind. What
kind of query should I use? Or would I be better off using the UpdateTable
table to store comments keyed on the ID, and then just modify my reports to
pull all of the comment fields for each record from that table? If I did
this, would it be possible to pull the ID field data from the parent table,
instead of forcing the user to type the ID in 2 different fields?

Thanks for putting up with the newbies.

 

[Evi]

That would be a severe limitation on you Matt (as well as creeping into the
realms of a consultation. You would be losing the advantage of having the
real Access genii reading your emails and spotting angles I've missed. You
would be better off explaining
1. What your database is about (avoiding terminology that only someone in
the same line of business would understand)
2. What the table structure is - you could use a list like this
TblInput
InputID (Primary Key, Autonumber)
InputDate (date)
InputComment (text)
InputName (the name of the person who input the data)

TblOutput
OutputID (PK, Text)
InputID (Foreign Key from TblInput
OutputDate (date)
OutputComment

3.. What you want to achieve giving field names and data type when
appropriate (I all the text in OutputComments to be copied into
InputComments)

4. If possible, why 3. is necessary.

Evi

Hi Evi,
Can I just email the file and explain what I'm trying to do to you? I
fully admit to a large knowledge gap, and this is starting to make my head
ache.

You can copy data directly into the current control of the Main form easily
enough. For instance, if you are clicked on a record in the subform and want
to copy the contents of a field called MySubField into StatusComments the
code behind your button in the main form can say

Me.StatusComments = Me.[NameOfYourSubForm].Form.[MySubField]

or if you wanted to add what was in the subform field to what was already
stored in the StatusComments field you could use

Me.StatusComments = Me.Parent.StatusComments & " " &
Me.[NameOfYourSubForm].Form.[MySubField]



If you want to combine 2 fields in linked tables (eg you want to combine the
ClientName field from the ClientName table with the ClientAddress field in
the ClientAddress table, then you can add both tables to a query grid and in
a third column type

ClientDetails: [ClientName] & " " & [ClientAddress]

I have got an uncomfortable feeling from your comment about modifying your
report that there may be a knowledge gap somewhere.
I'm not sure what you mean by ' would it be possible to pull the ID field
data from the parent table, instead of forcing the user to type the ID in 2
different fields? '
I could be wrong but I suspect that you are trying to do something which can
be done much more easily.
Can you explain what you are trying to do and why the user should need to
'type the ID in 2 different fields'

Evi

Hello all,
I've got a form which links to a table, called Input, with a subform
which links to another table, UpdateTable. The subform table only contains 2
fields, one called UpdateStatus, and the other called ID. I'd like to be
able to use this subform to add comments to the StatusComments field of my
Input table. I've considered an append query, but I don't want to add

a
new

record to the table for each comment, just update the StatusComments

field
of

the record with new comments. I've also considered an update query, but I
don't want to update all of the records in a given table, just the records
that I'm trying to add comments to.

So I've got a subform, a button, two tables, and an open mind. What
kind of query should I use? Or would I be better off using the UpdateTable
table to store comments keyed on the ID, and then just modify my

reports
to

pull all of the comment fields for each record from that table? If I did
this, would it be possible to pull the ID field data from the parent table,
instead of forcing the user to type the ID in 2 different fields?

Thanks for putting up with the newbies.

 

[Matt Winberry]

Sorry for the delayed response, Evi, for some reason my mail client didn't
notify me of a reply. I agree, by letting someone else do it for me, I'm
robbing myself. I've got a rudimentary knowledge of DB's, and am working on
a project that I had hoped would challenge me. It is, (boy is it ever!) but
I'm finding that my knowledge gap is definitely leaving me with my head
banging against a wall at times.

I've been able to make some progress. I've been able to get around one of
my original hurdles - "If I did this, would it be possible to pull the ID
field data from the parent table, instead of forcing the user to type the ID
in 2 different fields?" Today I've learned about one to many relationships,
and "cascade update related fields". So this changes my question a little
bit.

I've got a DB with 2 tables, [Master] and [Updates]. Each of those tables
has 2 fields, [ID] and [Descr]. The tables are linked One [Master].[ID] (PK)
to Many [Updates].[ID] . Referential integrity is enforced, with "cascade
update related fields" selected. I've also got a form [Test] with the
[Master].[ID] field bound, and a subform [Updates subform] with 2 fields
bound to [Updates].[ID] and [Updates].[Descr]. When I enter an ID in
[Master].[ID] it now updates [Updates].[ID], which is great. One step down,
hopefully only one more left to go.

What I'd like to do is be able to enter text in the [Updates].[Descr] field
and, if a record already exists for a given ID, create a new record. I hope
to ultimately be able to run a report which will allow me to group records
pulling [Master].[ID] and showing all [Updates].[Descr] entries for that ID.
There are other fields that I'd be putting in the report, but they'd all pull
from the [Master] table, so I don't have any worries about sorting on them.

I've been toying with queries, but Update queries haven't been doing what I
want them to do. I'm beginning to toy with append queries, but haven't hit
that point in my sandbox yet. Thanks, Evi!

That was probably more verbose than I could have been, sorry about that.
I'm making progress, but still stuck on how to

 

[Matt Winberry]

Small breakthrough. Using an append query on the updates table linked to a
button on the form, I can add the record to the table. This only works for
records that don't already have an ID in the Master table, though, because of
the primary key constraint. I may need to create 2 forms, one to add the
initial record to the project, and one to update status on the project, and
remove the relationship between the 2 tables. I should still be able to
report on both tables with the right criteria.

That would be a severe limitation on you Matt (as well as creeping into the
realms of a consultation. You would be losing the advantage of having the
real Access genii reading your emails and spotting angles I've missed. You
would be better off explaining
1. What your database is about (avoiding terminology that only someone in
the same line of business would understand)
2. What the table structure is - you could use a list like this
TblInput
InputID (Primary Key, Autonumber)
InputDate (date)
InputComment (text)
InputName (the name of the person who input the data)

TblOutput
OutputID (PK, Text)
InputID (Foreign Key from TblInput
OutputDate (date)
OutputComment

3.. What you want to achieve giving field names and data type when
appropriate (I all the text in OutputComments to be copied into
InputComments)

4. If possible, why 3. is necessary.

Evi

Hi Evi,
Can I just email the file and explain what I'm trying to do to you? I
fully admit to a large knowledge gap, and this is starting to make my head
ache.

You can copy data directly into the current control of the Main form easily
enough. For instance, if you are clicked on a record in the subform and want
to copy the contents of a field called MySubField into StatusComments the
code behind your button in the main form can say

Me.StatusComments = Me.[NameOfYourSubForm].Form.[MySubField]

or if you wanted to add what was in the subform field to what was already
stored in the StatusComments field you could use

Me.StatusComments = Me.Parent.StatusComments & " " &
Me.[NameOfYourSubForm].Form.[MySubField]



If you want to combine 2 fields in linked tables (eg you want to combine the
ClientName field from the ClientName table with the ClientAddress field in
the ClientAddress table, then you can add both tables to a query grid and in
a third column type

ClientDetails: [ClientName] & " " & [ClientAddress]

I have got an uncomfortable feeling from your comment about modifying your
report that there may be a knowledge gap somewhere.
I'm not sure what you mean by ' would it be possible to pull the ID field
data from the parent table, instead of forcing the user to type the ID in 2
different fields? '
I could be wrong but I suspect that you are trying to do something which can
be done much more easily.
Can you explain what you are trying to do and why the user should need to
'type the ID in 2 different fields'

Evi







Hello all,
I've got a form which links to a table, called Input, with a subform
which links to another table, UpdateTable. The subform table only
contains 2
fields, one called UpdateStatus, and the other called ID. I'd like to be
able to use this subform to add comments to the StatusComments field of my
Input table. I've considered an append query, but I don't want to add a
new
record to the table for each comment, just update the StatusComments field
of
the record with new comments. I've also considered an update query, but I
don't want to update all of the records in a given table, just the records
that I'm trying to add comments to.

So I've got a subform, a button, two tables, and an open mind. What
kind of query should I use? Or would I be better off using the
UpdateTable
table to store comments keyed on the ID, and then just modify my reports
to
pull all of the comment fields for each record from that table? If I did
this, would it be possible to pull the ID field data from the parent
table,
instead of forcing the user to type the ID in 2 different fields?

Thanks for putting up with the newbies.

 

[Evi]

Are you sure you've described your tables correctly Matt?
You say that the Master table has

Master.ID
Master.Descr

while the Many table has
Updates.ID
Updates.Descr

That's not the correct structure for a one to many.

Firstly, before you put any more work into it, rename those fields and be
prepared to mend your queries, reports and forms. they will cause no end of
hassle and confusion if you keep them as they are. (you can put whatever you
want on the visible labels in forms and fields)

eg

TblMaster
MasterID (I'll presume you are using a number field for this, perhaps an
autonumber)
MastDescr

TblUpdate
UpdateID
UpdateDescr
MasterID (this is the field that should be linked from TblMaster - If
MasterID is a number field, this should also be a number field - a Long
Integer)


Evi

Sorry for the delayed response, Evi, for some reason my mail client didn't
notify me of a reply. I agree, by letting someone else do it for me, I'm
robbing myself. I've got a rudimentary knowledge of DB's, and am working on
a project that I had hoped would challenge me. It is, (boy is it ever!) but
I'm finding that my knowledge gap is definitely leaving me with my head
banging against a wall at times.

I've been able to make some progress. I've been able to get around one of
my original hurdles - "If I did this, would it be possible to pull the ID
field data from the parent table, instead of forcing the user to type the ID
in 2 different fields?" Today I've learned about one to many relationships,
and "cascade update related fields". So this changes my question a little
bit.

I've got a DB with 2 tables, [Master] and [Updates]. Each of those tables
has 2 fields, [ID] and [Descr]. The tables are linked One [Master].[ID] (PK)
to Many [Updates].[ID] . Referential integrity is enforced, with "cascade
update related fields" selected. I've also got a form [Test] with the
[Master].[ID] field bound, and a subform [Updates subform] with 2 fields
bound to [Updates].[ID] and [Updates].[Descr]. When I enter an ID in
[Master].[ID] it now updates [Updates].[ID], which is great. One step down,
hopefully only one more left to go.

What I'd like to do is be able to enter text in the [Updates].[Descr] field
and, if a record already exists for a given ID, create a new record. I hope
to ultimately be able to run a report which will allow me to group records
pulling [Master].[ID] and showing all [Updates].[Descr] entries for that ID.
There are other fields that I'd be putting in the report, but they'd all pull
from the [Master] table, so I don't have any worries about sorting on them.

I've been toying with queries, but Update queries haven't been doing what I
want them to do. I'm beginning to toy with append queries, but haven't hit
that point in my sandbox yet. Thanks, Evi!

That was probably more verbose than I could have been, sorry about that.
I'm making progress, but still stuck on how to