Access Join Output

Apr 12, 2007

I have two access tables that I am joining. In the opty table I want all records that meet the WHERE clause and in the activity table I want any/all that meet the join clause. The opty table has a one to many relation to the activity table. The join works correctly, it is the display that I am having problems with. I want the output display as an opty then under that all the activities associated to the opty in ASC date order. My problem is I have a memo field in the opty table I want to display but if I put a GROUP BY clause in the sql then I only get part of the memo field (I know why this is happening). So, I tried to leave out the GROUP BY in the sql and use the OUTPUT GROUP BY in the display section. This works except if there are numerous activities associated to an opportunity then the opportunity (sometimes) will be displayed twice with different activities listed. Can some please help.

If I use the the ORDER BY, and put CDATE(acty_begin_dt), I get a NULL error. Which, I assume is because some of the opty's may not have an activity associated to it.

Below is the code I am using and it works fine except, the activities are not in the correct order. (I am using this query in a cold fusion application using access db)
Thanks


SELECT opty_row_id, opty_name, opty_created_by, opty_stage, opty_type, acty_type, acty_created_by, acty_status, acty_opty_row_id, acty_desc, acty_comments, opty_primary, opty_create_dt, opty_close_dt, opty_est_value, opty_desc, acty_row_id, acty_begin_dt, acty_end_dt
FROM opty LEFT JOIN activity ON (opty.opty_row_id = activity.acty_opty_row_id)
WHERE (Trim(opty_primary) = '#form.rpt_user_name#' AND (CDate(opty_create_dt) between #get_first# AND #get_last#)) OR (Trim(opty_primary) = '#form.rpt_user_name#' AND (CDate(opty_create_dt) < #past_date# AND (Trim(opty_stage = '01 - Prospecting') OR Trim(opty_stage = '04 - Opportunity') OR Trim(opty_stage = '03 - Qualification'))))
GROUP BY opty_row_id, acty_row_id, acty_begin_dt, opty_name, opty_created_by, opty_stage, opty_type, acty_type, acty_created_by, acty_status, acty_opty_row_id, opty_primary, opty_create_dt, opty_close_dt, opty_est_value, opty_desc, acty_end_dt, acty_desc, acty_comments
ORDER BY CDate(opty_create_dt) Desc

View Replies


ADVERTISEMENT

Access Output Problem

Sep 27, 2005

hi sir

I have problem printing out output to line chart based on below..

below got #N/A N.A. #N/A N.A.

StockDate Price Volume
25/09/2003 7.84 6667785
26/09/2003 7.77 3849075
29/09/2003 7.69 4754840
30/09/2003 7.23 22613852
01/10/2003 #N/A N.A. #N/A N.A.
02/10/2003 #N/A N.A. #N/A N.A.
03/10/2003 #N/A N.A. #N/A N.A.
06/10/2003 #N/A N.A. #N/A N.A.
07/10/2003 #N/A N.A. #N/A N.A.
08/10/2003 7.03 6920790
09/10/2003 7.03 2586686
10/10/2003 7.27 7037571

so can the output work above based on N/A?
can the chart director recognise it or cannot recognise?
i try it but it gives error when it shows N/A in access
so how to resolve it when N/A is needed for value for Price..
or got another alternatives?


ellen

View 3 Replies View Related

Reports :: Output To PDF Different Than Printed Output

Aug 20, 2013

I'm using Access 2007 SP3.

Whenever I export reports to PDF, the output appears zoomed and clipped. No extra pages are generated as they would be if I'm going over margins, it's just the report is clipped.

The report looks perfect in preview mode, and it looks perfect when going to an actual printer. However, when using OutputTo to save it as a PDF, this is when the report content is clipped.

Here is the code I'm using:

DoCmd.OpenReport MyReport, acViewPreview
DoCmd.OutputTo acOutputReport, "", acFormatPDF, "MyReport.pdf"
DoCmd.Close acReport, MyReport

I open the report in preview mode first so events are fired that show/hide various objects based on fields in the recordset.

I've tried reinstalling, and I've tried this on two different machines, one running Windows 7 and one running Windows Server 2008...both with the same results.

View 5 Replies View Related

Access To Create A Excel Table Like Output

Jan 5, 2012

I have another request for you. I did all the analysis. My final query looks as follows

Jan_N-1, Feb_N-1, Mar_N-1, Jan_N-2, Feb_N-2, Mar_N-2
0.98......... 0.90..... 0.88....... 0.95....... 0.88...... 0.70

but I need Access to create a excel table like output. which looks as like

.......Jan. Feb. Mar
N-1 0.98 0.90 0.88
N-2 0.95 0.88 0.70

View 4 Replies View Related

How To Add Linked Tables Objects To This Access Query Output?

Jul 14, 2005

Hi everybody. I got a query that displays object name and object type of access 2000 db. Unfortuenly it dose not display the linked tables objects(linked to tables in acccess 2000 db). could an expert tell me how i can fix this query so it displays linked tables object as well.Thanks




SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16))
ORDER BY MsysObjects.Name;

