Comments Textbox

[LFC]

I have a form in which I have an unbound textbox so that users can add
comments about the record. I type something into the textbox on one of the
records and it fills whatever I wrote into all other records. I want what
gets typed to just stay on the one record. Any ideas?

 

[Dirk Goldgar]

I have a form in which I have an unbound textbox so that users can add
comments about the record. I type something into the textbox on one of
the
records and it fills whatever I wrote into all other records. I want what
gets typed to just stay on the one record. Any ideas?

The obvious answer is to bind the text box to a field in the record. Is
there a reason that the text box has to be unbound?

 

[ghetto_banjo]

create a "comments" field in your table (if its not there already),
and then make the textbox bound to this field. otherwise it is just
on the form and not being stored anywhere.

 

[John W. Vinson]

I have a form in which I have an unbound textbox so that users can add
comments about the record. I type something into the textbox on one of the
records and it fills whatever I wrote into all other records. I want what
gets typed to just stay on the one record. Any ideas?

That's what unbound MEANS. It's not stored anywhere, and it's not associated
with any record.

If you want it associated with a record, you'll need to store it in a table
and bind that stored field to a textbox on your form.

If you will have one and only one comment per record, you can just add a Text
or Memo field to the table. However, it's pretty common to want to allow
multiple comments; this can be done by adding a Comments table, with fields
for a foreign key linked to the record's Primary Key, a Memo or Text field to
store the comment, and perhaps a date/time field defaulting to Now() to
timestamp the comment and a field to store the identity of the person making
the comment.

 

[LFC]

I had tried making it bound to a table, but since I'm querying from multiple
tables if I try to type into a comments field it won't let me because it says
the field is not updateable.

 

[LFC]

I found out the reason I couldn't edit the field was because I am using
"group by" in my query. Is there a criteria or some other way to do the same
thing "group by" would do? I have serial numbers in 3 different tables and
need to query for when a serial number is in all three tables.

 

[Dirk Goldgar]

I found out the reason I couldn't edit the field was because I am using
"group by" in my query. Is there a criteria or some other way to do the
same
thing "group by" would do? I have serial numbers in 3 different tables
and
need to query for when a serial number is in all three tables.

What is the current SQL of the query, and what -- in plain words -- is it
that you want to show on your form?

 

[LFC]

SELECT QIT_LWH_IMP_tbl.PIN, QIT_DTAC_IMP_tbl.Created, QIT_LWH_IMP_tbl.[Build
Date], QIT_LWH_IMP_tbl.[Process Date], QIT_Z3_QNOTE_tbl.Comments,
QIT_DTAC_IMP_tbl.[Optional 1], QIT_Z3_QNOTE_tbl.[Where Found],
QIT_Z3_QNOTE_tbl.Reviewed, QIT_Z3_QNOTE_tbl.[Coorelation Confirmed]
FROM QIT_Z3_QNOTE_tbl INNER JOIN (QIT_DTAC_IMP_tbl INNER JOIN
QIT_LWH_IMP_tbl ON QIT_DTAC_IMP_tbl.PIN = QIT_LWH_IMP_tbl.PIN) ON
(QIT_Z3_QNOTE_tbl.[Serial number] = QIT_LWH_IMP_tbl.PIN) AND
(QIT_Z3_QNOTE_tbl.[Serial number] = QIT_DTAC_IMP_tbl.PIN);

Essentially what I want to display is a few columns from each of the tables,
but only have records in which the PIN/serial number is located in all 3
tables. I saw someone that need to do something like this using UNION ALL.
Would that be what I need to use?

 

[LFC]

This is my sql statement so far:

