Function of colon between table names when query in design view

[Jazz]

I am looking at a field from a query that is in design view. Inside the
field are two field names from the same table which are separated by a colon.
For example, Old and New are both fields from tblReplace and appear like
this Old:New in design view. I believe the colon concatenates the field Old
and New into a new field when you change the query view to datasheet view.
Am I correct?

Also, in a completely separate question if you are looking at a query in
design view and there is a field in the (Field row, a table in the (Table
row, and Group By in the (Total row what function will the Group By option
perform when you change the query view to datasheet view?

 

[John Spencer]

If you are talking about having a field "block" with something in it like
Field: Old: New
that should be assigning an alias "OLD" for the field named NEW and should not
do any concatenating at all.

It will consolidate all the same values into one row. For instance if you had
a table with first names and last names show only the first name and GROUP By
the first names you will see one row for everyone who has the first name JOHN.

If you add the first name field again and set the Total to COUNT you would see
something like

FIrstName CountOfFirstName
JAMES_________8
JEAN__________1
JOHN__________22

If you have multiple fields and group by them, then the group by is done on
the combination of the fields.

 

[Jazz]

Thank you for teaching me about the proper vocabulary i.e. "block" and
helping me understand how the colon : and Group by work.

I do have one more question now about the colon. What function could
assigning an alias accomplish in a query?

 

[John Spencer]

It can simplify things.

If you have a calculated field assigning an alias can make clear what you are
calculating in the query. Given an item cost and a number of items sold, you
might want a line total

Field: LineTotal: [ItemCost] * [NumberSold]

If you have two tables with the same field name you might want to assign
different names to the fields instead of referring to them by tablename plus
field name when you use them on forms and reports.

Field: OldLast: LastName
Table: TableA

Field: NewLast: LastName
Table: TableB

"block" is just a convenient way to refer to the visual rectangular area where
a field name or calculated field is entered. If you use "cell" people tend to
think of a spreadsheet cell. It is not official terminology, it is just my
attempt to make things clear.

 

[Bob Barrows]

In addition to John's reply, the most important function of an alias is to
ensure that all columns in the resultset have
1. a name - a name is required and columns that are the result of
calculations will not have names without aliases being assigned to them
2. unique names - John already touched on this.