Query left outer join

I have the following query that I wish to return ALL vehicles and then show 
all trips applicable to those vehicles for the current date (pulled from a 
form). The problem I'm having is that the query returns only the vehicles 
that have trips for the day, not ALL vehicles. 

SELECT vehicle.vehicle_label, trips.trip_from, trips.trip_to, 
trips.pickup_date, trips.drop_date, trips.closed, trips.timeField, 
vehicle.SubContractor
FROM vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey
WHERE (((trips.pickup_date)=[Forms]![frmAllocation]![AllocationDateField]) 
AND ((vehicle.SubContractor)=False) AND ((vehicle.sold)=False) AND 
((vehicle.trailer)=False)) OR 
(((trips.drop_date)=[Forms]![frmAllocation]![AllocationDateField]))
ORDER BY vehicle.vehicle_label, trips.timeField;


Thankyou in advance.
0
Utf
3/18/2010 7:44:01 AM
access 16762 articles. 3 followers. Follow

5 Replies
789 Views

Similar Articles

[PageSpeed] 43

Hi Jared,

Your WHERE clause is limiting by pickup_date or drop_date, so if there is no 
date in these fields (there's no trip for that day) it will not return a 
record.

I think you need something like (untested):
SELECT v.vehicle_label, t.trip_from, t.trip_to, t.pickup_date, t.drop_date, 
t.closed, t.timeField, v.SubContractor
FROM vehicle AS v LEFT JOIN (
SELECT trip_from, trip_to, pickup_date, drop_date, closed, timeField, 
vehicle_fkey
FROM trips
WHERE trips.pickup_date=[Forms]![frmAllocation]![AllocationDateField] OR 
trips.drop_date=[Forms]![frmAllocation]![AllocationDateField]) AS t
ON v.vehicle_label = t.vehicle_fkey
WHERE v.SubContractor=False AND v.sold=False AND v.trailer=False
ORDER BY v.vehicle_label, t.timeField;

This uses a subquery to return all the trips with either a pickup_date or a 
drop_date equal to the form parameter, and then left joins that result to the 
vehicle table.  It *should* work, but as I said, it's untested.

Cheers and hope this helps,
Alex.


"Jared" wrote:

> I have the following query that I wish to return ALL vehicles and then show 
> all trips applicable to those vehicles for the current date (pulled from a 
> form). The problem I'm having is that the query returns only the vehicles 
> that have trips for the day, not ALL vehicles. 
> 
> SELECT vehicle.vehicle_label, trips.trip_from, trips.trip_to, 
> trips.pickup_date, trips.drop_date, trips.closed, trips.timeField, 
> vehicle.SubContractor
> FROM vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey
> WHERE (((trips.pickup_date)=[Forms]![frmAllocation]![AllocationDateField]) 
> AND ((vehicle.SubContractor)=False) AND ((vehicle.sold)=False) AND 
> ((vehicle.trailer)=False)) OR 
> (((trips.drop_date)=[Forms]![frmAllocation]![AllocationDateField]))
> ORDER BY vehicle.vehicle_label, trips.timeField;
> 
> 
> Thankyou in advance.
0
Utf
3/18/2010 9:08:01 AM
Thankyou Alex, perfect.

"Tokyo Alex" wrote:

> Hi Jared,
> 
> Your WHERE clause is limiting by pickup_date or drop_date, so if there is no 
> date in these fields (there's no trip for that day) it will not return a 
> record.
> 
> I think you need something like (untested):
> SELECT v.vehicle_label, t.trip_from, t.trip_to, t.pickup_date, t.drop_date, 
> t.closed, t.timeField, v.SubContractor
> FROM vehicle AS v LEFT JOIN (
> SELECT trip_from, trip_to, pickup_date, drop_date, closed, timeField, 
> vehicle_fkey
> FROM trips
> WHERE trips.pickup_date=[Forms]![frmAllocation]![AllocationDateField] OR 
> trips.drop_date=[Forms]![frmAllocation]![AllocationDateField]) AS t
> ON v.vehicle_label = t.vehicle_fkey
> WHERE v.SubContractor=False AND v.sold=False AND v.trailer=False
> ORDER BY v.vehicle_label, t.timeField;
> 
> This uses a subquery to return all the trips with either a pickup_date or a 
> drop_date equal to the form parameter, and then left joins that result to the 
> vehicle table.  It *should* work, but as I said, it's untested.
> 
> Cheers and hope this helps,
> Alex.
> 
> 
> "Jared" wrote:
> 
> > I have the following query that I wish to return ALL vehicles and then show 
> > all trips applicable to those vehicles for the current date (pulled from a 
> > form). The problem I'm having is that the query returns only the vehicles 
> > that have trips for the day, not ALL vehicles. 
> > 
> > SELECT vehicle.vehicle_label, trips.trip_from, trips.trip_to, 
> > trips.pickup_date, trips.drop_date, trips.closed, trips.timeField, 
> > vehicle.SubContractor
> > FROM vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey
> > WHERE (((trips.pickup_date)=[Forms]![frmAllocation]![AllocationDateField]) 
> > AND ((vehicle.SubContractor)=False) AND ((vehicle.sold)=False) AND 
> > ((vehicle.trailer)=False)) OR 
> > (((trips.drop_date)=[Forms]![frmAllocation]![AllocationDateField]))
> > ORDER BY vehicle.vehicle_label, trips.timeField;
> > 
> > 
> > Thankyou in advance.
0
Utf
3/18/2010 9:02:01 PM

"Jared" wrote:

> Thankyou Alex, perfect.
> 
> "Tokyo Alex" wrote:
> 
> > Hi Jared,
> > 
> > Your WHERE clause is limiting by pickup_date or drop_date, so if there is no 
> > date in these fields (there's no trip for that day) it will not return a 
> > record.
> > 
> > I think you need something like (untested):
> > SELECT v.vehicle_label, t.trip_from, t.trip_to, t.pickup_date, t.drop_date, 
> > t.closed, t.timeField, v.SubContractor
> > FROM vehicle AS v LEFT JOIN (
> > SELECT trip_from, trip_to, pickup_date, drop_date, closed, timeField, 
> > vehicle_fkey
> > FROM trips
> > WHERE trips.pickup_date=[Forms]![frmAllocation]![AllocationDateField] OR 
> > trips.drop_date=[Forms]![frmAllocation]![AllocationDateField]) AS t
> > ON v.vehicle_label = t.vehicle_fkey
> > WHERE v.SubContractor=False AND v.sold=False AND v.trailer=False
> > ORDER BY v.vehicle_label, t.timeField;
> > 
> > This uses a subquery to return all the trips with either a pickup_date or a 
> > drop_date equal to the form parameter, and then left joins that result to the 
> > vehicle table.  It *should* work, but as I said, it's untested.
> > 
> > Cheers and hope this helps,
> > Alex.
> > 
> > 
> > "Jared" wrote:
> > 
> > > I have the following query that I wish to return ALL vehicles and then show 
> > > all trips applicable to those vehicles for the current date (pulled from a 
> > > form). The problem I'm having is that the query returns only the vehicles 
> > > that have trips for the day, not ALL vehicles. 
> > > 
> > > SELECT vehicle.vehicle_label, trips.trip_from, trips.trip_to, 
> > > trips.pickup_date, trips.drop_date, trips.closed, trips.timeField, 
> > > vehicle.SubContractor
> > > FROM vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey
> > > WHERE (((trips.pickup_date)=[Forms]![frmAllocation]![AllocationDateField]) 
> > > AND ((vehicle.SubContractor)=False) AND ((vehicle.sold)=False) AND 
> > > ((vehicle.trailer)=False)) OR 
> > > (((trips.drop_date)=[Forms]![frmAllocation]![AllocationDateField]))
> > > ORDER BY vehicle.vehicle_label, trips.timeField;
> > > 
> > > 
> > > Thankyou in advance.

Hi Alex,

I need to add another table to the query called "custom_trips" and can't 
seem to get the code right for the second left join

basically i need add:

LEFT JOIN SELECT *
FROM custom_trips
WHERE 
(custom_trips.custom_date)=[Forms]![frmAllocation]!AllocationDateField]) 
as c ON v.vehicle_label = c.custom_vehicle_fkey

I can't seem to find any post where a second left join is included as a 
subquery per your previous help.

Thanks

Jared

0
Utf
4/11/2010 3:10:01 AM
The SQL should look something like the following.

SELECT v.vehicle_label, t.trip_from, t.trip_to
, t.pickup_date, t.drop_date
, t.closed, t.timeField, v.SubContractor
FROM (vehicle AS v LEFT JOIN
   (
    SELECT trip_from, trip_to, pickup_date
    , drop_date, closed, timeField, vehicle_fkey
    FROM trips
    WHERE trips.pickup_date=[Forms]![frmAllocation]![AllocationDateField]
    OR trips.drop_date=[Forms]![frmAllocation]![AllocationDateField]
   ) AS t
ON v.vehicle_label = t.vehicle_fkey)
LEFT JOIN
    (
     SELECT *
     FROM custom_trips
     WHERE custom_trips.custom_date=
          [Forms]![frmAllocation]!AllocationDateField]
    ) as c