SELECT QIT_LWH_IMP_tbl.PIN, QIT_DTAC_IMP_tbl.Created, QIT_LWH_IMP_tbl.[Build
Date], QIT_LWH_IMP_tbl.[Claim Number], QIT_LWH_IMP_tbl.[Part Number],
QIT_LWH_IMP_tbl.[Failure Mode Code], QIT_Z3_QNOTE_tbl.[Where Found],
QIT_Z3_QNOTE_tbl.Reviewed, QIT_Z3_QNOTE_tbl.Comments,
QIT_Z3_QNOTE_tbl.[Coorelation Confirmed]
FROM (QIT_LWH_IMP_tbl INNER JOIN QIT_DTAC_IMP_tbl ON QIT_LWH_IMP_tbl.PIN =
QIT_DTAC_IMP_tbl.PIN) INNER JOIN QIT_Z3_QNOTE_tbl ON QIT_LWH_IMP_tbl.PIN =
QIT_Z3_QNOTE_tbl.[Serial Number]
WHERE (((QIT_LWH_IMP_tbl.PIN) Is Not Null) AND ((QIT_Z3_QNOTE_tbl.[Serial
number]) Is Not Null) AND ((QIT_DTAC_IMP_tbl.PIN) Is Not Null));

I feel like I'm just one step away because if I include group by on the
QIT_LWH_IMP_tbl.PIN then I get exactly what I'm looking for. Unfortunately I
can't figure out a way around it. I tried doing
DISTINCT(QIT_LWH_IMP_tbl.PIN) and it cut the results down significantly, but
not as much as I would like.

 

[Dirk Goldgar]

This is my sql statement so far:

SELECT QIT_LWH_IMP_tbl.PIN, QIT_DTAC_IMP_tbl.Created,
QIT_LWH_IMP_tbl.[Build
Date], QIT_LWH_IMP_tbl.[Claim Number], QIT_LWH_IMP_tbl.[Part Number],
QIT_LWH_IMP_tbl.[Failure Mode Code], QIT_Z3_QNOTE_tbl.[Where Found],
QIT_Z3_QNOTE_tbl.Reviewed, QIT_Z3_QNOTE_tbl.Comments,
QIT_Z3_QNOTE_tbl.[Coorelation Confirmed]
FROM (QIT_LWH_IMP_tbl INNER JOIN QIT_DTAC_IMP_tbl ON QIT_LWH_IMP_tbl.PIN =
QIT_DTAC_IMP_tbl.PIN) INNER JOIN QIT_Z3_QNOTE_tbl ON QIT_LWH_IMP_tbl.PIN =
QIT_Z3_QNOTE_tbl.[Serial Number]
WHERE (((QIT_LWH_IMP_tbl.PIN) Is Not Null) AND ((QIT_Z3_QNOTE_tbl.[Serial
number]) Is Not Null) AND ((QIT_DTAC_IMP_tbl.PIN) Is Not Null));

I feel like I'm just one step away because if I include group by on the
QIT_LWH_IMP_tbl.PIN then I get exactly what I'm looking for.
Unfortunately I
can't figure out a way around it. I tried doing
DISTINCT(QIT_LWH_IMP_tbl.PIN) and it cut the results down significantly,
but
not as much as I would like.

You don't need your WHERE clause (in the above SQL), because the inner joins
on those fields will automatically exclude any records where the joined
fields are Null.

But the problem is that your joins will naturally create duplicate output
records if there are more than one record in any table with the same PIN or
[Serial Number]. From your description, I think that must be at the root of
the problem you're facing. So long as your query must output fields from
all three tables, as you have it defined now, there is no way to reduce
these apparent duplicate records to a single record (whether by GROUP BY or
DISTINCT) and have that record be wholly updatable. That flows naturally
from the fact that any record that is collapsed from multiple records can't
have its data tracked back to a single source record to be updated.

We need to step back and look at what you're trying to do from a broader
perspective. Since one-to-many relationships seem to be involved, maybe a
form/subform arrangement would suit your needs. Or, it *would* be possible
to write a query to extract all records in one table for which there are
matches in the other tables, and have that query be updatable -- so long as
the query doesn't need to return any fields from the other tables.

