Of course way to much here to understand all of it but the SQL is referencing
a table [Payroll - Master Table - Current Year] and THAT table is NOT listed
in the table objects just a query that uses the Alias of it??? and groups the
data and sums it. I want to get to the Original table - Where would I find
it????
SELECT [Payroll - Master Table - Current Year].[Soc Sec#], [Payroll - Master
Table - Current Year].[MAN NAME], Sum([Payroll - Master Table - Current
Year].[Total Wage Payment]) AS [SumOfTotal Wage Payment], Sum([Payroll -
Master Table - Current Year].[Federal Withholding]) AS [SumOfFederal
Withholding], Sum([Payroll - Master Table - Current Year].[State Witholding])
AS [SumOfState Witholding], Sum([Payroll - Master Table - Current
Year].[Employee FICA Withholding]) AS [SumOfEmployee FICA Withholding],
Sum([Payroll - Master Table - Current Year].[Employee Medicare Withholding])
AS [SumOfEmployee Medicare Withholding], Sum([Payroll - Master Table -
Current Year].[Vol Deduct Amount 1]) AS [SumOfVol Deduct Amount 1],
Sum([Payroll - Master Table - Current Year].[Vol Deduct Amount 2]) AS
[SumOfVol Deduct Amount 2], Sum([Payroll - Master Table - Current Year].[Vol
Deduct Amount 3]) AS [SumOfVol Deduct Amount 3], Sum([Payroll - Master Table
- Current Year].[Vol Deduct Amount 4]) AS [SumOfVol Deduct Amount 4],
Sum([Payroll - Master Table - Current Year].[Vol Deduct Amount 5]) AS
[SumOfVol Deduct Amount 5], Sum([Payroll - Master Table - Current Year].[Vol
Deduct Amount 6]) AS [SumOfVol Deduct Amount 6], Sum([Payroll - Master Table
- Current Year].[Vol Deduct Amount 7]) AS [SumOfVol Deduct Amount 7],
Sum([Payroll - Master Table - Current Year].[Vol Deduct Amount 8]) AS
[SumOfVol Deduct Amount 8], Sum([Payroll - Master Table - Current Year].[Vol
Deduct Amount 9]) AS [SumOfVol Deduct Amount 9], Sum([Payroll - Master Table
- Current Year].[Vol Deduct Amount 10]) AS [SumOfVol Deduct Amount 10],
Sum([Payroll - Master Table - Current Year].[Employee Union Dues]) AS
[SumOfEmployee Union Dues], Sum([Payroll - Master Table - Current
Year].[Company FICA Match]) AS [SumOfCompany FICA Match], Sum([Payroll -
Master Table - Current Year].[Company Medicare Match]) AS [SumOfCompany
Medicare Match], Sum([Payroll - Master Table - Current Year].[Company FUTA])
AS [SumOfCompany FUTA], Sum([Payroll - Master Table - Current Year].[Company
SUTA]) AS [SumOfCompany SUTA], Sum([Payroll - Master Table - Current
Year].[Company Pension Contribution]) AS [SumOfCompany Pension Contribution],
Sum([Payroll - Master Table - Current Year].[Company Welfare Contribution])
AS [SumOfCompany Welfare Contribution], Sum([Payroll - Master Table - Current
Year].[Company Annuity Contribution]) AS [SumOfCompany Annuity Contribution],
Sum([Payroll - Master Table - Current Year].[Company TT&R Contribution]) AS
[SumOfCompany TT&R Contribution], Sum([Payroll - Master Table - Current
Year].[Company Promotional Contribution]) AS [SumOfCompany Promotional
Contribution], Sum([Payroll - Master Table - Current Year].[Total REGULAR
Payment]) AS [SumOfTotal REGULAR Payment], Sum([Payroll - Master Table -
Current Year].[Auto Pay Bonus Amount]) AS [SumOfAuto Pay Bonus Amount],
Sum([Payroll - Master Table - Current Year].[Taxible Reimb]) AS [SumOfTaxible
Reimb], Sum([Payroll - Master Table - Current Year].[Non Taxible Reimb]) AS
[SumOfNon Taxible Reimb], Sum([Payroll - Master Table - Current
Year].[Vacation Payment]) AS [SumOfVacation Payment], Sum([Payroll - Master
Table - Current Year].[3rd Party Sick Payment]) AS [SumOf3rd Party Sick
Payment], Sum([Payroll - Master Table - Current Year].[Gross Earnings]) AS
[SumOfGross Earnings], Sum([Payroll - Master Table - Current Year].[Total
Deductions]) AS [SumOfTotal Deductions], Sum([Payroll - Master Table -
Current Year].[Net Earnings]) AS [SumOfNet Earnings], Sum([Payroll - Master
Table - Current Year].[Hourly Bonus]) AS [SumOfHourly Bonus], Sum([Payroll -
Master Table - Current Year].[Field Annuity 401K]) AS [SumOfField Annuity
401K]
FROM [Payroll - Master Table - Current Year]
GROUP BY [Payroll - Master Table - Current Year].[Soc Sec#], [Payroll -
Master Table - Current Year].[MAN NAME]
ORDER BY [Payroll - Master Table - Current Year].[MAN NAME];
Thanks,
Barb
KenSheridan via AccessMonster.com said:
Correction: I missed a couple of ampersands:
SELECT E1.FirstName & " " & E1.Lastname AS Employee,
E2.FirstName & " " & E2.Lastname AS Supervisor
FROM Employees As E1 INNER JOIN Employees AS E2
ON E1.SupervisorID = E2.EmployeeID;
Ken Sheridan
Stafford, England
Barb:
The tables aren't hidden. An alias is a just a name given to a table in a
query, its not the name of an actual table. Switch to SQL view and you'll
see in the SQL statement something like:
…..FROM RealTableName AS AliasName
where RealTableName is the actual table name and AliasName is the name
whoever created the query gave it in the query. Aliases are often used when
you have two or more instances of the same table in a query, e.g. if a table
is joined to itself like this:
SELECT E1.FirstName & " " E1.Lastname AS Employee,
E2.FirstName & " " E2.Lastname AS Supervisor
FROM Employees As E1 INNER JOIN Employees AS E2
ON E1.SupervisorID = E2.EmployeeID;
Here the aliases E1 and E2 represent two instances of the Employees table so
that it can be joined to itself to show which employee is each employee's
supervisor, of those that have supervisors. To also include the top brass
who don't have supervisors you'd use a LEFT JOIN
Another way in which aliases are used is to correlate a subquery with an
outer query, e.g.
SELECT *
FROM ORDERS AS O1
WHERE OrderDate =
(SELECT MAX(OrderDate)
FROM Orders As O2
WHERE O2.CustomerID = O1.CustomerID);
to return the latest order for each customer.
Or some developers will use aliases simply to shorten an SQL statement by not
repeating the full table names each time, even when the use of aliases is not
actually necessary.
Ken Sheridan
Stafford, England
i am looking over a database that someone gave me. There are Many queries
that use a table that I do not see in the list of tables(can they be hidden)
[quoted text clipped - 5 lines]