ON v.vehicle_label = c.custom_vehicle_fkey

WHERE v.SubContractor=False AND v.sold=False AND v.trailer=False
ORDER BY v.vehicle_label, t.timeField;




John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Jared wrote:
> 
> "Jared" wrote:
> 
>> Thankyou Alex, perfect.
>>
>> "Tokyo Alex" wrote:
>>
>>> Hi Jared,
>>>
>>> Your WHERE clause is limiting by pickup_date or drop_date, so if there is no 
>>> date in these fields (there's no trip for that day) it will not return a 
>>> record.
>>>
>>> I think you need something like (untested):
>>> SELECT v.vehicle_label, t.trip_from, t.trip_to, t.pickup_date, t.drop_date, 
>>> t.closed, t.timeField, v.SubContractor
>>> FROM vehicle AS v LEFT JOIN (
>>> SELECT trip_from, trip_to, pickup_date, drop_date, closed, timeField, 
>>> vehicle_fkey
>>> FROM trips
>>> WHERE trips.pickup_date=[Forms]![frmAllocation]![AllocationDateField] OR 
>>> trips.drop_date=[Forms]![frmAllocation]![AllocationDateField]) AS t
>>> ON v.vehicle_label = t.vehicle_fkey
>>> WHERE v.SubContractor=False AND v.sold=False AND v.trailer=False
>>> ORDER BY v.vehicle_label, t.timeField;
>>>
>>> This uses a subquery to return all the trips with either a pickup_date or a 
>>> drop_date equal to the form parameter, and then left joins that result to the 
>>> vehicle table.  It *should* work, but as I said, it's untested.
>>>
>>> Cheers and hope this helps,
>>> Alex.
>>>
>>>
>>> "Jared" wrote:
>>>
>>>> I have the following query that I wish to return ALL vehicles and then show 
>>>> all trips applicable to those vehicles for the current date (pulled from a 
>>>> form). The problem I'm having is that the query returns only the vehicles 
>>>> that have trips for the day, not ALL vehicles. 
>>>>
>>>> SELECT vehicle.vehicle_label, trips.trip_from, trips.trip_to, 
>>>> trips.pickup_date, trips.drop_date, trips.closed, trips.timeField, 
>>>> vehicle.SubContractor
>>>> FROM vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey
>>>> WHERE (((trips.pickup_date)=[Forms]![frmAllocation]![AllocationDateField]) 
>>>> AND ((vehicle.SubContractor)=False) AND ((vehicle.sold)=False) AND 
>>>> ((vehicle.trailer)=False)) OR 
>>>> (((trips.drop_date)=[Forms]![frmAllocation]![AllocationDateField]))
>>>> ORDER BY vehicle.vehicle_label, trips.timeField;
>>>>
>>>>
>>>> Thankyou in advance.
> 
> Hi Alex,
> 
> I need to add another table to the query called "custom_trips" and can't 
> seem to get the code right for the second left join
> 
> basically i need add:
> 
> LEFT JOIN SELECT *
> FROM custom_trips
> WHERE 
> (custom_trips.custom_date)=[Forms]![frmAllocation]!AllocationDateField]) 
> as c ON v.vehicle_label = c.custom_vehicle_fkey
> 
> I can't seem to find any post where a second left join is included as a 
> subquery per your previous help.
> 
> Thanks
> 
> Jared
> 
0
John
4/11/2010 1:50:10 PM
Thankyou John. Works great. just had to add a [ in the where clause.

Thanks for the quick response too. This forum is an extremely valuable 
resource.



"John Spencer" wrote:

> The SQL should look something like the following.
> 
> SELECT v.vehicle_label, t.trip_from, t.trip_to
> , t.pickup_date, t.drop_date
> , t.closed, t.timeField, v.SubContractor
> FROM (vehicle AS v LEFT JOIN
>    (
>     SELECT trip_from, trip_to, pickup_date
>     , drop_date, closed, timeField, vehicle_fkey
>     FROM trips
>     WHERE trips.pickup_date=[Forms]![frmAllocation]![AllocationDateField]
>     OR trips.drop_date=[Forms]![frmAllocation]![AllocationDateField]
>    ) AS t
> ON v.vehicle_label = t.vehicle_fkey)
> LEFT JOIN
>     (
>      SELECT *
>      FROM custom_trips
>      WHERE custom_trips.custom_date=
>           [Forms]![frmAllocation]!AllocationDateField]
>     ) as c
> ON v.vehicle_label = c.custom_vehicle_fkey
> 
> WHERE v.SubContractor=False AND v.sold=False AND v.trailer=False
> ORDER BY v.vehicle_label, t.timeField;
> 
> 
> 
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> Jared wrote:
> > 
> > "Jared" wrote:
> > 
> >> Thankyou Alex, perfect.
> >>
> >> "Tokyo Alex" wrote:
> >>
> >>> Hi Jared,
> >>>
> >>> Your WHERE clause is limiting by pickup_date or drop_date, so if there is no 
> >>> date in these fields (there's no trip for that day) it will not return a 
> >>> record.
> >>>
> >>> I think you need something like (untested):
> >>> SELECT v.vehicle_label, t.trip_from, t.trip_to, t.pickup_date, t.drop_date, 
> >>> t.closed, t.timeField, v.SubContractor
> >>> FROM vehicle AS v LEFT JOIN (
> >>> SELECT trip_from, trip_to, pickup_date, drop_date, closed, timeField, 
> >>> vehicle_fkey
> >>> FROM trips
> >>> WHERE trips.pickup_date=[Forms]![frmAllocation]![AllocationDateField] OR 
> >>> trips.drop_date=[Forms]![frmAllocation]![AllocationDateField]) AS t
> >>> ON v.vehicle_label = t.vehicle_fkey
> >>> WHERE v.SubContractor=False AND v.sold=False AND v.trailer=False
> >>> ORDER BY v.vehicle_label, t.timeField;
> >>>
> >>> This uses a subquery to return all the trips with either a pickup_date or a 
> >>> drop_date equal to the form parameter, and then left joins that result to the 
> >>> vehicle table.  It *should* work, but as I said, it's untested.
> >>>
> >>> Cheers and hope this helps,
> >>> Alex.
> >>>
> >>>
> >>> "Jared" wrote:
> >>>
> >>>> I have the following query that I wish to return ALL vehicles and then show 
> >>>> all trips applicable to those vehicles for the current date (pulled from a 
> >>>> form). The problem I'm having is that the query returns only the vehicles 
> >>>> that have trips for the day, not ALL vehicles. 
> >>>>
> >>>> SELECT vehicle.vehicle_label, trips.trip_from, trips.trip_to, 
> >>>> trips.pickup_date, trips.drop_date, trips.closed, trips.timeField, 
> >>>> vehicle.SubContractor
> >>>> FROM vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey
> >>>> WHERE (((trips.pickup_date)=[Forms]![frmAllocation]![AllocationDateField]) 
> >>>> AND ((vehicle.SubContractor)=False) AND ((vehicle.sold)=False) AND 
> >>>> ((vehicle.trailer)=False)) OR 
> >>>> (((trips.drop_date)=[Forms]![frmAllocation]![AllocationDateField]))
> >>>> ORDER BY vehicle.vehicle_label, trips.timeField;
> >>>>
> >>>>
> >>>> Thankyou in advance.
> > 
> > Hi Alex,
> > 
> > I need to add another table to the query called "custom_trips" and can't 
> > seem to get the code right for the second left join
> > 
> > basically i need add:
> > 
> > LEFT JOIN SELECT *
> > FROM custom_trips
> > WHERE 
> > (custom_trips.custom_date)=[Forms]![frmAllocation]!AllocationDateField]) 
> > as c ON v.vehicle_label = c.custom_vehicle_fkey
> > 
> > I can't seem to find any post where a second left join is included as a 
> > subquery per your previous help.
> > 
> > Thanks
> > 
> > Jared
> > 
> .
> 
0
Utf
4/11/2010 10:55:01 PM
Reply:

