Can Access aid in this or Excel?

[wyantnguyen]

Good day, and thank you for this amazing forum!



I am new to Access and Excel, please pardon my ignorance. I am trying to figure out if the following query can be completed on Microsoft Access or Excel.

I have three tables which ideally I would like to produce this final table:

PO#	Description	Cost#	Project#	Line#1	Voucher Doc#	Amount Paid	Invoice#
4555	WIRE #1	1193	32601199	1	253	$250	QWERTY
4555	WIRE #2	1193	32601199	2	253	$350	QWERTY
4555	WIRE #3	1193	32601199	3	254	$4888	ABCD
4555	WIRE #4	1193	32601199	4	254	$789	ABCD
4555	WIRE #5	1193	32601199	5	N/A	N/A	N/A
4585	COMPUTER#1	1500	315089	1	757	$898	CDE
4585	COMPUTER#2	1500	315089	2	758	$88987	ADF

First table: notice that there is no line #5 as there is no invoice for this, and nothing was paid out to vendor.

Po#	Line#	Voucher Doc#	Amount Paid
4555	1	253	250
4555	2	253	350
4555	3	254	4888
4555	4	254	789
4585	1	757	898
4585	2	758	88987

Second Table: contains voucher document # associated with invoice #

Voucher Doc#	Invoice#
253	QWERTY
253	QWERTY
254	ABCD
254	ABCD
757	CDE
758	ADF

Table#3: contains description of the purchase order...description of material, line# of PO and cost#.

PO#	Description	Cost #	Project#	Line#
4555	WIRE #1	1193	32601199	1
4555	WIRE #2	1193	32601199	2
4555	WIRE #3	1193	32601199	3
4555	WIRE #4	1193	32601199	4
4555	WIRE #5	1193	32601199	5
4585	COMPUTER#1	1500	315089	1
4585	COMPUTER#2	1500	315089	2

Is this something that can be done on Microsoft Access? I have played around a bit with importing tables, creating relationships and generating queries...but have not had success in coming up with generating the first table.

Thank you in advance,

Wyant

[June7]

Tables 1 and 3 can be joined and produce the records in final except for Invoice# field.

SELECT Table3.[Po#], Table3.Description, Table3.[Cost#], Table3.[Project#], Table3.[Line#], Table1.[VoucherDoc#], Table1.AmountPaid
FROM Table3 LEFT JOIN Table1 ON (Table3.[PO#] = Table1.[Po#]) AND (Table3.[Line#] = Table1.[Line#]);

Including table 2 will duplicate data because each record in table 2 will join to multiple records in table 1. For instance, there are two 253 in each table so joining on VoucherDoc# fields will result in 4 records with 253. Same for 254.

SELECT Table3.[Po#], Table3.Description, Table3.[Cost#], Table3.[Project#], Table3.[Line#], Table1.[VoucherDoc#], Table1.AmountPaid
FROM Table2 RIGHT JOIN (Table3 LEFT JOIN Table1 ON (Table3.[PO#] = Table1.[Po#]) AND (Table3.[Line#] = Table1.[Line#])) ON Table2.[VoucherDoc#] = Table1.[VoucherDoc#];

Whether or not this is an issue depends on what you want to do with the output. But consider this which does achieve final table output.

SELECT Table3.[Po#], Table3.Description, Table3.[Cost#], Table3.[Project#], Table3.[Line#], Table1.[VoucherDoc#], Table1.AmountPaid, DLookUp("[Invoice#]","Table2","[VoucherDoc#]=" & Nz([VoucherDoc#],0)) AS Inv
FROM Table3 LEFT JOIN Table1 ON (Table3.[PO#] = Table1.[Po#]) AND (Table3.[Line#] = Table1.[Line#]);

Use of domain aggregate functions in query is a last resort approach.

[CarlettoFed]

In the attachment you can see an example.

[wyantnguyen]

Thank you!

[wyantnguyen]

In the attachment you can see an example.

Thank you! This is helpful.