Join between SOP10100 and SOP30200

This has probably been answered before, but here goes again. I am
trying to create a Invoice report out of SOP. I really need both the
work and history tables since the client really won't know if the
invoice is posted yet or not. It could be and I don't want to
eliminate those transactions from appearing on the report. I am using
RM00101- customer master, SOP10100, SOP10200,SOP30200 and SOP30300 on
the report. I am joining the header tables to the customer and the
line items to the header tables. Seems simple enough. Are there any
tricks or traps to my logic? I don't see any standard reports or Smart
Lists  in GP that combine both sets of data or I would steal the logic
from them. I am kinda a "newbie" to GP so this may be a basic question
for those experts out there.

Joanne Mahoney
SDN Consulting
Jacksonville, FL

0
7/31/2007 8:31:46 PM
greatplains 29623 articles. 3 followers. Follow

5 Replies
943 Views

Similar Articles

[PageSpeed] 54

Your join really needs to be a Union across these tables.  Below is something 
similar that we use to create a view for payments applied to RM invoices in 
the work and history tables.  One thing to be cognizant of is the amount of 
history you're dealing with and the effect that will have on performance.

SELECT     dbo.RM20101.CUSTNMBR, dbo.RM20101.DOCNUMBR, dbo.RM20101.DUEDATE, 
dbo.RM20101.DOCDATE, dbo.RM20101.RMDTYPAL, 
                      dbo.RM20101.ORTRXAMT, dbo.RM20101.CURTRXAM, 
dbo.RM20101.CSPORNBR, dbo.RM20101.PYMTRMID, dbo.RM20101.CHEKNMBR
FROM         dbo.RM20101
WHERE     (dbo.RM20101.RMDTYPAL = 1)
UNION
SELECT     dbo.RM30101.CUSTNMBR, dbo.RM30101.DOCNUMBR, dbo.RM30101.DUEDATE, 
dbo.RM30101.DOCDATE, dbo.RM30101.RMDTYPAL, 
                      dbo.RM30101.ORTRXAMT, dbo.RM30101.CURTRXAM, 
dbo.RM30101.CSPORNBR, dbo.RM30101.PYMTRMID, dbo.RM30101.CHEKNMBR
FROM         dbo.RM30101
WHERE     (dbo.RM30101.RMDTYPAL = 1)

The SmartLists for Sales Transactions and Sales Line Items cover open and 
historical transactions, but I don't know how you could see the details of 
how they accomplished that.
-- 
Jim@TurboChef


"Joanne" wrote:

> This has probably been answered before, but here goes again. I am
> trying to create a Invoice report out of SOP. I really need both the
> work and history tables since the client really won't know if the
> invoice is posted yet or not. It could be and I don't want to
> eliminate those transactions from appearing on the report. I am using
> RM00101- customer master, SOP10100, SOP10200,SOP30200 and SOP30300 on
> the report. I am joining the header tables to the customer and the
> line items to the header tables. Seems simple enough. Are there any
> tricks or traps to my logic? I don't see any standard reports or Smart
> Lists  in GP that combine both sets of data or I would steal the logic
> from them. I am kinda a "newbie" to GP so this may be a basic question
> for those experts out there.
> 
> Joanne Mahoney
> SDN Consulting
> Jacksonville, FL
> 
> 
0
jim.harris (398)
7/31/2007 9:08:13 PM
Thanks, I'll try something similar. The customer only wants to see the
invoices from the prior day. But I jsut don't think that I can assume
that they would still be unposted.

Joanne Mahoney


