Field Naming and Complex Query - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Field Naming and Complex Query

William Kennedy

4 years ago

Are there any issues with naming the fields within a table the same as another tables fields or should I have separate field names?  The following is the fields common to many tables.  Each Table presently has a number replacing the x that is the same for all fields in the table.  All the common fields in each table are the same type and structure.  The payee and Category fields all use the same Combo and tables.  The Amount field is a Calculated field in the table, [Creditx]  [Debitx].  It would be easier if I can use common field names in my reports and forms.

IDx    Datex    PayeeX   Categoryx   DebitX  CreditX  AmountX  Validatedx

Eventually I want to combine all these tables into one table for a report with a append query and an Identifier field that indicates which table it came from.  If I have 10 tables that need to be combined can an action button do all the tables and then open the report.  This seems like a very complex series of actions.

	Juan C Rivera @Reply 4 years ago
Good question did you try it? I had problems with same names and had to declare them by table and field [table].[field] I guess the way to explain it complex like that may be confusing just reading so it may confuse the SQL... try it 1st and let me know love to know what happens.

Scott Axton @Reply

4 years ago

I know it's kind of a cop  out answer but...
It depends.
For instance if you are archiving old data - use the same names.  Makes it easier to union the tables later if you want to pull historical data.
If you are using the same fields in a customer, vendor, supplier tables it could be that you need to consider that you need to restructure your tables.

If you are joining two tables with the same field names say in a query, Access wouldn't know which table you want to pull the data from.  You would need to qualify the fields with the TableName1.FieldName1 TableName2.FieldName1
etc.

So there is not really a cut and dried answer.

	William Kennedy OP @Reply 4 years ago
I trying a couple things. Maybe using an alias in a query to append all the table records would work.

	Dan Jackson @Reply 4 years ago
From Juan and Scotts answer, it sounds absolutely fine but maybe get used to declaring the table name when referencing the field. Good to know

	Juan C Rivera @Reply 4 years ago
William did you try it what happened?

	William Kennedy OP @Reply 4 years ago
Sorry not yet. Working on another issue that came up.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View