Similar Artilces:

Linking queries on a report
Hello all, I built a number of queries that all tie to the same table. Each query sorts the data differently, but comes from the same data source. I would like to show the results of these queries on the same report, but I get and error message that the wizard can't connect the queries. How can I display multiple queries on the same report. I've tried subreports, but they don't seem to export as well. Thanks b Brennan What does "export" have to do with this? If I have multiple sets of results I want to show in one place, I create an empty "main"...

min query
Hi, How do I get the 2nd minimum value and the 3rd minimum from a table after I have done MIN query for the 1st minimum value? SELECT dbo_CON.SC, dbo_CON.SN, Min(dbo_CON.SQ) AS MinOfSQ FROM dbo_CON GROUP BY dbo_CON.SC, dbo_CON.SN HAVING (((dbo_CON.SC)<>49)); Thanks, Hi Jerry, This will give me three different SC SN records. I am looking for same SC SN records that has next min SQ. table SC SN SQ 1 100 1 1 100 2 1 100 3 1 100 ...

Cannot join on Social Security Num
I have a table that I am trying to add an ID number into from another. The two tables have a Social Security Number that is the only way to link the tables. I am trying to change that I am first doing a select query before the update query When I add both tables to a query and tie them together by the Social Security Num, (regular join, not outer or inner) I get zero records retrieved. There are matching Social Security Nums in both tables though The two SocSecNum fields have input picture clauses 000\-00\-0000;0;_ Both fields in both tables are the same length and input pictures ...