On Jul 31, 5:08 pm, Jim@TurboChef <jim.har...@turbochef.com> wrote:
> Your join really needs to be a Union across these tables.  Below is something
> similar that we use to create a view for payments applied to RM invoices in
> the work and history tables.  One thing to be cognizant of is the amount of
> history you're dealing with and the effect that will have on performance.
>
> SELECT     dbo.RM20101.CUSTNMBR, dbo.RM20101.DOCNUMBR, dbo.RM20101.DUEDATE,
> dbo.RM20101.DOCDATE, dbo.RM20101.RMDTYPAL,
>                       dbo.RM20101.ORTRXAMT, dbo.RM20101.CURTRXAM,
> dbo.RM20101.CSPORNBR, dbo.RM20101.PYMTRMID, dbo.RM20101.CHEKNMBR
> FROM         dbo.RM20101
> WHERE     (dbo.RM20101.RMDTYPAL = 1)
> UNION
> SELECT     dbo.RM30101.CUSTNMBR, dbo.RM30101.DOCNUMBR, dbo.RM30101.DUEDATE,
> dbo.RM30101.DOCDATE, dbo.RM30101.RMDTYPAL,
>                       dbo.RM30101.ORTRXAMT, dbo.RM30101.CURTRXAM,
> dbo.RM30101.CSPORNBR, dbo.RM30101.PYMTRMID, dbo.RM30101.CHEKNMBR
> FROM         dbo.RM30101
> WHERE     (dbo.RM30101.RMDTYPAL = 1)
>
> The SmartLists for Sales Transactions and Sales Line Items cover open and
> historical transactions, but I don't know how you could see the details of
> how they accomplished that.
> --
> Jim@TurboChef
>
>
>
> "Joanne" wrote:
> > This has probably been answered before, but here goes again. I am
> > trying to create a Invoice report out of SOP. I really need both the
> > work and history tables since the client really won't know if the
> > invoice is posted yet or not. It could be and I don't want to
> > eliminate those transactions from appearing on the report. I am using
> > RM00101- customer master, SOP10100, SOP10200,SOP30200 and SOP30300 on
> > the report. I am joining the header tables to the customer and the
> > line items to the header tables. Seems simple enough. Are there any
> > tricks or traps to my logic? I don't see any standard reports or Smart
> > Lists  in GP that combine both sets of data or I would steal the logic
> > from them. I am kinda a "newbie" to GP so this may be a basic question
> > for those experts out there.
>
> > Joanne Mahoney
> > SDN Consulting
> > Jacksonville, FL- Hide quoted text -
>
> - Show quoted text -

0
7/31/2007 9:13:57 PM
On Jul 31, 5:08 pm, Jim@TurboChef <jim.har...@turbochef.com> wrote:
> Your join really needs to be a Union across these tables.  Below is something
> similar that we use to create a view for payments applied to RM invoices in
> the work and history tables.  One thing to be cognizant of is the amount of
> history you're dealing with and the effect that will have on performance.
>
> SELECT     dbo.RM20101.CUSTNMBR, dbo.RM20101.DOCNUMBR, dbo.RM20101.DUEDATE,
> dbo.RM20101.DOCDATE, dbo.RM20101.RMDTYPAL,
>                       dbo.RM20101.ORTRXAMT, dbo.RM20101.CURTRXAM,
> dbo.RM20101.CSPORNBR, dbo.RM20101.PYMTRMID, dbo.RM20101.CHEKNMBR
> FROM         dbo.RM20101
> WHERE     (dbo.RM20101.RMDTYPAL = 1)
> UNION
> SELECT     dbo.RM30101.CUSTNMBR, dbo.RM30101.DOCNUMBR, dbo.RM30101.DUEDATE,
> dbo.RM30101.DOCDATE, dbo.RM30101.RMDTYPAL,
>                       dbo.RM30101.ORTRXAMT, dbo.RM30101.CURTRXAM,
> dbo.RM30101.CSPORNBR, dbo.RM30101.PYMTRMID, dbo.RM30101.CHEKNMBR
> FROM         dbo.RM30101
> WHERE     (dbo.RM30101.RMDTYPAL = 1)
>
> The SmartLists for Sales Transactions and Sales Line Items cover open and
> historical transactions, but I don't know how you could see the details of
> how they accomplished that.
> --
> Jim@TurboChef
>
>
>
> "Joanne" wrote:
> > This has probably been answered before, but here goes again. I am
> > trying to create a Invoice report out of SOP. I really need both the
> > work and history tables since the client really won't know if the
> > invoice is posted yet or not. It could be and I don't want to
> > eliminate those transactions from appearing on the report. I am using
> > RM00101- customer master, SOP10100, SOP10200,SOP30200 and SOP30300 on
> > the report. I am joining the header tables to the customer and the
> > line items to the header tables. Seems simple enough. Are there any
> > tricks or traps to my logic? I don't see any standard reports or Smart
> > Lists  in GP that combine both sets of data or I would steal the logic
> > from them. I am kinda a "newbie" to GP so this may be a basic question
> > for those experts out there.
>
> > Joanne Mahoney
> > SDN Consulting
> > Jacksonville, FL- Hide quoted text -
>
> - Show quoted text -

