Display table name as field in query
Does anyone know if there is a way to display the table name as a field in a
I have a UNION query that merges all of the records from 10 different
tables, but I need a field that indicates which table each record comes from.
Any help would be greatly appreciated.
>Does anyone know if there is a way to display the table name as a field in a
>I have a UNION query that merges all of the records from 10 different
>tables, but I need a field that indicates which table each record comes from.
Just use the table name in a calcula...queries -- outer join
Hi, I want to know if is possible make an OUTER JOIN using
more than two tables, because when I try appear a message that
says is wrong
thanks in advance
Message posted from http://www.ExcelForum.com
No you can't.
and you're in the wrong newsgroup.
>Hi, I want to know if is possible make an OUTER JOIN
>more than two tables, because when I try appear a
>says is wrong
>thanks in advance
>Message posted from http://www.ExcelForum.com/
you probably sh...Union Query and Where Clause
Is it is possible to apply WHERE clause to the entire result of the
Something like this:
(Select f1 from A
Select f1 from B)
where f1 = '1';
I tried to parenthesize the individual selects but it does not work.
So I had to put the where clause in each individual select to filter
Not within the UNION query itself. You'd need to save the UNION query and
then write a different query that uses that UNION query as the data source,
and then put the WHERE clause in that new query:
SELECT * FROM UNIONQuery
WHERE FieldName = "value";
...Looping through Query to create multiple sheets in excel- Just need the loop
I figured out where I should start the loop in order to keep the excel
work open and still be able to add more sheets, but I can't figure out
how to add code to For Next loop to go through a query
"qryManufacturer" and take each one and put them into the string
I always get to this point and I can't figure out how to loop through
a recordset. I have put the string in the query at the bottom.
Public Sub CopyRs2SheetHacked(strSql As String, strWorkBook As String,
Optional strWorkSheet As String, Optional strRange As
'Uses the Excel...Query error
I have a set of queries that load data from an excel spreadsheet. Lately I
have been getting an error that states "Field 'F18' doesn't exist in
destination table 'tblTempProduction.'
I have checked and this field does exist in this table. The strange thing
about it is I do not receive this error everytime I run the series of queries.
It just happens randomly. Any suggestions?
Message posted via AccessMonster.com
...can't get query to group
here is the sql.
can someone help me determine why the query isn't grouping by GoupNumber?
The GroupName is the same for the GroupNumber. ID is unique.
SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
GROUP BY Mid([ID],3,2), Accounts.GroupName, Accounts.ID
HAVING (((Accounts.ID) Like "01*"));
ID GroupNumber GroupName
010101 01 red
010102 01 red
010201 02 blue
010301 03 ...Too difficult!
I don't seem to be able to get an answer to this question -
is it so difficult or am I in the wrong forum?
How can I change format a column in all 30 or so sub-
folders in my INBOX in one go i.e. without doing them
Thanks for any help.
Create your own view in View-> Arrange By-> Current View-> Define Views...
and apply it on first use of the folder
Robert Sparnaaij [MVP-Outlook]
Tips of the month:
-Navigation Pane Tips & Tricks
-Create an Office 2003 CD slipstreamed with Service Pack 1
"Glyn" <anonymous@d...Difficult function: help needed.
In need a function which calculates an overall weighing factor based on
person age and the years the person is working at a company.
For each working year factor 1.
For all working years while older then 41 an addtional factor 0.5.
For all working years while older then 51 an additional factor 0.5.
Thanks for any help.
To be able to use this function easily in each case paste this command
into a Visual Basic Project, Macro Module:
If Years > 51 Then
Factor = 2
ElseIf Years > 41 Then
Factor = 1.5
Factor = 1
Therafter you wil...Can Pivot Table views of a query be saved as a favorite?
....if so, how?
Have the query properties sheet visible (right click on an empty spot on the
top half, if not, or use the toolbar): the second property is Default View.
Specify the one you wish to get.
Hoping it may help,
Vanderghast, Access MVP
<email@example.com> wrote in message
> ...if so, how?
...Difficult but do-able?
In a sheet in which I keep track of questions coming in, I not the date in
and date closed.
in another sheet I want to check how many questions were raised in a month
and how many closed in the same month.
Each question has its own line.
How can I best tackle this?
** Fool on the hill **
If on sheet1 you have these columns:
B: date in (format as date)
C: date closed (format as date)
and on sheet2
A: months (format as number)
B: questions raised
C: question closed in month of raising
Then in sheet2
C2: =SUMPRODUCT(--(MONTH(S...linking two tables in query
I'm trying to link two tables using an ODBC table in query. Not all
records in the left table have an entry in the right table. I've tried
all combinations of the link properties but I either get only those
records that have entries in both tables or each record in the left
table duplicated by the number of records in the right table.
Any advice gratefully received
Message posted from http://www.ExcelForum.com/
You need to create the link in Query by dragging one field onto the other, then double-click on the link line. You'll either want option 2 or option ...Excel 2003/MS Query
I have several spreadhseets set up with sql queries to our financial system.
From time to time I am having issues with the query definition being lost
(almost like someone has pasted values - but they haven't)
(Save query defn is ticked under Data Range Properties)
This is driving me mad - I can't find anyone that seems to have come across
Someone please help !
...DNS return queries through isa2000 for exchange
I recently stared having a problem where our email is being returned without
delivery from comcast email servers. The return message is the following:
<mail.XXXXXX.com #5.5.0 smtp;521-EHLO/HELO from sender ###.###.###.### does
not map to mail.XXXXXX.com in DNS>
(### is the ip address of the isa server)
I'm guessing that they recently started requiring a matching DNS reply
before accepting incoming email. I haven't changed anything on my end so that
is why I assume the change came from them.
My exchange server is on the inside (NATed) of the network. The IP address
we have...Inverting a query
it got to build a database for a company archive. It cotains some
queries. The last query I got to do shall show the unused racks.
If using the following query: SELECT zuordnungfa.archiv,
zuordnungfa.schrank AS zuordnungfa_schrank, zuordnungfa.seite,
zuordnungfa.fa, archiv.schrank AS archiv_schrank
FROM zuordnungfa INNER JOIN archiv ON zuordnungfa.schrank =
the query returns every rack, containing one or more folders. This is
exactly the opposite of what I want. Is there any way to select the
inverse or should I write a new (better) query, and how?
Here are the t...How to query when control param is null?
I want to run a query with criteria based on a form control. Works fine for
nonblank values in the control but when the control is null the query finds
no records, even though records do exist with null values in that field.
Does anybody know how I can fix this?
You cannot use a parameter to find Null. By its nature, Null is not equal to
Null (i.e.: Null = Null will never return True)
If the parameter is Null, do you want only Null values returned, or do you
want all the rows returned?
If the former, your SQL should be something like
WHERE MyField = Forms!MyForm!MyControl OR (MyF...Why is it so difficult???????
I posted an issue yesterday from my home computer. Today at work (WHERE I
NEED THE ANSWER!!!!!!) I can't find the post from yesterday! I've looked and
looked and looked but I can't find it. Searching for the subject doesn't find
it. HOW do I search for posts by me? My signature doesn't work, my email
doesn't work, etc. This is EXTREMELY FRUSTRATING!!!!!!!
If you posted to this newsgroup using this alias I can't find the message.
It was either deleted for some reason, you posted to a different newsgroup,
or used a different user.
--...Very difficult query (?)
I've been struggling with this all day, and although I think I'm near a
solution (actually I thought that 2 hours ago!) it now involves 4 nested
queries and is 'grinding' horribly (i.e. takes 30-40 seconds to run), and
I'm convinced that there must be a better way to get what I need! I really
hope someone can help with this.
I have a table [x confirmed] which includes the following fields:
'month number' (integer)
'cont rate' (number - percent)
'nhsp ees' (number - currency)
'pen pay' (numb...Excel query
I am trying to add a drop-down list to a cell with info
like different brands of smoke detectors and the
corresponding prices. When I select a brand the list
disappears and leaves me with the brand and price in that
Is this possible?
On empty sheet, enter the table (starting from A1)
Let's assume your table fills the range A1:B20
Rename the sheet p.e. as "PriceTable"
Select from menu Insert.Name.Define
Into name field enter some name, p.e. PriceList
Into source field, enter the formula:
=OFFSET($A$1,,,COUNTIF($A...Query Criteria IIf statement
I'm using MSAccess 2003 and this seems like a simple process, especially
compared to some of the things I'm trying to do in my database. In the
criteria of a query field, I am using the following IIf statement:
[FYAGR] is a different field in the same query. When [FYAGR] is greater
than 3,000,000, I get 3 as expected, but no matter how I word the range of
numbers I'm looking for in the false part of the statement, my query returns
no records. If I remove the IIf statement and place '<4' I get the correct
range, but no ...Can't enter data in query datasheet
I have a query (involving multiple tables) that I want to execute and then
use to enter/change data via the datasheet which results from the query.
On most queries, I am able to do that. One this one, I can't. What is the
criteria for determining what queries will allow data entry and which queries
What do I have to do to this query to allow data entry?
SELECT [4GateMeetingTimes].Time, ActivityTbl.Activity_ActivityKey,
ActivityTbl.Activity_Pl...Show only Latest date in reuslts of a query
I have a parent table holding review dates and a child table with audit
trail entries made with dates.
I want to extract all documents with a review date within a range (select
query doing this) and would also like to see only the latest audit trail
entry in the the child table.
At the moment my select query shows a document with all audit trail entries.
Any suggestions on how I can get only the latest audit trail entry would be
Select query left join to ---
Group by query with these fields --
Audit_Identifier Max(audit trail entry da...difficult format
I want to format a code like this: ##.0001.## so then i push it down and gives me 0002 and so on, the other ## r numbers. The problem is that when i do that, it sums in the last two numbers instead of what i want, so i ask if it is possible to format like this: (text)##.(number)0001.(text)## so it guives me the sum of number then do what i want. if it is possible how ca i do that?
You can try this, starting in row 1:
You'll have to adjust the "Row()" number when starting in any other row.
For example,...queries #2
I have a co-worker who wants to be able to do a query within a worksheet - it
should be ridiculosly easy, but I can't seem to figure it out, and Excel
2003's help only speaks of importing queried data into Excel, and that's not
what I want.
"Dave" <Dave@discussions.microsoft.com> wrote ...
> I have a co-worker who wants to be able to do a query within a worksheet - it
> should be ridiculosly easy, but I can't seem to figure it out, and Excel
> 2003's help only speaks of importing queried data into Excel, and that's not
> what...Display Query from form in Foreground
I have a form upon which the user selects display, filter and sort fields.
He then clics the submit button and a query is built and then displayed with
the following code:
DoCmd.OpenQuery "Queryname", acNormal, acEdit
The query works but shows up in the background and cant be clicked on until
the form is closed. The idea was to create a form on which the user could do
adhoc queries until he gets it right and then click another button to do the
Excel extract. Is there a way to show the query in the foreground?
On Tue, 22 Jan 2008 17:54:27 -0800, rmcompute wrote:
> I ha...Dilemma with form bound to a query....
How can I enter data into my tables through a form bound to a query? There
must be a way but it eludes me if there is. I am fairly new at this so
lots of things baffle me at this point. Please help. Pat
"If you can find a path with no obstacles, it probably doesn't lead
"Dupatt" <Dupatt@yahoo.com> wrote in message
> How can I enter data into my tables through a form bound to a query?
> There must be a way but it eludes me if there is. I am fairly new at
> this so ...