make query from to 64 bit system
I have a application need to be compile for 64 bit system. how can i read data from vb.net 2005. hi Jennifer, On 09.12.2009 17:36, Jennifer wrote: > I have a application need to be compile for 64 bit system. how can i read > data from vb.net 2005. First of all, I don't see the point why you're asking this question in an Access newsgroups. Further more, you should really be more spefific... mfG --> stefan <-- ...

<> data query
I have two tables and both have a field called tag_id. The first table "my2003" has about 5000 records. The second table "monitor2003" is a subset of table "my2003" and has about 3000 records. I need help building a query that will give me the records in table "my2003" where the "tag_id" field is <> to the "tag_id" field in the "monitor2003" table. Also which type of joining of the two tables is needed? Use the query wizard to create an unmatch record query that will list all the records in my2003 but are n...

Different queries but the same?
Hi Everyone, Access 2003. I accidently double clicked on the wrong thing in a new query based on another query. In the query grid, I added the qryVentThpyWithinLastArea and double clicked on the *. It only filled one column of my grid but when I ran the query, it showed all the fields from the query. I then dragged down Area field and added my criteria. I had been taught to double click on the blue title bar and then drag all the fields to the grid then add my criteria. Both ways give me the same data. What is the differnce? I pasted the sql below so you can see what's going...

