query tables

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?


0
Utf
11/27/2007 7:44:02 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
674 Views

Similar Articles

[PageSpeed] 27

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!
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"patti" wrote:

> 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?
0
Utf
11/27/2007 8:14:01 PM
On Nov 27, 1:44 pm, patti <pa...@discussions.microsoft.com> wrote:
> 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.
0
mcescher
11/27/2007 8:21:20 PM
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.
"Jerry Whittle" wrote:

> 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!
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> "patti" wrote:
> 
> > 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?
0
Utf
11/27/2007 8:31:00 PM
On Nov 27, 2:31 pm, patti <pa...@discussions.microsoft.com> wrote:
> 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.





0
mcescher
11/27/2007 9:02:47 PM
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.

"patti" wrote:

> 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.
> "Jerry Whittle" wrote:
> 
> > 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!
> > -- 
> > Jerry Whittle, Microsoft Access MVP 
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > 
> > "patti" wrote:
> > 
> > > 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?
0
Utf
11/27/2007 9:13:01 PM
thanks for the help & the education!

"patti" wrote:

> 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?
> 
> 
0
Utf
11/27/2007 10:42:00 PM
Reply:

Similar Artilces:

UPR30300 Table in Payroll
I need info about the UPR30300 Table. Particularly about the column PYRLRTYP. I wanna about the possible values of this column. Following is my understanding 1 - Paycode, 2- Deductions, 3-benefits I want to know about more possible values like 4, 5, 6 etc and what each of the stands for. Any help will be greatly apreciated. Thanks in advance Tempest You are right about those 3 values for the PYRLRTYP. 4 means State Tax and 5 is for Local Tax, and that's about it. Hope this helps. Marvs This posting is provided "AS IS" with no warranties, and confers no rights. "...

Wrong Datatype in query
Hi to all, I am getting a data type mismatch in the following query in the where condition: UPDATE tblBudgetBr SET tblBudgetBr.[To] = DateAdd("yyyy",2,"Date()") WHERE (((tblBudgetBr.MthToMth)=True)); If I run a normal select with the same criteria,it works fine, can anyone tell me what is wrong here ?? TIA On Fri, 22 Feb 2008 12:03:01 -0800, Alain wrote: > Hi to all, > > I am getting a data type mismatch in the following query in the where > condition: > > UPDATE tblBudgetBr SET tblBudgetBr.[To] = DateAdd("yyyy",2,"Date()") &...

Extending Field Size in Pivot Tables
Hi - I have been conducting interviews and some of the responses are long. I have tried to put them in a pivot table but there seems to be a field size limit. (By the Way, MS Access is not available). Is there a way I can either extend the field size which would be my preference or can you suggest a different way? Using the lookups as a formula are overwhelming the size of my file. Thanks Carrie -- Message posted via http://www.officekb.com Hi, A little more detail might help what is the structure of your pivot table, what is the error message you are getting, what do you long fields c...

Union query
Hi I have two querries that are the same, except I have the criteria: - on column called Ble service - one query asks for items "TTO" the other for "OPPO" When I try to union both queries into one using sql (union all) then on the result the query asks for bble service - I can just type enter and have the result of the query, but I want to skip it - I just want to click to my union query and see the results. I am doing something wrong that it asks me for it? Thank you -- Greatly appreciated Eva If I understand correctly you do not need a union. Try t...

Table for summary quantities for item/site combinations
I am using Great Plains 8.0. I want a table that holds summary quantities for item/site combinations so I can access site specific quantity information for an item or group of items with Smartlist Builder. I really need the ability to have usage numbers for my inventory (sales + dependant quantities) by warehouse to simplify purchasing. I know I can get item information for Item History but I need a complete listing by vendor or primary vendor so I can print out a list of items I want to review for possible reorder. The information needs to include sales quantities and dependant quantit...

pivot tables
Hi all, I have a spreadsheet that has a variety of 'fields', split into column ie. name | address1 | address 2 | priority | zone I have created a pivot table that shortlists based on the priority an zone fields. Problem is, I can only get the 'name' data listed in th result rows, and not the associated address data. The ideal is to b able to generate as follows; Pivottable priority - select from a dropdown (done this) zone - select from a dropdown (done this) name1 - address1 address2 name2 - address1 address2 name3 - .... Any ideas -- Message posted from http...

Budget vs. Spending table
I'm switching from Money 2005 to 2007. In the 2005 version, under "Budget Summary" on the left sidebar from the Home page was a link to a report/table called "Budget vs. Spending. The table eas titled "Compare Actual Spending to Your Budget" and near the top had a drop down box to select the time period for the table. Below the table had the actual categories, spending, budget and difference in columns. Order was by category. I can't find this same table/report in 2007. Closest is viewing "Monthly Budget report - similar layout but no time choi...