Do I need to union both sets of tables, the header ( sop10100 and
sop30200) and the detail ( sop10200 and sop30300)?

Joanne

0
8/1/2007 2:08:12 PM
Hi Joanne,

Think of a union as combining the result sets from two or more select 
statements.  The UNION is on the select statement no so much on the table.  
What information did you need from the line items?

"Joanne" wrote:

> On Jul 31, 5:08 pm, Jim@TurboChef <jim.har...@turbochef.com> wrote:
> > Your join really needs to be a Union across these tables.  Below is something
> > similar that we use to create a view for payments applied to RM invoices in
> > the work and history tables.  One thing to be cognizant of is the amount of
> > history you're dealing with and the effect that will have on performance.
> >
> > SELECT     dbo.RM20101.CUSTNMBR, dbo.RM20101.DOCNUMBR, dbo.RM20101.DUEDATE,
> > dbo.RM20101.DOCDATE, dbo.RM20101.RMDTYPAL,
> >                       dbo.RM20101.ORTRXAMT, dbo.RM20101.CURTRXAM,
> > dbo.RM20101.CSPORNBR, dbo.RM20101.PYMTRMID, dbo.RM20101.CHEKNMBR
> > FROM         dbo.RM20101
> > WHERE     (dbo.RM20101.RMDTYPAL = 1)
> > UNION
> > SELECT     dbo.RM30101.CUSTNMBR, dbo.RM30101.DOCNUMBR, dbo.RM30101.DUEDATE,
> > dbo.RM30101.DOCDATE, dbo.RM30101.RMDTYPAL,
> >                       dbo.RM30101.ORTRXAMT, dbo.RM30101.CURTRXAM,
> > dbo.RM30101.CSPORNBR, dbo.RM30101.PYMTRMID, dbo.RM30101.CHEKNMBR
> > FROM         dbo.RM30101
> > WHERE     (dbo.RM30101.RMDTYPAL = 1)
> >
> > The SmartLists for Sales Transactions and Sales Line Items cover open and
> > historical transactions, but I don't know how you could see the details of
> > how they accomplished that.
> > --
> > Jim@TurboChef
> >
> >
> >
> > "Joanne" wrote:
> > > This has probably been answered before, but here goes again. I am
> > > trying to create a Invoice report out of SOP. I really need both the
> > > work and history tables since the client really won't know if the
> > > invoice is posted yet or not. It could be and I don't want to
> > > eliminate those transactions from appearing on the report. I am using
> > > RM00101- customer master, SOP10100, SOP10200,SOP30200 and SOP30300 on
> > > the report. I am joining the header tables to the customer and the
> > > line items to the header tables. Seems simple enough. Are there any
> > > tricks or traps to my logic? I don't see any standard reports or Smart
> > > Lists  in GP that combine both sets of data or I would steal the logic
> > > from them. I am kinda a "newbie" to GP so this may be a basic question
> > > for those experts out there.
> >
> > > Joanne Mahoney
> > > SDN Consulting
> > > Jacksonville, FL- Hide quoted text -
> >
> > - Show quoted text -
> 
> Do I need to union both sets of tables, the header ( sop10100 and
> sop30200) and the detail ( sop10200 and sop30300)?
> 
> Joanne
> 
> 
0
LeslieVail (702)
8/5/2007 7:46:00 PM
On Jul 31, 5:08 pm, Jim@TurboChef <jim.har...@turbochef.com> wrote:
> Your join really needs to be a Union across these tables.  Below is something
> similar that we use to create a view for payments applied to RM invoices in
> the work and history tables.  One thing to be cognizant of is the amount of
> history you're dealing with and the effect that will have on performance.
>
> SELECT     dbo.RM20101.CUSTNMBR, dbo.RM20101.DOCNUMBR, dbo.RM20101.DUEDATE,
> dbo.RM20101.DOCDATE, dbo.RM20101.RMDTYPAL,
>                       dbo.RM20101.ORTRXAMT, dbo.RM20101.CURTRXAM,
> dbo.RM20101.CSPORNBR, dbo.RM20101.PYMTRMID, dbo.RM20101.CHEKNMBR
> FROM         dbo.RM20101
> WHERE     (dbo.RM20101.RMDTYPAL = 1)
> UNION
> SELECT     dbo.RM30101.CUSTNMBR, dbo.RM30101.DOCNUMBR, dbo.RM30101.DUEDATE,
> dbo.RM30101.DOCDATE, dbo.RM30101.RMDTYPAL,
>                       dbo.RM30101.ORTRXAMT, dbo.RM30101.CURTRXAM,
> dbo.RM30101.CSPORNBR, dbo.RM30101.PYMTRMID, dbo.RM30101.CHEKNMBR
> FROM         dbo.RM30101
> WHERE     (dbo.RM30101.RMDTYPAL = 1)
>
> The SmartLists for Sales Transactions and Sales Line Items cover open and
> historical transactions, but I don't know how you could see the details of
> how they accomplished that.
> --
> Jim@TurboChef
>
>
>
> "Joanne" wrote:
> > This has probably been answered before, but here goes again. I am
> > trying to create a Invoice report out of SOP. I really need both the
> > work and history tables since the client really won't know if the
> > invoice is posted yet or not. It could be and I don't want to
> > eliminate those transactions from appearing on the report. I am using
> > RM00101- customer master, SOP10100, SOP10200,SOP30200 and SOP30300 on
> > the report. I am joining the header tables to the customer and the
> > line items to the header tables. Seems simple enough. Are there any
> > tricks or traps to my logic? I don't see any standard reports or Smart
> > Lists  in GP that combine both sets of data or I would steal the logic
> > from them. I am kinda a "newbie" to GP so this may be a basic question
> > for those experts out there.
>
> > Joanne Mahoney
> > SDN Consulting
> > Jacksonville, FL- Hide quoted text -
>
> - Show quoted text -