View 3 Replies View Related

How To Add Linked Tables Objects To This Access Query Output?

Jul 14, 2005

Hi everybody. I got a query that displays object name and object type of access 2000 db. Unfortuenly it dose not display the linked tables objects(linked to tables in acccess 2000 db). could an expert tell me how i can fix this query so it displays linked tables object as well.Thanks


Code:SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectTypeFROM MsysObjectsWHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16))ORDER BY MsysObjects.Name;

View 2 Replies View Related

Modules & VBA :: Routine To Output Access Report To PDF Using Fields

Jul 3, 2013

I have a routine that outputs an MS Access report as a pdf file which works OK. It does not open the file which is what I want.

DoCmd.OutputTo acReport, "rptTransactionInvoiceExcVAT", acFormatPDF, "C:UsersJEFF1Documents
ptTransactionInvoiceExc VAT.pdf", False

What I would like to do is output the pdf file with a Customer name and number from the field Me.txtAmazonBuyer. I have tried:

DoCmd.OutputTo acReport, "rptTransactionInvoiceExcVAT", acFormatPDF, "C:UsersJEFF1Documents" + ([Me.txtAmazonBuyer]) + ".pdf", False

But no joy.

View 7 Replies View Related

Using Switch Statement In Access Query - Output Integer?

Mar 27, 2013

I am trying to use a switch statement in a access query and i be leave i have the code right but for some reason it will only output a string i need it to output an long integer . (All the columns that are in the statement are Numbers).

Code:
columnName:Switch (([column1]<>0), [column1], ([column2]<>0), [column2], ([column3]<>0), [column3],([column4]<>0),[column4],([column4]=0),0)

Outputs a string i need an long integer

I have tried

Code:
columnName:Switch (([column1]<>0), CLng(column1), ([column2]<>0), CLng(column2), ([column3]<>0), CLng(column3),([column4]<>0),CLng(column4),([column4]=0),0)

and does not work just outputs a string. It outputs the right number but not as an integer...

View 1 Replies View Related

Reports :: Save Report As PDF - Output To Action Cancelled With Access VB Macro

Jan 12, 2015

One of the users of my application, reports an error when saving a report to pdf by VBA code. She’s using Access 2013, on Windows 8.1.

The error is “error 2501 output to action was cancelled with access vb macro”

The VBA code is

StrReport = “RepInvoice”
StrDocumentName = “C:UsersMaryDocumentsAccessInvoice.pdf”
DoCmd.OpenReport StrReport, acViewPreview
DoCmd.OutputTo acOutputReport, StrReport, "PDFFormat(*.pdf)", StrDocumentName, False
DoCmd.Close acReport, StrReport, acSaveNo

This error occurs at the OutputTo line. This code is part of an user form with a button “save as PDF”.
When this code is run, the report is visible as an example (as meant to be) on the screen. The code stops at the OutputTo line.

When I use
DoCmd.OutputTo acOutputReport, StrReport, acFormatPDF, StrDocumentName,, False
the problem still exists.

I tried to save the report manually by Preview > Export > PDF. The preview is OK, saving as PDF results in the same error 2501. This may lead to the conclusion that this problem is not caused by the VBA code.

I verified whether user write rights are OK, and whether a default printer is available. Both are OK. The report consists of 1 page.
When this very same application is used with Access 2010, everything functions as expected.

View 8 Replies View Related

WHERE Clause On LEFT JOIN : Why Do I Get Join Expression Not Supported Message?

