UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Help Setting Up A Table, Office 2013    
 
   
Sotolin
post Jul 17 2019, 04:34 PM
Post#1



Posts: 86
Joined: 27-August 03



Hey everyone,

Really need your help. I have a list of codes and accounts that I need help putting a table together... I need to automatically populate each code with all accounts. For example code 2375 will have all accounts, code 2376 will have all accounts and so forth.

This is sample of the data I have

Code Account
2375 6100421
2376 6100431
2377 6100432
2403 6100433
2443 6100720
2463 6101500
6101510
6100200

I need to be able to run a formula or macro to give me the following:


Code Account
2375 6100421
2375 6100431
2375 6100432
2375 6100433
2375 6100720
2375 6101500
2375 6101510
2375 6100200
2376 6100421
2376 6100431
2376 6100432
2376 6100433
2376 6100720
2376 6101500
2376 6101510
2376 6100200


Thanks for all your help
Go to the top of the page
 
RJD
post Jul 17 2019, 05:00 PM
Post#2


UtterAccess VIP
Posts: 9,913
Joined: 25-October 10
From: Gulf South USA


Hi: In Access, a Cartesian product query should do what you want. That is, you can put the Code list in one table and the Account list in another table. Then use a query with no JOIN to produce the result with all code /Account combinations, like this ...

SELECT Code, Account FROM tblCodes, tblAccounts ORDER BY Code, Account;

Try that and then you can convert it to a make-table query to store the combinations or send the result to Excel..

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
RJD
post Jul 17 2019, 05:15 PM
Post#3


UtterAccess VIP
Posts: 9,913
Joined: 25-October 10
From: Gulf South USA


I know you said "Excel", but this is very easy to do in Access and send to Excel. Here is a demo that uses your data, creates all the combinations and transfers the data, with headers, in order by Code then Account, to an Excel file.

If the Code and Account lists are already in Excel, you can link them to Access as tables, perform the Cartesian product and send the results back to an Excel file.

See if this will meet your needs.

HTH
Joe
Attached File(s)
Attached File  SettingUpATable.zip ( 19.75K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Sotolin
post Jul 18 2019, 08:20 AM
Post#4



Posts: 86
Joined: 27-August 03



Thank you.. Yes I found a way to do it access... I was hoping that there was an excel way as I have to show people how to do it and they don't have MS Access.

But for now this works.

Thanks for the help again.
This post has been edited by Sotolin: Jul 18 2019, 08:20 AM
Go to the top of the page
 
RJD
post Jul 18 2019, 09:25 AM
Post#5


UtterAccess VIP
Posts: 9,913
Joined: 25-October 10
From: Gulf South USA


You are welcome.

Yes, I believe there is a way to do what you want in Excel, but I think it will take a longer VBA procedure to do that. Access just makes it much easier.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
RJD
post Jul 18 2019, 11:44 AM
Post#6


UtterAccess VIP
Posts: 9,913
Joined: 25-October 10
From: Gulf South USA


In case you are still looking at this thread ...

I was curious about this, so did a web search on "Cartesian product in MS Excel" and came up with several hits. THIS one looks particularly interesting, with no VBA involved. This was a new Excel procedure to me. And it may satisfy your users who have Excel and not Access.

Take a look at the procedure referenced, and at others using the search I mentioned above, and see if this will meet your requirements.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th August 2019 - 01:22 PM