Could you explain in more detail ...

1. What your tables represent,
2. What the relationships between the tables are, and
3. What you are really trying to do here?

 

[LFC]

My tables represent, warranty data, problems at the factory and more failure
data. Unforunately there are multiple occurances of serial numbers in all 3
tables so the relationships are many-to-many...I know this isn't a good
thing, but I didn't know what else to do. Essentially what I want is to be
able to see all cases of a serial number being involved in all three because
it should help us improve quality if we can identify a reocurring problem
over all 3 tables. I also found out that in one of my tables the the first
digit and the last 4 digits of some serial numbers were not entered. I think
that means I need to change to a like condition, but when I wrote it up I
return no results.

SELECT QIT_LWH_IMP_tbl.PIN, QIT_LWH_IMP_tbl.[Build Date],
QIT_LWH_IMP_tbl.[Claim Number], QIT_LWH_IMP_tbl.[Part Number],
QIT_LWH_IMP_tbl.[Failure Mode Code], QIT_LWH_IMP_tbl.[Failure Reason Code]
FROM QIT_LWH_IMP_tbl, QIT_DTAC_IMP_tbl, QIT_Z3_QNOTE_tbl
WHERE (((QIT_LWH_IMP_tbl.PIN) Like '*QIT_DTAC_IMP_tbl.PIN*' And
(QIT_LWH_IMP_tbl.PIN) Like '*QIT_Z3_QNOTE_tbl.[Serial Number]*'));

This is my sql statement so far:

SELECT QIT_LWH_IMP_tbl.PIN, QIT_DTAC_IMP_tbl.Created,
QIT_LWH_IMP_tbl.[Build
Date], QIT_LWH_IMP_tbl.[Claim Number], QIT_LWH_IMP_tbl.[Part Number],
QIT_LWH_IMP_tbl.[Failure Mode Code], QIT_Z3_QNOTE_tbl.[Where Found],
QIT_Z3_QNOTE_tbl.Reviewed, QIT_Z3_QNOTE_tbl.Comments,
QIT_Z3_QNOTE_tbl.[Coorelation Confirmed]
FROM (QIT_LWH_IMP_tbl INNER JOIN QIT_DTAC_IMP_tbl ON QIT_LWH_IMP_tbl.PIN =
QIT_DTAC_IMP_tbl.PIN) INNER JOIN QIT_Z3_QNOTE_tbl ON QIT_LWH_IMP_tbl.PIN =
QIT_Z3_QNOTE_tbl.[Serial Number]
WHERE (((QIT_LWH_IMP_tbl.PIN) Is Not Null) AND ((QIT_Z3_QNOTE_tbl.[Serial
number]) Is Not Null) AND ((QIT_DTAC_IMP_tbl.PIN) Is Not Null));

I feel like I'm just one step away because if I include group by on the
QIT_LWH_IMP_tbl.PIN then I get exactly what I'm looking for.
Unfortunately I
can't figure out a way around it. I tried doing
DISTINCT(QIT_LWH_IMP_tbl.PIN) and it cut the results down significantly,
but
not as much as I would like.

You don't need your WHERE clause (in the above SQL), because the inner joins
on those fields will automatically exclude any records where the joined
fields are Null.

But the problem is that your joins will naturally create duplicate output
records if there are more than one record in any table with the same PIN or
[Serial Number]. From your description, I think that must be at the root of
the problem you're facing. So long as your query must output fields from
all three tables, as you have it defined now, there is no way to reduce
these apparent duplicate records to a single record (whether by GROUP BY or
DISTINCT) and have that record be wholly updatable. That flows naturally
from the fact that any record that is collapsed from multiple records can't
have its data tracked back to a single source record to be updated.

We need to step back and look at what you're trying to do from a broader
perspective. Since one-to-many relationships seem to be involved, maybe a
form/subform arrangement would suit your needs. Or, it *would* be possible
to write a query to extract all records in one table for which there are
matches in the other tables, and have that query be updatable -- so long as
the query doesn't need to return any fields from the other tables.

