Table names in query fields

  • Thread starter Thread starter buggirl
  • Start date Start date
B

buggirl

Hello -

I have just created a basic query, similar to lots of other queries that I
have created, but this one turned out a little different: the table names are
included in the field names in the query. I'm not sure why this is happening
(did I press a button accidentally?), but I would like to get rid of it.

Thanks,

buggirl
 
What button was that?

Post the SQL statement for the query here. While in design view go to View,
Sql View. Copy what it says there and post it here.
 
I do have same field names in two tables. That's probably the problem. Is
there any way to rectify it without changing all of the names?

Here is the SQL anyway:

SELECT [tbl PoolDetails].SampleID, [tbl PoolDetails].PoolID, [tbl
SiteDetails].SampleYear, [tbl Location].location, [tbl stable
isotopes].group, [tbl stable isotopes].d15N, [tbl stable isotopes].d13C
FROM (([tbl Location] INNER JOIN [tbl SiteDetails] ON [tbl
Location].LocationPK = [tbl SiteDetails].Location) INNER JOIN [tbl
PoolDetails] ON [tbl SiteDetails].SampleID = [tbl PoolDetails].SampleID)
INNER JOIN [tbl stable isotopes] ON [tbl PoolDetails].PoolPK = [tbl stable
isotopes].poolPK.Value;
 
You could create aliases for the fields:

SELECT [tbl PoolDetails].SampleID AS SampleIDs,
[tbl PoolDetails].PoolID AS PoolIDs,
[tbl SiteDetails].SampleYear AS SampleYears,
[tbl Location].location AS Locations,
[tbl stable isotopes].group AS Groups,
[tbl stable isotopes].d15N AS d15Ns,
[tbl stable isotopes].d13C AS d13Cs
FROM (([tbl Location] INNER JOIN [tbl SiteDetails]
ON [tbl Location].LocationPK = [tbl SiteDetails].Location)
INNER JOIN [tbl PoolDetails]
ON [tbl SiteDetails].SampleID = [tbl PoolDetails].SampleID)
INNER JOIN [tbl stable isotopes]
ON [tbl PoolDetails].PoolPK = [tbl stable isotopes].poolPK.Value;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


buggirl said:
I do have same field names in two tables. That's probably the problem. Is
there any way to rectify it without changing all of the names?

Here is the SQL anyway:

SELECT [tbl PoolDetails].SampleID, [tbl PoolDetails].PoolID, [tbl
SiteDetails].SampleYear, [tbl Location].location, [tbl stable
isotopes].group, [tbl stable isotopes].d15N, [tbl stable isotopes].d13C
FROM (([tbl Location] INNER JOIN [tbl SiteDetails] ON [tbl
Location].LocationPK = [tbl SiteDetails].Location) INNER JOIN [tbl
PoolDetails] ON [tbl SiteDetails].SampleID = [tbl PoolDetails].SampleID)
INNER JOIN [tbl stable isotopes] ON [tbl PoolDetails].PoolPK = [tbl stable
isotopes].poolPK.Value;


Jerry Whittle said:
What button was that?

Post the SQL statement for the query here. While in design view go to View,
Sql View. Copy what it says there and post it here.
Click to expand...
Click to expand...
 
Gotcha. I'll give it a go.

But, for my long-term sanity, I may be smarter to change the names of some
fields!

Thanks!

buggirl

Jerry Whittle said:
You could create aliases for the fields:

SELECT [tbl PoolDetails].SampleID AS SampleIDs,
[tbl PoolDetails].PoolID AS PoolIDs,
[tbl SiteDetails].SampleYear AS SampleYears,
[tbl Location].location AS Locations,
[tbl stable isotopes].group AS Groups,
[tbl stable isotopes].d15N AS d15Ns,
[tbl stable isotopes].d13C AS d13Cs
FROM (([tbl Location] INNER JOIN [tbl SiteDetails]
ON [tbl Location].LocationPK = [tbl SiteDetails].Location)
INNER JOIN [tbl PoolDetails]
ON [tbl SiteDetails].SampleID = [tbl PoolDetails].SampleID)
INNER JOIN [tbl stable isotopes]
ON [tbl PoolDetails].PoolPK = [tbl stable isotopes].poolPK.Value;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


buggirl said:
I do have same field names in two tables. That's probably the problem. Is
there any way to rectify it without changing all of the names?

Here is the SQL anyway:

SELECT [tbl PoolDetails].SampleID, [tbl PoolDetails].PoolID, [tbl
SiteDetails].SampleYear, [tbl Location].location, [tbl stable
isotopes].group, [tbl stable isotopes].d15N, [tbl stable isotopes].d13C
FROM (([tbl Location] INNER JOIN [tbl SiteDetails] ON [tbl
Location].LocationPK = [tbl SiteDetails].Location) INNER JOIN [tbl
PoolDetails] ON [tbl SiteDetails].SampleID = [tbl PoolDetails].SampleID)
INNER JOIN [tbl stable isotopes] ON [tbl PoolDetails].PoolPK = [tbl stable
isotopes].poolPK.Value;


Jerry Whittle said:
What button was that?

Post the SQL statement for the query here. While in design view go to View,
Sql View. Copy what it says there and post it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hello -

I have just created a basic query, similar to lots of other queries that I
have created, but this one turned out a little different: the table names are
included in the field names in the query. I'm not sure why this is happening
(did I press a button accidentally?), but I would like to get rid of it.

Thanks,

buggirl
Click to expand...
Click to expand...
Click to expand...
 
Hello -

I have just created a basic query, similar to lots of other queries that I
have created, but this one turned out a little different: the table names are
included in the field names in the query. I'm not sure why this is happening
(did I press a button accidentally?), but I would like to get rid of it.

Thanks,

buggirl
Click to expand...

Why do you care? Your users should never see query datasheets or fieldnames;
they should be interacting with data only on Forms, and you can put whatever
labels you like on a form.

If you have the same fieldname in two tables, then yes, Access must
disambiguate them. The automatic way to do so is by appending the tablename
[Table1].[FieldA], [Table2].[FieldA].

If you wish to disambiguate them yourself you can open the query in design
view and put a unique alias name followed by a colon in front of the ambiguous
name; e.g. if you have FieldA from Table1, you can put

FieldAVersion1: FieldA

in the Field cell instead of just FieldA.
 
Back
Top