SQL Question 1

[longhair]

Morning all,
did a quick search but came up with nothing of what i think is a simple issue (simple because i can solve it in other languages just not SQL).
if you have a table with numerous a/p records i'm tring to pull the recordset back with 1 'V' (voucher) record and one displayed 'P' record (but need to cycle through and hit every 'P' record for my calculated field). Any suggestions?
SQL is as follows:

SELECT [vch-hdr].voucher, [vch-hdr].[inv-date], [vch-hdr].[inv-amt], [vch-hdr.inv-amt]-[aptrxp.amt-paid] AS Balance, aptrxp_2.type, aptrxp_1.type, aptrxp_1.[due-date], [vch-hdr.inv-amt]-[aptrxp.amt-paid] AS [Net Due]
FROM (([vch-hdr] LEFT JOIN aptrxp ON ([vch-hdr].[vend-num] = aptrxp.[vend-num]) AND ([vch-hdr].voucher = aptrxp.voucher)) LEFT JOIN aptrxp AS aptrxp_1 ON ([vch-hdr].voucher = aptrxp_1.voucher) AND ([vch-hdr].[vend-num] = aptrxp_1.[vend-num])) INNER JOIN aptrxp AS aptrxp_2 ON ([vch-hdr].voucher = aptrxp_2.[vend-num]) AND ([vch-hdr].[vend-num] = aptrxp_2.voucher)
WHERE ((([vch-hdr].[inv-date])>=[pQueryStart] And ([vch-hdr].[inv-date])<=[pQueryEnd]) AND ((aptrxp_2.type)="P") AND ((aptrxp_1.type)="V") AND (([vch-hdr].[vend-num])=[pQueryVend]))
ORDER BY [vch-hdr].voucher;

thanks in advance.
regards,
longhair

 

[lameid]

Maybe you want a group by clasue? Use Sum function on your calculated field? Use In for criteria to further limit your data based on a sub-query?

Those are just some ideas... I'm not sure I followed exactly what you want to happen.

 

[longhair]

lameid,