Hi Jim,

Thanks a lot for the information. I was able to use it. I created the
view in sql 2005 but when I try to run it in sql 2000 I am getting a
syntax error. Very odd. The same exact syntax. Here is the most
current version of the view ( that works in 2005 but not in 2000). Do
you have any idea why???

CREATE VIEW [dbo].[sopinvoice]
AS
SELECT     oh.CUSTNMBR, oh.CUSTNAME, oh.SOPNUMBE, oh.DOCDATE,
oh.SOPTYPE, oh.SUBTOTAL, oh.SLPRSNID, od.ITEMNMBR, od.XTNDPRCE,
                      oh.FRTAMNT, od.EXTDCOST, od.QUANTITY
FROM         dbo.SOP10100 AS oh LEFT OUTER JOIN
                      dbo.SOP10200 AS od ON oh.SOPNUMBE = od.SOPNUMBE
AND oh.SOPTYPE = od.SOPTYPE
WHERE     (oh.SOPTYPE = '3') AND year(oh.docdate) = year(getdate())
UNION ALL
SELECT     hh.CUSTNMBR, hh.CUSTNAME, hh.SOPNUMBE, hh.DOCDATE,
hh.SOPTYPE, hh.SUBTOTAL, hh.SLPRSNID, hd.ITEMNMBR, hd.XTNDPRCE,
                      hh.FRTAMNT, hd.EXTDCOST, hd.QUANTITY
FROM         dbo.SOP30200 AS hh LEFT OUTER JOIN
                      dbo.SOP30300 AS hd ON hh.SOPNUMBE = hd.SOPNUMBE
AND hh.SOPTYPE = hd.SOPTYPE
WHERE     (hh.SOPTYPE = '3') AND year(hh.docdate) = year(getdate())




0
8/20/2007 6:40:55 PM
Reply:

Similar Artilces:

Doing joIn with REGEXP? (MySQL)
Hi Im working with MySQL. I have similar data in 2 different schemas Im trying to relate. This particular example relates to US states being stored differently in both systems. So, say I have the 2 tables as follows: STATE_1 ----------------- | ID | STATE | ----------------- | 1 | AL | | 2 | AK | | 3 | AS | ................. STATE_2 ------------ | STATE | ------------ | US-AL | | US-AS | | US-AK | ............ I want to get the STATE_1.ID value where the STATE fields are equal. Note that STATE_2.STATE values are the same as those in ...

