query to recreate a flat file export

[mw4 via AccessMonster.com]

I have a need (unfortunately) to take a relational database and query out
flat file output.

Let's say I have two tables:

tblAssets
AssetID--number
AssetDescription--text
etc...

tblPayments
PayID--number
AssetID--number
PayDate--Date
PayAmount--Currency
PayAccount--Number

Each asset can have multiple payments, traditional one to many.

I have a need to query the two tables to get an Excel output as such:

It would look like this if every asset only had one payment:
AssetID--AssetDescription--PayID--PayDate--PayAmount--PayAccount

If any ONE asset had two payments, it would look like this:
AssetID--AssetDescription--PayID--PayDate--PayAmount--PayAccount--PayID2--
PayDate2--PayAmount2--PayAccount2

If any ONE asset had three payments, it would look like this:
AssetID--AssetDescription--PayID--PayDate--PayAmount--PayAccount--PayID2--
PayDate2--PayAmount2--PayAccount2--PayID3--PayDate3--PayAmount3--PayAccount3

You get the idea.

I need a starting point for this query and then I can get it into Excel no
problem.
It's stumping me.

 

[MGFoster]

I have a need (unfortunately) to take a relational database and query out
flat file output.

Let's say I have two tables:

tblAssets
AssetID--number
AssetDescription--text
etc...

tblPayments
PayID--number
AssetID--number
PayDate--Date
PayAmount--Currency
PayAccount--Number

Each asset can have multiple payments, traditional one to many.

I have a need to query the two tables to get an Excel output as such:

It would look like this if every asset only had one payment:
AssetID--AssetDescription--PayID--PayDate--PayAmount--PayAccount

If any ONE asset had two payments, it would look like this:
AssetID--AssetDescription--PayID--PayDate--PayAmount--PayAccount--PayID2--
PayDate2--PayAmount2--PayAccount2

If any ONE asset had three payments, it would look like this:
AssetID--AssetDescription--PayID--PayDate--PayAmount--PayAccount--PayID2--
PayDate2--PayAmount2--PayAccount2--PayID3--PayDate3--PayAmount3--PayAccount3

You get the idea.

I need a starting point for this query and then I can get it into Excel no
problem.
It's stumping me.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Common question. See this site for a solution:

http://www.mvps.org/access/modules/mdl0004.htm

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSffLdYechKqOuFEgEQI4rACfZkKsgPgIAcJTpt9O4hEzWImh8/4AnRiN
cZPCDwCqYIRInPtv6cfeYe5P
=f8j4
-----END PGP SIGNATURE-----

 

[mw4 via AccessMonster.com]

I've been messing with this all day and can not get it to work.
Any help using the fields and tables that I have below?

 

[mw4 via AccessMonster.com]

I've been messing with this all day and can not get it to work.
Any help using the fields and tables that I have below?

I mean above, not below...

 

[KARL DEWEY]

The two queries below will accomidate 4 payments. You can build on to the
last one if you will have more. The first query creates a number for each
payment.

tblPayments_Num
SELECT Q.[AssetID], Q.[PayID], Q.[PayDate], Q.[PayAmount], Q.[PayAccount],
(SELECT COUNT(*) FROM [tblPayments] Q1
WHERE Q1.[AssetID] = Q.[AssetID]
AND Q1.[PayID] & Q1.[PayDate] & Q1.[PayAmount] & Q1.[PayAccount] <
Q.[PayID] & Q.[PayDate] & Q.[PayAmount] & Q.[PayAccount] )+1 AS Num
FROM tblPayments AS Q
ORDER BY Q.[AssetID], Q.[PayID] & Q.[PayDate] & Q.[PayAmount] &
Q.[PayAccount];


SELECT tblAssets.AssetID, tblAssets.AssetDescription, tblPayments_Num.PayID,
tblPayments_Num.PayDate, tblPayments_Num.PayAmount,
tblPayments_Num.PayAccount, tblPayments_Num_1.PayID AS PayID2,
tblPayments_Num_1.PayDate AS PayDate2, tblPayments_Num_1.PayAmount AS
PayAmount2, tblPayments_Num_1.PayAccount AS PayAccount2,
tblPayments_Num_2.PayID AS PayID3, tblPayments_Num_2.PayDate AS PayDate3,
tblPayments_Num_2.PayAmount AS PayAmount3, tblPayments_Num_2.PayAccount AS
PayAccount3, tblPayments_Num_3.PayID AS PayID4, tblPayments_Num_3.PayDate AS
PayDate4, tblPayments_Num_3.PayAmount AS PayAmount4,
tblPayments_Num_3.PayAccount AS PayAccount4, tblPayments_Num_3.Num
FROM (((tblAssets LEFT JOIN tblPayments_Num ON tblAssets.AssetID =
tblPayments_Num.AssetID) LEFT JOIN tblPayments_Num AS tblPayments_Num_1 ON
tblAssets.AssetID = tblPayments_Num_1.AssetID) LEFT JOIN tblPayments_Num AS
tblPayments_Num_2 ON tblAssets.AssetID = tblPayments_Num_2.AssetID) LEFT JOIN
tblPayments_Num AS tblPayments_Num_3 ON tblAssets.AssetID =
tblPayments_Num_3.AssetID
WHERE (((tblPayments_Num.Num)=1) AND ((tblPayments_Num_1.Num)=2) AND
((tblPayments_Num_2.Num)=3) AND ((tblPayments_Num_3.Num)=4));