sorry - guess i didn't post enough info.
dealing with 2 tables.
vch-hdr (voucher header) and aptrxp (ap transaction).
query is based on the passed parameters [pQueryVend] (vendor #, [pQueryStart] (start date)& [pQueryEnd] (end date).
there will only be 1 record per vendor # in the vch-hdr table but numerous records in the aptrxp table. of the numerous records in the aptrxp table i need the one (and there will only be one) record that contains a 'V' in the type field. i then need to sum the records in the aptrxp table that have a 'P' in the type field and return this to the single instance of vch-hdr.
there ia usually 1 entry in the aptrxp table for 'P' (payment) but there are at times numerous ones (more than 1 payment - check printed then canceled and then reissued are the 2 main reasons that there are more than 1).
currently, the way the query is structured if there is more than 1 instance of a 'P' record in the aptrxp table the record set is returning more than 1 record.
does that make more sense?
would an example of the data in the tables and the record set returned be more helpful?
regards,
longhair

 

[lameid]

Knowing what to sum makes all the difference...

First make a query that simply does the summing...

Select aptrxp.vend-num, aptrxp.voucher, Sum(aptrxp.amt-paid) as TotPayment
From aptrxp
Where aptrxp.type = "PA"
Group By aptrxp.vend-num, aptrxp.voucher

Next join that into a query that performs the logic you mentioned above and is in your original post. Just remove aptrxp_2. I find it curious that your query inner joins to aptrxp_2 as that will only return records that have had a payment. In JET SQL (Access datafile) there is not a difference between that and an outer join because it will evaluate the join and then criteria which in this case involves criteria for payments. Conversely, if you are using an ADP, SQL server will evaluate the criteria first and then the join. If you want all records you need an outer join (Left Join or Right Join) to this separate query in JET. Also, you will want to inculde NZ function in your use of payments because some values will be null in the case of the outer join.

I hope this hasn't been to much a Reader's Digest version for you to follow.

 

[lespaul]

would an example of the data in the tables and the record set returned be more helpful?

yes, it is always helpful if you post this information in any thread, this one or new ones.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

As I quite don't understand the rules for calculating Balance and [Net Due], here a starting point only:
SELECT H.voucher, H.[inv-date], H.[inv-amt], V.[amt-paid] AS V_amt_paid, V.[due-date], Sum(P.[amt-paid]) AS SumOfP_amt_paid
FROM ([vch-hdr] AS H
INNER JOIN aptrxp AS V ON H.[vend-num] = V.[vend-num] AND H.voucher = V.voucher)
INNER JOIN aptrxp AS P ON H.[vend-num] = P.[vend-num] AND H.voucher = P.voucher
WHERE V.type = 'V' AND P.type = 'P'
AND (H.[inv-date] Between [pQueryStart] And [pQueryEnd])
AND H.[vend-num] = [pQueryVend]
GROUP BY H.voucher, H.[inv-date], H.[inv-amt], V.[amt-paid] AS V_amt_paid, V.[due-date]
ORDER BY H.voucher;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[longhair]

PHV,

thanks for the starting point. i modified it a bit and the query is now returning only 1 record but the 'Balance' is still off, if there are numerous payments. for example:
voucher 123456 for 330.19 with 1 payment of 330.19 returns 0.00 for the balance. this is correct 330.19 minus 300.19 = 0.00
voucher 654321 for 315.04 with 3 payments of 315.04 -315.04 and 315.04 returns a balance of 630.08. this is incorrect 315.04 minus (315.04 minus (-315.04) minus 315.04) <> 630.08. if i remember my math correct (^o^)
any other ideas on the sum() function?
modified code:

SELECT H.voucher, H.[inv-date], H.[inv-amt], V.[due-date], Sum(H.[inv-amt]-P.[amt-paid]) AS Balance
FROM ([vch-hdr] AS H INNER JOIN aptrxp AS V ON (H.[vend-num] = V.[vend-num]) AND (H.voucher = V.voucher)) INNER JOIN aptrxp AS P ON (H.[vend-num] = P.[vend-num]) AND (H.voucher = P.voucher)
WHERE (((V.type)="V") AND ((P.type)="P") AND ((H.[inv-date])>=[pQueryStart] And (H.[inv-date])<=[pQueryEnd]) AND ((H.[vend-num])=[pQueryVend]))
GROUP BY H.voucher, H.[inv-date], H.[inv-amt], V.[due-date]
ORDER BY H.voucher;

thanks in advance.
lameid - have not tried your suggestion yet. would like to try to get the solution in one query if possible. the db is already > 100mb and will only grow, will be on a server passing historical data back to clients.
regards,
longhair

 

[PHV]

What about replacing tjis:
Sum(H.[inv-amt]-P.[amt-paid]) AS Balance
with this ?
H.[inv-amt]-Sum(P.[amt-paid]) AS Balance

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[longhair]

PHV,
absolutely beatuiful.
nice & simple - like i metioned earlier i knew how to do it in other languages just not SQL (obviously not a strong point of mine with such a simple question).
thanks for the help - have a star.
regards,
longhair

 

[lameid]

PHV is right about your balance expression.

I still think you really want an outer join so that you show a balance for things that do not have payments. Asuuming thate Type can not contain nulls, the following should work in Access to accomplish it... I just hadn't thought of it before.

SELECT H.voucher, H.[inv-date], H.[inv-amt], V.[due-date], H.[inv-amt]-nz(Sum(P.[amt-paid]),0) AS Balance
FROM ([vch-hdr] AS H INNER JOIN aptrxp AS V ON (H.[vend-num] = V.[vend-num]) AND (H.voucher = V.voucher)) LEFT JOIN aptrxp AS P ON (H.[vend-num] = P.[vend-num]) AND (H.voucher = P.voucher)
WHERE (((V.type)="V") AND (P.type Is Null OR (P.type)="P") AND ((H.[inv-date])>=[pQueryStart] And (H.[inv-date])<=[pQueryEnd]) AND ((H.[vend-num])=[pQueryVend]))
GROUP BY H.voucher, H.[inv-date], H.[inv-amt], V.[due-date]
ORDER BY H.voucher;

 

[longhair]

lameid,
i understand the concerns regarding balance. if this were 'live' data i would agree with you both 100%. The db housing this data will be a historical one. that is, it will contain data that will be purged from our actual mrp system / db. the only way that the data can be purged (read criteria) is that it 100% reconciled and the reconciliation date was at least 3 years ago and there have been no transactions linked to the purged records in the last 3 years. we will be keeping 3 years of data reconciled or not in our actual db. this db is just being created to house data to 1 meet the 7 year criteria and 2 just in case. when i looked at the data with the director and spoke to the accounting dept we came up with the figure of them accessing the db less than 10 times a year - with about 80% of that due to requests from our accounting firm (which may or may not happen in a given year). the db to house the data was relatively easy, however for ease of the end user i'm trying to design it's functionality to be as close to our actual mrp software as possible (actually much easier on the ap side than on the ar side).
again, thanks to all.
regards,
longhair