Replacing data in Report

[Guest]

Forgive me if this has been answered. I just spent a half hour looking with
no luck.

I have a table, "Exceptions", that was imported from Excel. One column/field
is "TD Number".

In another table, "TD Numbers" also imported from Excel, there are three
columns / fields: DTE Number, County Number, TD Name. For each DTE Number
there is only one County Number.

[Exceptions].[TD Number] = [TD Numbers].[DTE Number]. Sorry if I have the
syntax messed up. Any way, when [Exceptions].[TD Number] returns "31110", the
same number is found in [TD Numbers].[DTE Number]. In the [TD Numbers]
table, the corresponding [County Number] is "001".

So far I've tried about combination of queries, new tables, SQL Statements
and what have you to get the report to show the County Number instead of the
DTE Number.

In summary, when the report is generated from the table [Exceptions], I want
the field [TD Number] to show the [County Number] instead of the [DTE
Number}. I really don't want to replace 3,200 records by hand. What can I
say. I'm lazy.

Thank you.

 

[Marshall Barton]

Forgive me if this has been answered. I just spent a half hour looking with
no luck.

I have a table, "Exceptions", that was imported from Excel. One column/field
is "TD Number".

In another table, "TD Numbers" also imported from Excel, there are three
columns / fields: DTE Number, County Number, TD Name. For each DTE Number
there is only one County Number.

[Exceptions].[TD Number] = [TD Numbers].[DTE Number]. Sorry if I have the
syntax messed up. Any way, when [Exceptions].[TD Number] returns "31110", the
same number is found in [TD Numbers].[DTE Number]. In the [TD Numbers]
table, the corresponding [County Number] is "001".

So far I've tried about combination of queries, new tables, SQL Statements
and what have you to get the report to show the County Number instead of the
DTE Number.

In summary, when the report is generated from the table [Exceptions], I want
the field [TD Number] to show the [County Number] instead of the [DTE
Number}.

This is a job for the report's record source query. What
you want is to JOIN the two tables so that the fields from
the related records are all available to the report

I think you want something like:

SELECT E.[TD Number], T.[County Number], T.[TD Name]
FROM Exceptions As E INNER JOIN [TD Numbers] As T
ON E.[TD Number] = T.[DTE Number]

 

[Guest]

Thanks for the tip, Marshall but the report field is now empty. Maybe I
missed something. Here's the layout.

Table "Exceptions", Field "TD Number".

Table TD Numbers, Fields "County Number" and "DTE Number".

Error message: "Syntax error (comma) in query expression '[SELECT E.] [TD
Number], T.][[County Number], T].[[TD Name] FROM Exceptions As E INNER JOIN
[TD Numbers] As T ON E].[[TD Number] = T].[[DTE Number]"
The error message put in the extra "]".

I'm so confused.

--
I know enuff to be dangerous.

Forgive me if this has been answered. I just spent a half hour looking with
no luck.

I have a table, "Exceptions", that was imported from Excel. One column/field
is "TD Number".

In another table, "TD Numbers" also imported from Excel, there are three
columns / fields: DTE Number, County Number, TD Name. For each DTE Number
there is only one County Number.

[Exceptions].[TD Number] = [TD Numbers].[DTE Number]. Sorry if I have the
syntax messed up. Any way, when [Exceptions].[TD Number] returns "31110", the
same number is found in [TD Numbers].[DTE Number]. In the [TD Numbers]
table, the corresponding [County Number] is "001".

So far I've tried about combination of queries, new tables, SQL Statements
and what have you to get the report to show the County Number instead of the
DTE Number.

In summary, when the report is generated from the table [Exceptions], I want
the field [TD Number] to show the [County Number] instead of the [DTE
Number}.

This is a job for the report's record source query. What
you want is to JOIN the two tables so that the fields from
the related records are all available to the report

I think you want something like:

SELECT E.[TD Number], T.[County Number], T.[TD Name]
FROM Exceptions As E INNER JOIN [TD Numbers] As T
ON E.[TD Number] = T.[DTE Number]

 

[Marshall Barton]

Thanks for the tip, Marshall but the report field is now empty. Maybe I
missed something. Here's the layout.

Table "Exceptions", Field "TD Number".

Table TD Numbers, Fields "County Number" and "DTE Number".

Error message: "Syntax error (comma) in query expression '[SELECT E.] [TD
Number], T.][[County Number], T].[[TD Name] FROM Exceptions As E INNER JOIN
[TD Numbers] As T ON E].[[TD Number] = T].[[DTE Number]"
The error message put in the extra "]".

Where did all those extra [ and ] come from?? I don't know
how you entered the query, but it is totally garbled.

You were supposed to switch the query to SQL view and Paste
my query as a replacement for whatever was already there.

 

[Guest]

Ah, there lies the rub. I was pasting the query into the design view.
Oh, I've been up to my neck in gators the past several days which is why I
didn't respond sooner.
Thanks again.
--
I know enuff to be dangerous.

Thanks for the tip, Marshall but the report field is now empty. Maybe I
missed something. Here's the layout.

Table "Exceptions", Field "TD Number".

Table TD Numbers, Fields "County Number" and "DTE Number".

Error message: "Syntax error (comma) in query expression '[SELECT E.] [TD
Number], T.][[County Number], T].[[TD Name] FROM Exceptions As E INNER JOIN
[TD Numbers] As T ON E].[[TD Number] = T].[[DTE Number]"
The error message put in the extra "]".

Where did all those extra [ and ] come from?? I don't know
how you entered the query, but it is totally garbled.

You were supposed to switch the query to SQL view and Paste
my query as a replacement for whatever was already there.