Designate a Table

[Ahrek]

This is a simple commande that I forget every time I need to use it. I am running a query in which more than one table has the same name for a set of records.

How do I designate which table to pull the data from in an expression?

 

[intern42]

If designing the query in design view, add the tables and directly drag the fields from the correct tables.

If designing in SQL view, use the following syntax:

SELECT [Table Name].[Field Name]
FROM [Table Name];

 

[dcx693]

You mean a SQL expression? SELECT fields FROM tablename WHERE etc...

 

[Ahrek]

thanks, but i am writing an aggregate function:

dsum("[meas]*[qtord]", "cpfile2", "[pname1]='"&[pname1]&'"")

the problem is that multiple of the tables in the query have a record named "pname1". I need to designate which "pname 1" I need. It involves a "!" and the correct order and bracketing.

 

[intern42]

[Table name].[field name]

Try that.

If you're pretty sure that you need the bang (!), try
[TABLES]![Table name].[field name]

 

[dcx693]

The 2nd parameter in the DSUM function, where you have "cpfile2", is where you specify which table (or query) is the "domain" in which you are doing the summing. Just change that parameter to the name of the table you wish to sum in.

If you're calling DSUM from a report or form, then this part of the formula:
"[pname1]='"&[pname1]&'""
needs to be in this format:
"[pname]='" & Me!nameofcontrol & "'" (if the control contains text).