Nov 10, 2006

I've been toiling with the issue of WHERE clauses on the "Right" side of Left Joins. I'm aware that you need to use JOIN ON......AND.... rather than JOIN ON....WHERE.... if the WHERE relates to the Right Hand table.

I've even got an example in my DB where the above works, but now am struggling to use the same theory for other tables. Therefore, I went and created two Mickey Mouse tables to test the logic but am getting an error.

I have
Table 1 with one field called Field 1 - values A, B, C
Table 2 as follows

Field 1.....Field 2.......Field 3
A.............100
C.............200..........XXX

I hoped to have a query that finds all records on Table 1 and records on Table 2 where Field 1 matches on the two tables and Field3 = XXX

My SQL is
SELECT Table1.Field1, Table2.Field1, Table2.Field2, Table2.Field3
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
AND Table2.Field3="XXX";

but I get Join Expression not supported

What am I doing wrong?

Thanks
Andrew

View 7 Replies View Related

Create A JOIN Of Different Tables Called Join A Variable And List

Nov 16, 2013

And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.

Code:
SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID )
ON trains.trainID = bookingLeg.tid) as firstJOIN

Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?

View 6 Replies View Related

Forms :: Include Check Boxes In Interactive Access Form Allowing To Choose Output Field

Jun 27, 2013

Including check boxes (representing the query fields) in an interactive access form, in order to decide which fields should be visible.

I think the solution is building an invent in VBA for each check box, however I'm not an expert in Access VBA and don't know how to write the code.

In the example that I've uploaded, in the Form1, when I run the query, all fields are displayed, i.e. VENDOR, REGION, CUSTOMER and MATERIAL are displayed. How can I manage it in the form with a listbox to display only the REGION or MATERIAL for instance.

View 4 Replies View Related

Syntax For Nest JOIN In MS Access?

Feb 22, 2008

This query works:

SELECT p.AcntNumber, p.Name, d.FromDate
FROM Daysheet AS d
INNER JOIN Patient AS p ON d.AcntNumber=p.AcntNumber

But this does not:

SELECT p.AcntNumber, p.Name, d.FromDate, c.CPT
FROM Daysheet AS d
INNER JOIN Patient AS p ON d.AcntNumber=p.AcntNumber
INNER JOIN Charge AS c ON c.Id=d.ChargeNum

Nor this:

SELECT p.AcntNumber, p.Name, d.FromDate, c.CPT
FROM Daysheet AS d
(INNER JOIN Patient AS p
(INNER JOIN Charge AS c ON c.Id=d.ChargeNum)
ON d.AcntNumber=p.AcntNumber)

How do you word a nested join?

View 2 Replies View Related

Does Access Recognise Anti-join?

May 19, 2006

Hello,

I'm merging de-duping contact data from 2 tables with the same fields. One table is the master (lets call it X) and the other is new data (lets call it Y).

However, it's not a simple as adding the new recordset to the old recordset: there are many dupes - sometimes X data needs to be replaced with Y data, sometimes the X data is more comprehensive than the Y data so the Y data is discarded, but often Y and X records for matching email addresses needs to be merged.

I currently have a union query joining the two tables (X and Y), upon which a make table query is based. A de-duplication query has been based on the resulting table of all records (X + Y), and exact duplicate/fragment 'Y' records in the table have been removed.

I have in my mind a diagram of a right-outer-join query (where the left table is X + Y and the right table is the original table X) - and I want the data that would be excluded from this operation. I have come across un-join queries in discussions relating to Oracle, can I use this operator in Access or is it incompatible with the Jet engine?

Would I just be able to use "IS NOT" somewhere in the right-outer-join query instead?

Please help! Thank you!

View 4 Replies View Related

Access Slow To Sum Or Join On Null Values?

Oct 5, 2006

Is Access slower at summing null records than SQL server?
I have a query which takes less than 1second in SQL server but takes about 5-10 in Access but can't think why there is such a lag in processing speeds.

View 7 Replies View Related

Join Syntax Errors Translating Oracle SQL To Access SQL

Mar 21, 2008

I've developed a working query to grab some information from my Oracle 9i database using SQL Developer. I have a tool in MS Access 2003 that I'm developing for other users so they can input some options through the GUI and query the same data that I do.