better to filter on join or where clause
If you have something in your where clause that could be put in the inner join would it be better there? SELECT * FROM tableA a JOIN tableB b on b.key = a.key Where b.status <> 15 and b.status <> 20 or SELECT * FROM tableA a JOIN tableB b on b.key = a.key and b.status not in (15,20) Any difference? Thanks, Tom There is no difference when using inner join. The optimizer will generate the same execution plan. It is more a preference/style of writing queries. I prefer to place filters in WHERE and leave only join predicates in the ON clause: SELECT ...

joining domain
hi! Is it possible to have the users rejoin xp,win7 to win2003 AD with the computer object already exist in the AD? i know that this can be done through administrator group, but can this be done by the user without adding them into the domain admain grp or accoutn operator? what's the best practice and previlege that i shd give to the support team who only need to able to join the pc into the domain? Thanks. They have to be able to delete and add. If you are talking about doing this to one machine not a real big deal but if you want folks to do this all the time it c...

Joining worksheets / outer join
I've found some spreadsheet joining discussion threads, but I haven't found one which I think is applicable... Basically, I have 5 worksheets in an Excel file to be used as a mail merge. Each worksheet has lists of companies and the industries that they handle. Some companies have offices at different addresses, which should remain as seperate rows. Some companies handle more than one industry and are thus in more than one worksheet. Some of the companies have one business contact for all industries, some have seperate business contacts. I want one worksheet, which merges data from...