Question about a search query (primary and Secondary search)
I have been tasked with going through a few people's PSTs in order to find word search matches for specific criteria. First I started by exporting the PST into Access format -it does a great job in creating the fields and populating the data, now I need to create a query in order to find the primary search match as in the table below i.e."house" -if I find "house" in one of the emails -then I would proceed and do a secondary search in the remaining of the other secondary search words i.e."man" "actual" "forbid" ,etc. Once that compl...

Filter query from text box
I have a query that is filtered out by a year. I have a form that the user enters in the year. In the query, if I just put in a year of 2009. It works fine. If I replace the filter criteria to this: [Forms]![frmSalesAnalysis]![Year] I get a ODBC-Failed error. Any ideas. Thanks, Jasper Post SQL of query by opening in design view, click on VIEW - SQL View, hightlight all, copy, and paste in a post. -- Build a little, test a little. "Jasper Recto" wrote: > I have a query that is filtered out by a year. > > I have a form that the ...

MY PIVOT TABLE WON'T REFRESH
I HAVE TRIED ALL OF THE SUGGESTIONS IN MICROSOFT BUT NOTHING WORKS. IT SEEMS TO GO THRU IT'S CYCLE BUT THE INFORMATION IS NOT SHARED WITH THE OTHER SHEETS. I don't know what you mean by "the information is not shared with the other sheets." What's on the other sheets? What exactly happens when you refresh the pivot table? What results do you expect to see, that you don't see? LYNDE wrote: > I HAVE TRIED ALL OF THE SUGGESTIONS IN MICROSOFT BUT NOTHING WORKS. IT SEEMS > TO GO THRU IT'S CYCLE BUT THE INFORMATION IS NOT SHARED WITH THE OTHER SHEETS. --...

Question on Table: Constants
Is it safe to use this table to get the List View Line Item Number. For example in the window "Receivings Transaction Entry" Open "Purchasing Distribution Entry" window" The Type column has drop down lists and Work In Progress is one of the items for Project accounting cost category. And in the table Constants if you query the column Name "Name" for "POP_DIST_WIP" You'll find the right matching Line number "15" as a value. I just want to know if this table will be available in the future updates and if it will be updated wit...

Deleting all data from B_E database tables. (AC2003)
Hi all, Im trying to write a query so that all data in all but two tables is deleted, not the tables themselves only the data. I keep getting these errors when im trying to build a suitable query.. COULD NOT DELETE FROM SPECIFIED TABLES or TOO MANY FIELDS DEFINED With any luck i'd like to able to have this query run automatically each day is there any way to do that or do i have to repost in vba section? Thanks for reading!! We aren't there. We can't see what you've already tried. Please post the SQL statement of one of your DELETE queries. -- Regards Jeff ...

Crosstab Query and Zero Values
Greetings, thank you in advance for looking. Basically the database tracks spend based upon the vendor and who is spending the money. Problem is that not all spenders spend with all vendors and vice versa. I need to create a crosstab query that I can take out of access and paste into a preformated excel workbook. Problem is that I can't figure out how to create the crosstab so that all values for the Row are shown. I added a zero spend record for all vendors with all spenders...hoping this would resolve the situation. Alas it did not, Access isn't showing records that have ...

Daily Make Table Query
I have a Make Table Query that I will need to run every morning. Is there anyway to have the query add today's date to the name of the table (without going in and changing it everyday) so that the new table won't overwrite yesterday's table? =?Utf-8?B?SkQ=?= wrote: > > I have a Make Table Query that I will need to run every morning. Is there > anyway to have the query add today's date to the name of the table (without > going in and changing it everyday) so that the new table won't overwrite > yesterday's table? In VBA you can use a c...

ADC query?
Hi all, As we have migrated our Ex5.5 servers over to Ex2K we have set up ADC Connection Agreements. Each new Exchange server is added to the Exchange site containing the old server and mailboxes moved using ADU&C. These connection agreements firstly point at the original 5.5 server on port 389 and once all of the mailboxes are on the new server we change them to point at the new server on port 379. We have a slightly different situation at one of our offices in that the old servers were in a different domain that we don't intend to keep. Therefore the new server is sitting in a ne...

Lot Attribute Table ID?
We maintain Lot Attributes for our items and have found an error in a few lots. Rather than try to do an inventory transaction just to update lot attribute information would it be possible to update the information in a table? If so what is the proper table for the Lot Attribute data? We are on 9.0 There is no other method I found to simply update Lot Attributes within GP. Your help/reply is appreciated. -Scott Scott The IV_Lot_Attributes table is IV00301 in SQL. You can find this information under Tools >> Resource Descriptions >> Tables. David Musgrave [MSFT] Senio...

