How can I create this table?

[QuietRiot]

Not sure how to do this. SQL or VBA or what. Basically when I import an excel sheet into a table it looks like this:

[F1] (first column)
Fund: 4329
AccountNumber
13224233
Fund: 3343
AccountNumber
23939393
Fund: 2299
AccountNumber
23939321
Fund: 4329
AccountNumber
39328393

I want this:

[F1] [F2]
4329 13224233
3343 23939393
2299 23939321
4329 39328393

 

[jjturner]

Without using code, I would add an Autonumber field to your table [AutoID], then build your query:

SELECT Right(Trim(t1.F1),4) AS Fund, t2.F1 AS AcctNum
FROM myTable t1 INNER JOIN myTable t2 ON t1.AutoID+2=t2.AutoID
WHERE t1.F1 Like "Fund*";

(Assuming your Fund will always be 4 characters long)

HTH,
John

 

[QuietRiot]

Without using code, I would add an Autonumber field to your table [AutoID], then build your query:

SELECT Right(Trim(t1.F1),4) AS Fund, t2.F1 AS AcctNum
FROM myTable t1 INNER JOIN myTable t2 ON t1.AutoID+2=t2.AutoID
WHERE t1.F1 Like "Fund*";

(Assuming your Fund will always be 4 characters long)

HTH,
John

Thanks. I can't believe I'm working on this gorgeous day but I am and I'm from Boston too. I actually messed up my example though. There could be multiple account numbers under each fund and not just 1 account number so the autoID + 2 wouldn't work. I figured out a way using code but basically im looping through the recordset and putting the first found fundID in a string and putting it at each line until it finds the next one. It works but I'm thinking queries might be faster and more efficient?

 

[jjturner]

Yes, it was a summer day for sure - not to be missed.

Regarding SQL vs code, you could say that SQL is a table's natural habitat. When you dissect the recordset with a VBA control structure, it's like taking a fish out of water and putting it on a petri dish, or something to that effect.

Well, your situation has a little wrinkle in it now which makes a solution a bit more complex, but it can be done:

SELECT t1.Fund, t2.AcctNum
FROM (SELECT t3.AutoID, Right(Trim(t3.F1),4) AS Fund, 
          (SELECT TOP 1 t4.AutoID
           FROM myTable t4
           WHERE t4.AutoID > t3.AutoID AND t4.F1 Like "Fund*"
           ORDER BY t4.AutoID) AS AutoID_2
      FROM myTable t3
      WHERE t3.F1 Like "Fund*") t1,
     (SELECT t5.AutoID, t5.F1 AS AcctNum
      FROM myTable t5
      WHERE t5.F1 Not Like "Fund*" AND t5.F1 Not Like "Account*") t2
WHERE t2.AutoID > t1.AutoID AND t2.AutoID < t1.AutoID_2;

Untested, but I think that's a workable structure...

HTH,
John

 

[QuietRiot]

Yes, it was a summer day for sure - not to be missed.

Regarding SQL vs code, you could say that SQL is a table's natural habitat. When you dissect the recordset with a VBA control structure, it's like taking a fish out of water and putting it on a petri dish, or something to that effect.

Well, your situation has a little wrinkle in it now which makes a solution a bit more complex, but it can be done:

SELECT t1.Fund, t2.AcctNum
FROM (SELECT t3.AutoID, Right(Trim(t3.F1),4) AS Fund, 
          (SELECT TOP 1 t4.AutoID
           FROM myTable t4
           WHERE t4.AutoID > t3.AutoID
           ORDER BY t4.AutoID) AS AutoID_2
      FROM myTable t3
      WHERE t3.F1 Like "Fund*") t1,
     (SELECT t5.AutoID, t5.F1 AS AcctNum
      FROM myTable t5
      WHERE t5.F1 Not Like "Fund*" AND t5.F1 Not Like "Account*") t2
WHERE t2.AutoID > t1.AutoID AND t2.AutoID < t1.AutoID_2;

Untested, but I think that's a workable structure...

HTH,
John

Hmm.. I couldn't get it to work. Can we dumb it down a bit for me.

I have 2 queries now (qAccountInfo, qFunds)

qAccountInfo contains ID, AccountNumber, TotalShares
qFunds contains ID, Fund

What exaclty does TOP 1 do? Wouldn't it be better if I used Last(ID) where qFunds.ID <= qAccountInfo.ID. I feel that this gives me the accurate fund but I can't put it all together for some reason.

Thanks,

 

[jjturner]

The trick is you need to set the range of ID's for AcctNum which pertain to a specific fund. In order to do that you need to determine the ID of the next fund (so the AcctNums don't keep getting returned after that point). You find the ID for the next fund with a subquery -

So: SELECT TOP 1 t4.AutoID ... pulls the top instance of the ID which associates with the fund immediately following the fund it's looking at during run-time

I changed my subquery since it was missing some criteria (I forgot to tell it to look for records Like "Fund*"). Hopefully it should work now, and hopefully it makes some sense.

You have to be careful with TOP since it can return more than 1 record if they're the same value - but in this case you're safe since Autonumber will never repeat.

HTH,
John