Query Join
Hello, I need your assistance as I am a beginner with MS Access 2007. Please review the SQL and hope it makes sense. I am trying to capture all records from 2010 AOP even if there is none from SAP GL side. It seems to work with SAP GL, but not all of the AOP side. What am I missing? Not sure how to fix. SELECT [DC Conversion].DC, [2010 AOPII with Benefits].DC, [2010 AOPII with Benefits].[SAP GL], [Category conversion].Category, [SAP P4 GL].[COST ELEMENT], [Category conversion].[Cost Element Name], Sum([SAP P4 GL].P4) AS SumOfP4, [2010 AOPII with Benefits].P4 FROM [2...

Multiple left joins
I'm trying to develop a conditional join that will allow me lookup the data in one field and if it doesn't match, it will look to another field to match. I currently have the following setup: I'm using a left join to include all records from "Table 1 Column A" and only those records in "Table 2 Column A" where the joined fields are equal. Here's what I'm looking to achieve: If "Table 1 Column A" does not match "Table 2 Column A", look to join "Table 1 Column A" where the fields match in "Table 2 Column B&...

join tables in a view
I am not "a sql" gal and need some help creating a join FROM dbo.[z-SL] INNER JOIN dbo.[POFooter] ON dbo.[z-SL].ListName = dbo. [POFooter].[invnum] AND dbo.[z-SL].ProdCode = dbo.[POFooter].[ProdCode] SL.ListName is text nvarchar(50) POFooter.invnum is numberic(18.6) I know I need to alter one of them to join, just not sure how? The best is to alter one of the columns and make it compatible data type. Also, it depends on the value in the NUMERIC column. Here is something you can try: FROM dbo.[z-SL] AS S INNER ...

SQL JOIN for Unmatched records & JOIN fields from another table
Hi Learned people, I have the following 2 SQL codes SELECT Couples_2009.CoupleID, Couples_2009.CoupleNumber, Couples_2009.MaleID, Couples_2009.FemaleID, Couples_2009.PermNumberCollect FROM Couples_2009 LEFT JOIN Couples ON Couples_2009.[MaleID] = Couples.[MaleID] WHERE (((Couples.MaleID) Is Null)); For a result of unmatched records & SELECT Couples.CoupleID, Couples.CoupleNumber, [Male].[Surname]+' '+[Male].[First_Name] AS Male, [Female].[Surname]+' '+[Female].[First_Name] AS Female FROM Competitors AS Male INNER JOIN (Couples INNER JOIN Competitors...

Join Properties
Thank you This message appear to me: The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. (Error 3258) I find the answer : From JOIN PROPERTIES I select No 2: and the query run � but I want to understand :>>>>>>>>>>>>> 1: only include rows where the joined fields from both tables are equal. 2: Include all records from "tblplayer" and only those records fro...

Join SQL 2005 Study Group
Collection 2778: Writing Queries Using Microsoft=AE SQL Server=99 2005 Transact-SQL Introduces you to Transact-SQL starting with basic SELECT queries, then advancing to grouping, joins, subqueries, data modification, transactions, and heterogeneous data sources. IT Professionals who must access and manipulate data in SQL Server 2005 databases and other data sources. After completing this collection, you will be able to: Get started with databases and Transact-SQL. Perform basic queries. Group and summarize data. Join data from multiple tables. Work with subqueries. Modify data. Query meta...

Full Join in MS ACCESS 2007?
I need to combine two tables and get all the PID_NUMBER. Say table 1 has 25 rows of PID's and Table2 has 50 PID's, and I want to combine both tables and my result should show 75 rows. Is this possible? I tried the following query but it's not working.. SELECT PID_NUMBER FROM [table1] FULL JOIN [Table2] ON [Table1].PID_NUMBER = [Table2]. PID_NUMBER; Thank you -- Message posted via http://www.accessmonster.com mls via AccessMonster.com wrote: >I need to combine two tables and get all the PID_NUMBER. Say table 1 has 25 >rows of PID's and Table2 ha...

SFO without joined to MSCRM domain (continue) for Mr.John
Dear Mr.John, Please tell me, Why SFO can not use RPC over Http? because I must to explain with my customer. Do you have any idea for using SFO with MSCRM behind firewall and client are using SFO had multi domain in their company? rpc over http is used to coomunicate with exchange server. -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "phong" <phong@discussions.microsoft.com> wrote in message news:8FB1B72F-6021-4D50-B71D-F92433CD44B8@microsoft.com... > Dear Mr.John, > Please tell me, Why SFO can not use RPC over Http? because I must to &...

Possible to do a partial join
I have to join (compare) 2 tables -- tblA and tblB. They both have one column called Company. In tblA a company may be listed as abc or abc inc. or The abc or The abc inc. In tblB the company will be listed as abc tblA contains 600 rows and tblB contains 500 rows of which the majority of the the rows will match exactly. Some of the rows will not match at all - no portion of tblB.Company will match anything in tblA.Company. I need to isolate the rows in tblB that partially match tblA -- that is -- where tblB.Company = 'abc' and tblA.Company = 'The...

Re: Query Join
John, I hope you don't mind me asking this. When you say "building the query a step at a time", what do you really mean? When I copied your SQL to mine, I still have a problem. I very much would like to learn this and am a good student. I know excel pretty well, but when it comes to access, not so good. What approach should I take? Leave the tables in the query and delete the joins. Then manually create a join (right) as you stated below? Just looking for some direction. Your help is very much appreciated. "John Spencer" wrote: > Try b...

Self-Join Relationship Problem
I have two tables ---------------------------- tblLocation: ---------------------------- LocationID(text) LocationDesc(text) ---------------------------- --------------------------- tblInterconnect: ---------------------------- FromLocationId(text) ToLocationID(text) ---------------------------- Primary key for tblLocation is LocationID. As I want each location ID to appear only once in the From list with multiple occurances in the To field being acceptable, I set ONLY FromLocationId as the primary key for tblInterconnect. Relationship is: tblLocation.LocationId ---one-to-one----> ...

join
Hello, I have a join which is working great. Unfortunately I would only like the first record for Loans and Grants. Right now it brings up the first client with the first loan and grant then the first client with the second loan and grant etc... I would only like the client to appear once, thus the first loan and grant only. Is this possible? Here's the code: SELECT [Tbl Client Information].*, [Tbl Loans].ID, [Tbl Grant].ID AS [ID_Tbl Grant], [Tbl Loans].DateofLoanAgreementSigned, [Tbl Grant].DateofGrantAgreementSigned FROM ([Tbl Client Information] LEFT JOIN [Tbl Loan...

Joining IIF statements together
Hi I have the following two IIF statements which give the right results when used separately but I need to join them together so Access is assessing all conditions. I've tried multiple ways though come back with varied errors - from syntax, commas, too many arguments etc ... Is there actually a way I can join these? =IIf([JobSubStatus]="Awaiting Appointment Advice" And DCount([JobID],"QryInProgress","[JobID] = " & [JobID])=1,Now()-[datereceived],[JobStatusDate]-[datereceived]) =IIf(IsNull([JobSubStatus]),Now()-[datereceived],IIf([j...

Can't Fail We ADVERTISE for YOU Just Join! #2
The hottest biz online did a crazy move. They decided to do a FREE Ad CO-OP. That means once you join they will send your website targeted traffic. They are SPENDING THOUSANDS ON THIS CO-OP. All you need to do is plug in. Take a Free Tour Today at http://www.watchthemoneyonline.com ...

Just joined
Hello everyone, I just joined today, and I am looking forward to reading what you all have to say each day. Stacy Welcome aboard, Stacy. This forum is used by GP users and consultants to post questions and, hopefully, to obtain answers from their peers. It is a great resource. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Stacy Tompkin" wrote: > Hello everyone, I just joined today, and I am looking forward to > reading w...

Can a Windows Server 2008 join W2K2 AD?
Hi, Just bought a Windows 2008 Server. We want to have the Windows 2008 server be a member server in a Windows 2000 AD environment. Not a DC. No migration. It is just going to serve web pages. Do we need to do anything other than have it join the Windows 2000 domain? Are there any issues with this set up? Thanks, SDM Hello sdmusicmaker, Yes, you can add it. Keep in mind that Windows server 2008 has the firewall enabled by default. Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights. ** Pl...

Why Inner join is better than equi join
Dear friends, Inner join and self join both are same. Can you tell me why we prefer inner join? Thanks & Regards Vinod kumar Deep wrote: > Dear friends, > Inner join and self join both are same. Can you tell me why we > prefer inner join? > > Thanks & Regards > Vinod kumar Could you rephrase your question, please? Your subject line mentions inner and equi joins, which indeed are the same, while your question here refers to inner join and self-join, which do not have to be the same. Give an example of each type of join you are asking about s...

UNION Query with Join
I am creating a UNION query based on 3 other parsing queries,qry1,qry2,qry3. qry1 is linked to another table (it contains codes, the linked table is descriptions for the codes) the union works well untill i include the "description" field from that query - it bombs out prompting me as if i have spelt the field name incorrectly Post the SQL statement of the UNION query with and without the description field. The reason Jet/ACCESS asks you for that description value is because Jet/ACCESS cannot identify it as a field within the query's source tables, so it's either a...

New randomizer,join early !!!!!!
This is new randomizer(started 6 september 2005) join early and collect more payments.... Rotation system and displayed automatically to the 10,000 or more people who will visit this page every week! You'll receive payments without even lifting a finger! HONEST AND RELIABLE! We take pride in helping you to make money, and we run this service honestly and fairly. EXTRA ROTATIONS AVAILABLE! Upon reaching the setup screen, you will have the opportunity to increase your rotation frequency. This means you can have your ID displayed more often by adding extra weight. MAKE MONEY WHILE YOU SLE...

Number to Number "Data Type Mismatch in Expression" in Join
I am attempting to write a query joining two linked tables in a query by payroll number (all numeric). When write the query and join by payroll number I get the "Data Type Mismatch in Expression" error. Both tables have the Data Type as "Number", but the only difference I can tell is one has "Field Size" as "Decimal" and the other has "Field Size" as "Double". I have even tried to join on the VAL() of each payroll number field with no luck. Any ideas? "Decimal" and "Double" are actually different dat...

Slow Join
I'm trying to rewrite queries for better performance and am stuck on one. This is how it is written now: SELECT tbl_OrderParts.UniqueID, tbl_OrderParts.Part_Number, tbl_OrderParts.Sales_Order, tbl_parts.Product_Description, tbl_parts.ECCN, tbl_parts.Manufacturer_ID, tbl_manufacturer.Manufacturer FROM tbl_manufacturer INNER JOIN (tbl_parts INNER JOIN tbl_OrderParts ON tbl_parts.Part_Number = tbl_OrderParts.Part_Number) ON tbl_manufacturer.Manufacturer_ID = tbl_parts.Manufacturer_ID; I rewrote it as follows but now it's not updatable (does not allow entry in my form): ...