trim in query
Hi All I am trying to trim a leading space at the start of each field in my "Customers" table which is an Exel spreadsheet imported using "TransferSpreadsheet" After researching the internet I have been able to get this to work using, for example: CustomerName: Trim([F3]) in the Field line of a select query ("CustomersTrimQry"). My promlem is that when I append the data from "CustomersTrimQry" to the "Customers" table the space returns. Prior to using trim I ran 3 queries from the original "CustomersImport" table, "C...

Database SQL query
Sorry for this post here. If someone knows a group I can go to for this question, please let me know. I have a program in MFC that accesses and updates an Access database. The problem is I can't figure out how to add a field which contains a word with a single apostrophe ie (St. Vincent's). I'm using the CDaoDatabase and CDaoRecordset classes. I understand that DAO is interpreting the apostrophe as some type of terminator but there must be a way around the issue. MS Access has information on how to do it in Access VB but that doesn't seem to work in MFC. Thanks Ughh ...

Left Join dilemma is this possible?
I am trying to create a recordset in Excel 2000 from two Access 2000 tables to include all items from the first table "Liquor" and only items from the second table "LiquorOrders" that match either a date or an order number. I have 19 items in the first table and I would like to select all 19 items and only these 19 items with a Status = True, with corresponding data from the "LiquorOrders" with blanks where an order for an item was not placed on that specific date or order number. This at first seemed possible but after much researching and experimentation I hav...