I'm trying to translate the query I developed in Oracle and have it work in Access. I have my ODBC connection set up so that Access can get to the same tables.

Here is my Oracle Query:

[QUERY]...

The error I'm receiving is: Run-time error '3296'

Join expression not support.

I believe the error has something to do with the "LEFT JOIN CPCF ... " but I can't seem to figure out how to fix it. The query will run in Access if I change that to an "INNER JOIN CPCF ...." but the result set is not what I need it to be. Any thoughts on how to fix this?

View 1 Replies View Related

Queries :: Full Outer Join In Access 2010?

Jan 21, 2015

I would like to create a full outer join in Access2010 between two tables with many to many relationship.

So I have tblServer and tblApplication and there is also the ServerID-ApplicationID table that connects these tables.

I found online many examples but all of them where for two tables with one-to-many relationship.

View 6 Replies View Related

Queries :: Access 2010 / Join Expression Not Supported

Jul 1, 2015

I am trying to execute the query below with multiple left joins because of the data I am trying to get back. The weird thing is sometimes it work and then sometimes it gives me a join expression error. It seems that access strangely removes brackets around the ON clauses. However even when I put those brackets back in this query it isn't working. why this query isn't running or why the brackets disappear in Access 2010.

SELECT Patients.[First Name] & " " & Patients.[Surname] AS Fullname
, [Clinic Patient].[MYMOP ID]
, NZ(MYMOPs.[MYMOP Date], [Clinic Patient].[First Appointment]) AS [MYMOP1 Date]
, MYMOPs.Completed AS [MYMOP1 Completed]

[code]....

View 14 Replies View Related

Queries :: Access 2003 - Join Via Calculated Field

Apr 28, 2013

I'm stuck in Access 2003 - the group I am working with is unwilling to upgrade due to costs, and I've inherited this database (or I should say, a glorified spreadsheet), so unfortunately I am stuck with the tools that I have to use.

So I have Table A:MemberID (autonumber, Primary Key)

NameLast (Text)
NameFirst (Text)
TLBeginner (Yes/No) - Training level
TLIntermediate (Yes/No) - Training level
TLContinuing (Yes/No) - Training level
TLAdvanced (Yes/No) - Training level

I have created Query1 with the above table and added in a calculated field which essentially takes the training levels and converts it to a decimal number (basing it off a binary number of the 4-bits/Yes-no fields) with the calculated field below:

TrLevelTxtID: IIf([TLFundamental]=True,1,0)*1 + IIf([TLIntermediate]=True,1,0)*2 + IIf([TLContinuing]=True,1,0)*4 + IIf([TLAdvanced]=True,1,0)*8

So the calculated field works great - Depending what training levels are selected or not selected, I get a range between 0 through 15.

Table B: I want a list of "friendly name" for the different training levels.

TLID (Number, No duplicates)
TLFriendlyText (Text)

So what I want to be able to do, is to have Query1, that will pull the training level text (TLFriendlyText) within the query based on the calculated field (TrLevelTxtID). The catch is, I can't seem to create a join between the two tables with the calculated field.

Ideally, I want the final query to pull, [NameFirst], [NameLast] from TableA and [TrLevelTxtID] (from TableB) based on the queries calculated field [TrLevelTxtID].

How best to achieve this with the Access 2003 limitations. Also, this query will only be used for reporting/mail merging so there are no update requirements or concerns.

View 4 Replies View Related

Queries :: Access 2010 - Inline Query Using Inner Join And Group By?

Nov 14, 2013

I am currently using INNER JOIN and GROUP BY to narrow down the RowSource of a combo box based on the value selected in the active combo box where the code is located "onClick".

The value being selected is a StoreID, This ID is matched against the AssetRegister to find all group names, The GroupID and GroupName are then retrieved from the AssetGroup table, then finally the list is grouped on the Group ID and Group Name.

I have tested this query in SQL Server Management Studio and it works without any issues, however when I use it as an inline query to adjust the row source of the combo box it returns no values.

Here is the SQL query:

Code:
SELECT AssetGroup.ID, AssetGroup.GroupName
FROM AssetGroup
INNER JOIN
(SELECT AssetRegister.AssetGroup, AssetRegister.Store
FROM AssetRegister
WHERE AssetRegister.Store=7) AS ar ON (ar.AssetGroup = AssetGroup.ID)
INNER JOIN Store ON Store.ID = ar.Store
GROUP BY AssetGroup.ID, AssetGroup.GroupName ORDER BY AssetGroup.GroupName

And here is the inline query applied to the RowSource:

Code:
AssetGroup.RowSource = "SELECT AssetGroup.ID, AssetGroup.GroupName FROM AssetGroup INNER JOIN " _
& "(SELECT AssetRegister.AssetGroup, AssetRegister.Store FROM AssetRegister WHERE AssetRegister.Store=" & StoreID & ") AS ar ON (ar.AssetGroup = AssetGroup.ID) " _
& "INNER JOIN Store ON Store.ID = ar.Store GROUP BY AssetGroup.ID, AssetGroup.GroupName ORDER BY AssetGroup.GroupName"
AssetGroup.Requery

View 4 Replies View Related

Reports :: Output Access Reports As JPG To Display In Pictures Screen Saver

Sep 2, 2013

I have been tasked with creating a database to log employee suggestions and then automatically present reports around the site. I have developed the database and it works OK and can output *.PDF reports, but I'm having problems with how to display these reports. My idea was to output the reports automatically as *.TIFF or *.JPG files and have them stored in a folder that the My Pictures screensaver uses. The reports (in fact, any site report stored in this folder) can then be shown on any screen dotted around the site when it goes into screensaver mode. However I can't find any way to output/convert to a picture file.

The other option is to create a webpage that rotates through the saved PDFs, but this isn't ideal as the PC users will have to load the webpage to display the reports.

View 4 Replies View Related

Develop A Query In MS Access 2010 To Join Two Tables Using Three Joins One Of Which Is A Date Range

Apr 15, 2015

I am trying to develop a query in MS Access 2010 to join two tables using three joins one of which is a (between) date range. The tables are contained in Access.

ABCPART links to XYZPART. ABCSERIAL links to XYZSERIAL. ABCDATE links to (between) XYZDATE1 and ZYZDATE2.
[ABCTABLE]
ABCORDER
ABCPART

[code]...

View 4 Replies View Related

Inner Join And Left Join

Jan 3, 2007

I am trying to do an inner join with a left join. The only problem is, I want to inner join the table that is being joined. This is how I thought it would work below, but it doesn't work.

SELECT * FROM ((Submissions LEFT JOIN Candidates ON Submissions.`Candidate Code` = Candidates.`Candidate Code`) INNER JOIN `Type Candidate Status` ON Candidate.Status = `Type Candidate Status`.`Status ID`)
WHERE Submissions.Status <> 7 ORDER BY `School Interest` DESC;

I want to get the Candidate.Status to inner join with the `Type Candidate Status`.`Status ID`.

If you can help, thanks in advance.
Dave

View 1 Replies View Related

MS Access "Joins", Possible To Join Via A Range Or Add Code?

Nov 2, 2007

hi im pretty new to this, using access2000 i believe


i have a large amount of data and have been able to join them successfully. using the "left" "Operator" "right"


one of the things id like to join are dates, but the dates i want to join are not exactly equal to each other so i cant just use a operator "=".


i need to be able to tell access to link the dates within say 30 days of each other.



Ideally something like left > right -30


currently im using excel to do this for me but if access can do it then it would be great.


in excel i use a macro which matches dates within 30 days, and just use a simple

If range("A1") < range("A2") + 30 and range("A1") > range("A2") - 30 then ...........




so is it possible to have access join things within a range?


would be grateful for any help ;)

View 2 Replies View Related

"OUTER JOIN" On Access

Jun 19, 2006

Hello Everybody...

Pls, does someone have any idea of how I can do an "OUTER JOIN"-like effect on an Access querie? I tried to use this statement in his standard but it didn't work...

Thanks!

View 1 Replies View Related

JOIN DB2 Table With Access (or Other) Table

Apr 8, 2008

I have most of my data in DB2 tables which I am linking to. However, there are a few pieces of data I am gathering from text files. I put them into a little Access table and tried to JOIN with a DB2 table field. My query died....

Is Access amenable to joining different types of tables? If anyone can advise I would be most grateful!

Arpeggione

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved