query tables

[patti]

i have inherited a database w/ o documentation & noone to tell me its history.

I have a query that shows 2 tables: "A" and "B"

Table A has several fields, B only 2. The tables are joined on these 2 fields.
3 fields called for the query, all from table a.
When i run the query i get data.

But where is this data coming from? I can find no Table A or B; i can find
no query A or B.

I am stumped. Can anyone help me?

 

[Jerry Whittle]

Could be a few things. For example A and B are queries and not tables.
Whoops! You already eliminated that. Make sure to scroll left and right on
the database window if there are scroll bars. They could just be out of site.

They could be aliases where a table is temporarily named A or B.

Someone could have hidden the tables. If soon the menu go to Tools, Options,
the select the View tab. Put a checkmark in Hidden Objects and System Objects
to see if they then show up.

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Maybe that will give up a secret or two!

 

[mcescher]

i have inherited a database w/ o documentation & noone to tell me its history.

I have a query that shows 2 tables: "A" and "B"

Table A has several fields, B only 2. The tables are joined on these 2 fields.
3 fields called for the query, all from table a.
When i run the query i get data.

But where is this data coming from? I can find no Table A or B; i can find
no query A or B.

I am stumped. Can anyone help me?

Perhaps they are hidden.

Tools > Options > View tab > Hidden Objects

Once they are visible, you can open the properties of the object and
turn off the hidden attribute.

Hope this helps,
Chris M.

 

[patti]

well - lookie there. Thanks!

Here's the sql:

SELECT A.[Long Style], A.[On Hand], A.[On Order]
FROM [Weekly Demand] AS A INNER JOIN [select [long style],max([week end
date]) as [max date] from [weekly demand] group by [long style]]. AS B ON
(A.[Long Style] = B.[long style]) AND (A.[Week End Date] = B.[max date]);

aliases are alien to me. if you would be so kind, could u provide me a short
explanation of what/why.

thanks.

 

[mcescher]

well - lookie there. Thanks!

Here's the sql:

SELECT A.[Long Style], A.[On Hand], A.[On Order]
FROM [Weekly Demand] AS A INNER JOIN [select [long style],max([week end
date]) as [max date] from [weekly demand] group by [long style]]. AS B ON
(A.[Long Style] = B.[long style]) AND (A.[Week End Date] = B.[max date]);

aliases are alien to me. if you would be so kind, could u provide me a short
explanation of what/why.
thanks.

An alias is a short cut to an object. In the query above, you could
type:

SELECT [Weekly Demand].[Long Style], [Weekly Demand].[On Hand],
[Weekly Demand].[On Order]
FROM [Weekly Demand] etc...

or you type the shortened version that you have above. It's just
quicker to type "A" rather than [Weekly Demand].

Also, (and I realize this is an inheirited db), spaces are not your
friend when you name tables, queries, etc... One common format is to
show the object type and then capitalize the first letter of the words
in the name. For example:

Weekly Demand would be named "tblWeeklyDemand"

Hope this helps,
Chris M.

 

[Jerry Whittle]

Nice to know that it was something simple. First of all an alias can save
space. For example here's your original SQL fixed up some:

SELECT A.[Long Style],
A.[On Hand],
A.[On Order]
FROM [Weekly Demand] AS A
INNER JOIN (select [long style],
max([week end date]) as [max date]
from [weekly demand] group by [long style]) AS B
ON (A.[Long Style] = B.[long style])
AND (A.[Week End Date] = B.[max date]);

Now let's get rid of the A alias and plug in the actual table name:

SELECT [WEEKLY DEMAND].[Long Style],
[WEEKLY DEMAND].[On Hand],
[WEEKLY DEMAND].[On Order]
FROM [Weekly Demand]
INNER JOIN (select [long style],
max([week end date]) as [max date]
from [weekly demand] group by [long style]) AS B
ON ([WEEKLY DEMAND].[Long Style] = B.[long style])
AND ([WEEKLY DEMAND].[Week End Date] = B.[max date]);

Notice that it's a little longer. Now imagine that instead of the table
being named [WEEKLY DEMAND] someone when nutzo and called it something like:
[WEEKLY DEMAND OF PARTS AND MATERIALS].
Remember that a field or table name can be up to 64 characters. That could
get ugly!

Now look at your B alias. It's actually an alias for a subquery. I've never
done one without an alias so I don't know if it would even work with
something like:

([select [long style],max([week end date]) as [max date] from [weekly
demand] group by [long style]]).[long style]
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

well - lookie there. Thanks!

Here's the sql:

SELECT A.[Long Style], A.[On Hand], A.[On Order]
FROM [Weekly Demand] AS A INNER JOIN [select [long style],max([week end
date]) as [max date] from [weekly demand] group by [long style]]. AS B ON
(A.[Long Style] = B.[long style]) AND (A.[Week End Date] = B.[max date]);

aliases are alien to me. if you would be so kind, could u provide me a short
explanation of what/why.

thanks.

Could be a few things. For example A and B are queries and not tables.
Whoops! You already eliminated that. Make sure to scroll left and right on
the database window if there are scroll bars. They could just be out of site.

They could be aliases where a table is temporarily named A or B.

Someone could have hidden the tables. If soon the menu go to Tools, Options,
the select the View tab. Put a checkmark in Hidden Objects and System Objects
to see if they then show up.

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Maybe that will give up a secret or two!

 

[patti]

thanks for the help & the education!