Could you explain in more detail ...

1. What your tables represent,
2. What the relationships between the tables are, and
3. What you are really trying to do here?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[LFC]

Also I forgot to mention that I think I'm going to go with a group by
intially and then have a button linking to a sub form that will have all the
instances of the serial number.

This is my sql statement so far:

SELECT QIT_LWH_IMP_tbl.PIN, QIT_DTAC_IMP_tbl.Created,
QIT_LWH_IMP_tbl.[Build
Date], QIT_LWH_IMP_tbl.[Claim Number], QIT_LWH_IMP_tbl.[Part Number],
QIT_LWH_IMP_tbl.[Failure Mode Code], QIT_Z3_QNOTE_tbl.[Where Found],
QIT_Z3_QNOTE_tbl.Reviewed, QIT_Z3_QNOTE_tbl.Comments,
QIT_Z3_QNOTE_tbl.[Coorelation Confirmed]
FROM (QIT_LWH_IMP_tbl INNER JOIN QIT_DTAC_IMP_tbl ON QIT_LWH_IMP_tbl.PIN =
QIT_DTAC_IMP_tbl.PIN) INNER JOIN QIT_Z3_QNOTE_tbl ON QIT_LWH_IMP_tbl.PIN =
QIT_Z3_QNOTE_tbl.[Serial Number]
WHERE (((QIT_LWH_IMP_tbl.PIN) Is Not Null) AND ((QIT_Z3_QNOTE_tbl.[Serial
number]) Is Not Null) AND ((QIT_DTAC_IMP_tbl.PIN) Is Not Null));

I feel like I'm just one step away because if I include group by on the
QIT_LWH_IMP_tbl.PIN then I get exactly what I'm looking for.
Unfortunately I
can't figure out a way around it. I tried doing
DISTINCT(QIT_LWH_IMP_tbl.PIN) and it cut the results down significantly,
but
not as much as I would like.

You don't need your WHERE clause (in the above SQL), because the inner joins
on those fields will automatically exclude any records where the joined
fields are Null.

But the problem is that your joins will naturally create duplicate output
records if there are more than one record in any table with the same PIN or
[Serial Number]. From your description, I think that must be at the root of
the problem you're facing. So long as your query must output fields from
all three tables, as you have it defined now, there is no way to reduce
these apparent duplicate records to a single record (whether by GROUP BY or
DISTINCT) and have that record be wholly updatable. That flows naturally
from the fact that any record that is collapsed from multiple records can't
have its data tracked back to a single source record to be updated.

We need to step back and look at what you're trying to do from a broader
perspective. Since one-to-many relationships seem to be involved, maybe a
form/subform arrangement would suit your needs. Or, it *would* be possible
to write a query to extract all records in one table for which there are
matches in the other tables, and have that query be updatable -- so long as
the query doesn't need to return any fields from the other tables.

Could you explain in more detail ...

1. What your tables represent,
2. What the relationships between the tables are, and
3. What you are really trying to do here?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

My tables represent, warranty data, problems at the factory and more
failure
data. Unforunately there are multiple occurances of serial numbers in all
3
tables so the relationships are many-to-many...I know this isn't a good
thing, but I didn't know what else to do. Essentially what I want is to
be
able to see all cases of a serial number being involved in all three
because
it should help us improve quality if we can identify a reocurring problem
over all 3 tables. I also found out that in one of my tables the the
first
digit and the last 4 digits of some serial numbers were not entered. I
think
that means I need to change to a like condition, but when I wrote it up I
return no results.