How do I make column on the left side of Outlook[inbox,Sent etc.]
I want to add a column on the left side when I am in Outlook that shows (Inbox, Sent Items, Deleted items etc...) How is this done when in Outlook, Windows XP (SP3) Home Edition? "John" <John@discussions.microsoft.com> wrote in message news:6FA61CD3-8CE3-4DFA-B7B7-A27BCF0C29A6@microsoft.com... > I want to add a column on the left side when I am in Outlook that shows > (Inbox, Sent Items, Deleted items etc...) How is this done when in Outlook, > Windows XP (SP3) Home Edition? Enable the Navigation Pane. Press Alt+F1 or click View>Navigation Pane. ...

vlookup problems with left
I'm using vlookup,i have a 10 digit number which i have to mach with 9 digit number (last digit is some kind of control digit) and when i use left to take only the first 9 digits =VLOOKUP( LEFT(F200;9);W198:W201;1;FALSE) i get #N/A if i manuallly delete the 10th digit i get mached data. is there a problem with using left on vlookup? =left() returns text. That will never match a real number. I'd take the integer amount after dividing by 10. =vlookup(int(f200/10);w198:w201;1;false) But if you're really only looking to see if there's a match, you could use: ...

MS query and Firebird
I am accessing a Firebird data base with MS Query into Excel. I am trying to change the column heading (column name) but as I writte a "Newname" in Column Heading MS Query dont accept. I try to change directlly the SQL statement with "AS" clause and without "AS" clause but MS Query dont accept. I dont know if the format of select statement is different in Firebird data base or is a problem with MS Query. JC. ...

Did Publisher 2007 get left behind?
Did Publisher 2007 somehow escape the Office 2007 redesign that was done to Word, Excel, etc. I don't see the ribbon and it doesn't appear to be using the .pubx filetype. Publisher is Office's black sheep... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Lurfys Maw" <invalid@invalid.invalid> wrote in message news:haql44p59ts3k3j7b9rd2nkldnliqrk9qd@4ax.com... > Did Publisher 2007 somehow escape the Office 2007 redesign that was > done to Word, Excel, etc. I don't see the ribbon and it doesn'...

Join based on next closest value (like Excel VLOOKUP)
Trying to do something similar to a VLOOKUP (Excel) in an Access 2003 query. I have the following tables: JOBS Job,Quantity A,96 B,256 C,300 D,4299 COSTS Quantity,Cost 0, $1000 100, $1200 200, $1500 300, $2000 400, $2500 500, $3000 I need a query that takes JOBS.Quantity, looks it up in COSTS.Quantity and find the cost for the NEXT LOWEST quantity. (Example: Job B has a quantity of 256 and the next LOWEST quantity from COSTS is 200 so Job B costs $1500.) The results should be as follows: JOBS.Job,COSTS.Cost A,$1000 B,$1500 C,$2000 D,$3000 This would be ...

Exporting Mail box after employee has left company
I'd like to export a mailbox from Exchange 2003. The employee has left the company, and allthough I haven't deleted his user account yet, I would like to know if it is possible to export his account without actually logging in with his username & password and opening outlook as that user. Thanks, Nate Weldon yes; use exmerge.exe to accomplish this... "Nate Weldon" <NateWeldon@discussions.microsoft.com> wrote in message news:674CD1FE-F33E-4734-BC2C-B20210B95A3C@microsoft.com... > I'd like to export a mailbox from Exchange 2003. The employee has left th...

UDF XLA and SQL queries
Newbie question. Apologies in advance if it's too simplistic or in the wrong forum. I want to add an UDF to Excel. This UDF queries a SQL database. Say the function name is 'MyFunc'; I want to be able to have (in a cell) =MyFunc("ABC") return some data from the SQL database (for example here, say the Product Description for product "ABC"). The user needs to select the database the first time he uses the workbook. The UDF is called often in the worksheets of a given workbook so I would prefer to open the connection once at the beginning and...

