Member Avatar for gruffy321

HI all, am fairly new here stil and hope to give back as much as i take , in time , experience and wisdom... (the wisdom bit is probably way off for now)

I have a set of tables , but one table needs to show the information chosen in one column . (we will call this column Country and the field holds United Kingdom and is linked to the primary id of the table)
A column in the same table (we will call this column Team and the field should hold the data selected in country)

1.My issue is comes from trying differing sorts of sql and not really understanding how access processes these including reserved words etc. Any POinters would be a great help , especially MS Access 2007 style approach to SQL table queries etc
2. I am not sure if i am breaking relational integrity rules here, by allowing repettition of data in the same table.
3. Help !?
4. Please ??
5. Thanks for reading Gruffy
ps below is sql i have currently tried, but get errors for:


;cant get to work

SELECT 
FROM tblAthlete INNER JOIN tblAthlete AS tblAthlete ON tblAthlete.Country = tblAthlete.Team;

;cant get to work

UPDATE tblAthlete INNER JOIN tblAthlete AS tblAthlete ON tblAthlete.Country = tblAthlete.Team SET;
  • 1 Contributor
  • 1 Reply
  • 201 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by gruffy321

Dani AI

Generated

A short, practical summary for future readers and to expand on 's followup: if the goal is simply to copy the current Country value into Team for existing rows, run a single UPDATE query. If Team must always mirror Country, do not store the same text twice — use a query-calculated field or display Country twice on forms/reports. Storing duplicate values is denormalization and makes maintenance harder (updates must be kept in sync).

A safe one-off update (preview first, then run) is:

UPDATE tblAthlete
SET [Team] = [Country];

To limit changes to empty Team values:

UPDATE tblAthlete
SET [Team] = [Country]
WHERE [Team] IS NULL;

Preview affected rows first with a SELECT, and always back up the database before running update queries.

Notes and troubleshooting:

  • If Country is actually a lookup that stores a country ID but displays the country name, copying the field copies the stored value (the ID). If the displayed name is required, use a join to the country table or create a calculated column in queries that returns the name.
  • If Access gives ambiguous/keyword errors, enclose names in square brackets (example above) or rename fields that clash with reserved words.
  • You can build the same update in Access 2007 UI via Create -> Query Design, switch to Update Query and put [Country] in the Update To cell for the Team field.

When denormalization is necessary (for snapshotting or caching), add a clear process: either a form-level copy (AfterUpdate event) or a scheduled batch update so the duplicate stays consistent. Since located good reference sites, this distills the actionable steps so readers can apply them without chasing other pages.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.