Sub Query zero rows but need header
As the sub query return zero rows , inspite i me adding an header column in the outter query i dont get that header row and it coresponsing values as null select 'Category2' Header,* from sysobjects where crdate =(select xtype from sysobjects where crdate>'31-12-2010') Expected out: Row1: Category2 null null How do i acheive this. What the rule thats i am trying to break. I tried union I need more simple method. As the sub query return zero rows , inspite i me adding an header column in the outter query i dont get that header row and it coresponsin...

table into Word Document
I have data in the cells of an excel spreadsheet so that it looks like a table. I can print this out and it looks good (margins, font, etc). How can I get this data/table into a Word document without losing the formatting? I have tried inserting a page break into Word and then copying/pasting but that doesn't seem to work. HELP?!!? Thanks Copy the way you were doing it. Then, in Word select: Edit, Paste Special, Formatted text. Suzan "Penny" wrote: > I have data in the cells of an excel spreadsheet so that it looks like a > table. I can print this out and it...

Update field names using a query
I have to do a daily import from our accounting software. I have queries created that update a lot of the information that I need. However, I was wondering if there was a way to do a Make table query or an update query that I can rename the field names instead of me mannualy doing it each day. After I clean the data, I have to export it into our leads database and the field names have to match. There are about 15 fields in this import that have to be renamed every day, I was just wondering if I could write some kind of query that could update the field names for me. Thanks for any in...

combo box column 1 query
Can anyone tell me what this doesn't work? The function is supposed to refer to the first column combo box. FYI: the data comes of sql server. SELECT Station, TxDatetime, Drawer, SubDrawer, Pkt, GenMedName, BrandMedName, MedID, Amt, [Min], [Max], UserName, UserID, PtID, PaType FROM dbo.phmPYXHx WHERE (UserName = N'[Form]!PX_UserReports!combo0!Column(1)') <-- here is where i refer to column1 of the combo Misty -- Message posted via http://www.accessmonster.com On Tue, 21 Aug 2007 20:28:29 GMT, "Mitchell_Collen via AccessMonster.com&...

Pivot Table with multiple dependent excel spreadsheets
Hey all, Is it possible to create one pivot table that checks in with multiple excel spreadsheets to update itself? FErd -- Frildo Andersen CARE Hawaii, Inc. 606 Coral St. 2nd floor Honolulu, HI 96813 phone: 808-791-6159 fax: 808-791-6198 ...

too complex query
hello I'm using Access 2003 SP3 and here is my query based on single table PARAMETERS StartDate DateTime, EndDate DateTime; SELECT tblCalendarHistory.CalendarID, tblCalendarHistory.Date, tblCalendarHistory.Division FROM tblCalendarHistory WHERE (((tblCalendarHistory.Date) Between "StartDate" And "EndDate") AND ((tblCalendarHistory.Division)=[Forms]![frmDivision]![txtDivision])); At first I created a form for imputting start and End dates, but I was getting "Too complex" message. So I created parameters - same message. If I replace parameters ...

Query Criteria Yes/No
I want to sort on a query criteria only those Yes [Ticked Box] Thanks for any Help..Bob On Mon, 28 May 2007 11:32:06 +1200, "Bob V" <rjvance@ihug.co.nz> wrote: > > >I want to sort on a query criteria only those Yes [Ticked Box] >Thanks for any Help..Bob > "Sort" means to "put a set of records into a particular order". I guess you mean *search* rather than sort. A Yes/No field is actually stored as a number, -1 meaning Yes and 0 meaning No. You can use your choice of -1, Yes, True; or 0, No, False as criteria. John W. V...

Simple DateDiff query
Here it is: I have a form which contains the opening date and the resolved date and I'm trying to workout how many days it takes from opening to resolution. Is there a way to write a sql query to do this? Or can I write a formular in the table to show this? Or can I get a text box on the form to tell me this and then feed this through onto the table? I've been working on this all morning and would appreciate any help you can give me. Thanks, PAR ...

Query Help 01-04-08
I have 2 tables, one is School district, the other is school districts that have projects. I want a query to give me a list of all district and all districts with projects. Since there are 255 districts and 183 districts with projects, I want a list of 255 districts with the ones with no projects blank. In other words: Districts Districts with projects alma alma atkins atkins conway cabot cabot I Just want nothing (a blank) where there is a district with no project. No matter what I do, it only returns the 183 districts. Help! Thanks so much!!!!! Golfi...