SELECT QIT_LWH_IMP_tbl.PIN, QIT_LWH_IMP_tbl.[Build Date],
QIT_LWH_IMP_tbl.[Claim Number], QIT_LWH_IMP_tbl.[Part Number],
QIT_LWH_IMP_tbl.[Failure Mode Code], QIT_LWH_IMP_tbl.[Failure Reason Code]
FROM QIT_LWH_IMP_tbl, QIT_DTAC_IMP_tbl, QIT_Z3_QNOTE_tbl
WHERE (((QIT_LWH_IMP_tbl.PIN) Like '*QIT_DTAC_IMP_tbl.PIN*' And
(QIT_LWH_IMP_tbl.PIN) Like '*QIT_Z3_QNOTE_tbl.[Serial Number]*'));

Hmm, to see all cases from all tables in one list, you're going to have to
use a union query, and a union query can't be updatable. Therefore, I think
I would do something like this:

1. Create a union query to get master list of all serial numbers in all
tables. Let each record in the union query also include a calculated field
identifying which table it comes from. Use UNION, not UNION ALL, so that
there will be only one record returned from each table for any given serial
number. (I'm going to assume for now that you don't care if there are
multiple records for the same serial number in one table; only if there are
records for that serial number in two or more of the tables.) The SQL might
look like:

SELECT PIN As SerialNo, "L" As SourceTable
FROM QIT_LWH_IMP_tbl
UNION
SELECT PIN As SerialNo, "D" As SourceTable
FROM QIT_DTAC_IMP_tbl
UNION
SELECT [Serial Number] As SerialNo, "Z" As SourceTable
FROM QIT_Z3_QNOTE_tbl

Suppose we save this query as "qryAllSerialNos".

2. Create a query that selects from the union query above, only those
records that have matching records from one of the other tables. It might
have SQL like this:

SELECT * FROM qryAllSerialNos
WHERE
(SourceTable = "L" AND
Exists(
SELECT PIN FROM QIT_DTAC_IMP_tbl
WHERE PIN = SerialNo
)
OR
Exists(
SELECT [Serial Number] FROM QIT_Z3_QNOTE_tbl
WHERE [Serial Number] = SerialNo
)
)
OR
(SourceTable = "D" AND
Exists(
SELECT PIN FROM QIT_LWH_IMP_tbl
WHERE PIN = SerialNo
)
OR
Exists(
SELECT [Serial Number] FROM QIT_Z3_QNOTE_tbl
WHERE [Serial Number] = SerialNo
)
)
OR
(SourceTable = "Z" AND
Exists(
SELECT PIN FROM QIT_LWH_IMP_tbl
WHERE PIN = SerialNo
)
OR
Exists(
SELECT PIN FROM QIT_DTAC_IMP_tbl
WHERE PIN = SerialNo
)
)

That's pretty hairy, and I don't know how efficient it will be, but it might
work. If any "Like" comparisons need to be done, they can be put in later,
but I'm interested to see if this approach works.

Save that query as "qrySerialNosMultiple".

Now, opening that query all by itself should show you what serial numbers
are problematic, and what tables they're in. But if you want to be able to
update the records or see the detail of each, that approach is too
simplistic. For that, I think I would proceed as follows:

Put a list box on an unbound form, and set the list box's rowsource to
qrySerialNosMultiple. Let the list box have two columns, one for SerialNo
and one for SourceTable, and let the SerialNo column be the list box's bound
column.

Put three subforms on that form, one bound to each of the three tables, each
subform set in continuous forms view (or datasheet view). Set the Link
Master Fields property of each subform control to the name of the list box.
Set the Link Child Fields property of each subform to the name of the
serial-number field in that subform's recordsource table -- PIN or [Serial
Number].

Now, whenever you click on a serial number in the list box, you'll see the
matching records, if any, in each of the subforms. That will let you decide
what to do about them, and potentially do it on the spot.

 

[Dirk Goldgar]

Also I forgot to mention that I think I'm going to go with a group by
intially and then have a button linking to a sub form that will have all
the
instances of the serial number.

That's similar to the idea I just proposed, actually. Your query with all
inner joins, though, will only find records where the serial number is in
all three tables.