Parse field in query
I have a table called MasterData with a field called Originator. The field data looks like this... Lou Santana Steven Sentry/Miami/PU/WC/US CN=Kathy T. Buon/OU=Miami/OU=BU/O=WC/C=US Terry J Leif I need it to look like this... Lou Santana Steven Sentry Kathy T. Buon Terry J Leif How can this be parsed?? -- deb What a mess that you're stuck with! Below will look for the first / and return the string before it in a query. If there isn't a / it returns the entire string. Of course this doesn't help with the CN= in CN=Kathy T. Buon. Parsed: IIf(InStr([Originator],"/")...

Project Manager 2007 queries
I am considering purchasing Proect Manager for my my Planner, we are a small office who a familiar with the basics but have a few queries which are a little more detailed, I hope someone may able to advise on:- 1. Reporting - What are the options for importing and exporting documents and the number of format types, are they clear and easy to learn and use ? 2. Graphs - Is graphical data readily available, are there differing options of graphs and are the tools for producing them clear and easy to use ? 3. Sand pits - Are there areas where 'offline' working is availabl...

Using form to enter "query criteria" (between values)
Good morning everyone, I want to create a form with two boxes, where first box is lower limit of the value and the second upper limit. This values should be used in my query as filter criteria. Like this (query filter criteria): > "textbox1" AND < "textbox2" Does anyone know how I can build this in a form (i.e. connect my textboxes and query criteria)? Kindly, Mikael Sweden By thinking about for another minute I found the (simple) answer. In the query criteria I put the following expression: BETWEEN [forms].[nameofform].[NameofDatefield1] AND [forms].[name...

Need help getting query to reference a field in a subform
I am trying to run a query using a specific field in a subform as the criteria. The query is displaying one combo box from the Form, as well as several fields from its own subform. My Form name is: FormGrantManagement My Subform name is: TableMOA subform My Subform's subform is: TableCostReports The field in the subform I want to reference is: MOAID I have found other help topics that lead me to think that a criteria along the lines of [Forms]![FormGrantManagement]![TableMOA subform]! [Form]![MOAID] is what I need to use, and I have tried multiple variations of this but witho...

Save Query Results as table to another Access Database
I have an access database that users use to pull reports (stored procs in sql server) and it paste the data on 4 sheets in excel. Occasionally the reports are too big for excel. I'd like to develop a similar database that saves the results of the 4 stored procs as tables in a new Access database. right now I have it returning the results as a query. How do I save the results in another database? Thanks, -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 Hi Billy The following SQL statement should do it for you: SELECT * INTO [TableName] I...

Append Query Fails, MS Access 2000 bug?
I appear to be having a problem with an append query. I created a select query, converted the query to a make table query. The query created the table, NewTable, and inserted the data. I then modified the query again to an append query. The data in NewTable was deleted, then I ran the append query... everything ran fine. I opened NewTable in Design View, saved the table (NO changes were made). I re-ran the append query and it failed: Microsoft Access can't append all the records in the append query. Microsoft Access set 0 field(s) to Null due to a type conversion failure, and i...

Query help please 04-08-10
I have two combo boxes. Location and Super1 The location box lets you select a location where a staff member is working at.For example U3 B-days. The Super1 box lets you select their supervisor who works U3 B-days. As it stands right now it will work. The problem is that it will show all Supervisors through out the whole place. I found a way where I can select from the Location box where it will pull up just the supervisors for that location in the Super1 box. That works so far so good. Now, where the problem comes in is when the staff member is a supervisor. For example: Staf...

How to query multiple zip codes
I have a database of over 380k records that I need to separate out by zip codes. What I am doing is getting a 50 mile radius of my directors across the country and this program comes up with a list of zip codes in that radius. Just for OR, I have 7 pages of zip codes (182 zips). Is there an easy way in Access to get only those zips? Some I can use the operator between "97005-97024", but I can't do the entire list that way. I've always wanted to figure this out. Thanks for any help to make this easier for me. I have about 48 regions to do. Hi Jerry: I do have ...

Storing Information From Queries?
I'm working with a 1gb database and have about 20 queries that analyze and summarize the database in different ways. I tried running a separate query that runs all 20 of these queries, but it's just too much for my computer. Is there a way to, maybe, write a macro that runs each query individually, and stores the result from each query? Any ideas on how to automate the running of these queries, and store the results? Edit/Delete Message soma wrote: >I'm working with a 1gb database and have about 20 queries that analyze and >summarize the database in different...