New user, need help with simple Table set up

[biscuit07238]

I don't know if I what I want to do can be done. It seems simple but maybe it's not just part of the program. I'm VERY VERY new to this so please if you have a solution put it in layman's terms as I am still familiarizing myself with the layout and terminology associated with the program.



To best explain what I'm trying to do I'm going to simplify it using a sports analogy.

Every Baseball team is part of a league and part of a division in that league.

When Setting up a table I would like to have Access auto fill some of my fields based on what is put in another field. For Example. If I type "Braves" in my team field, I would want it to auto fill League with "National" and Division with "East" Ideally if I had multiple tables, I would like then all to do the same, so if at any point I have a team field on any table it would fill in the rest the same way. Is this option available to me? Any help would be most appreciated.

Thanks in advance.

[Welshgasman]

Yes, that is doable, but you generally would not store those values as you can look them up any time. That changes if the values might change, like the discount for a customer on an order.
You really should look at the Northwind new DB. https://support.microsoft.com/en-gb/...5-0714ca8dc1e2

I would probably have a combo where I would select the team and that combo would have additional columns that I would use to populate text controls.
You would need to get your data structure correct first though.

[Micron]

Analogies often lead us down the wrong hole but here goes. You should have a table for league (OK, so only 2 records or not - see what I mean about analogies?), table for division and table for teams. If you're concerned about players, a table for that (only) as well. Table teams will provide the relationship between player and team by way of PlayerIDpk being a field in tblPlayers. OR you need a junction table if you want to maintain history; e.g. every team that Edwin Encarnacion played on as well as his current team. Understanding db normalization is not even the foundation for building a relational database - it is the footing (and that's my analogy!). Here's a post where I put my usual list of helper links:

https://www.accessforums.net/showthr...773#post521773

[CJ_London]

Would also point out that unlike excel, database tables are for storing data only, not presentation. For presentation you use forms and reports.

with your tables set up along the lines suggested by Micron your requirement becomes trivial to achieve in a form or report

[Micron]

if at any point I have a team field on any table it would fill in the rest the same way.

To what's been stated I would add that the quoted idea would just be a non-starter. That idea reinforces my belief that db normalization truly needs to be understood but isn't yet.

[June7]

Have you yet studied anything? A solid week with an introductory book on Access would probably answer many of your questions and provide a grounding in database principles, Access functionality, programming concepts, VBA and macro coding.

More templates https://support.microsoft.com/en-us/...8-1afcfc91a9cb

[biscuit07238]

I just wanted to thank the community for your suggestions here. I found some new things out. I will admit one answer seemed a bit snide but maybe that was not their intention. Either way, I have found a workable solution. A special thanks to Welshgasman who not only gave me a link that helped with other questions but also was a quick and kind response to the original post. Turns out I was overlooking the obvious answer which was just to create the field as a Lookup field linked back to another table to pull the data I need that will also serve when I create my query's. Thank you all and if a Mod reads this they can close this thread.

[Welshgasman]

I will admit one answer seemed a bit snide

Normally that would be directed at me, as I can be quite harsh on people who do not even Google for a solution.
A lot of the times, I do not know the answer, but a quick Google gets me a whole bunch of links. You have to spend the time looking at them, or make more exact criteria.

Nice to see that is not me this time, but TBH, I do not think that was intended, if I am thinking of the correct particular post.

[madpiet]

If you want, post a database (just tables!) with your design. Then someone here can proof it and suggest a better way of doing it. If you add like 3 records per table (enough to show what the table is about and what you're trying to do), we can help you do what you're trying to do. That said, understanding the hows and whys of normalization is critical. Maybe feel like dry theory, but it's absolutely essential to understand if you want to create a usable database. (I've seen some that were little more than Excel tables but inside Access).
So my two cents is to get the table structures right. After that, things will fall into place.

[Micron]

I imagine it was me, and nothing was intended other than to try to help - perhaps too matter-of-factly.
By definition, that means without emotion and in a practical manner.

[biscuit07238]

No, not you at all, nor Welsh. I totally get being straightforward with people who don't google, I often have the same type of approach myself. For the record I do the google thing first but I think I will be more understanding moving forward when I personally try to help others because one thing I learned in this is that often it's not that I didn't google but I didn't use the same terminology that the program uses. I think if I had used the right search terms I would have found a solution rather quickly but because of HOW I was searching, it led me to multiple other functions (which are useful, just not for my issue) and then to making this post. But in a way I guess that's good. I found a helpful community, I got some EXTRA knowledge with some of my future database planning and usage, I've found new templates to help me explore in more design features for another database I am contemplating delving into (if I can get the work ethic to take on that momentous task LOL) and I ended up taking what I learned in this posting to both find my answer and learn how to help others who may have googled but not googled correctly. All in all, I'll call that a win.

Thanks again for everyone's help.

[Welshgasman]

If you want, post a database (just tables!) with your design. Then someone here can proof it and suggest a better way of doing it. If you add like 3 records per table (enough to show what the table is about and what you're trying to do), we can help you do what you're trying to do. That said, understanding the hows and whys of normalization is critical. Maybe feel like dry theory, but it's absolutely essential to understand if you want to create a usable database. (I've seen some that were little more than Excel tables but inside Access).
So my two cents is to get the table structures right. After that, things will fall into place.

No, not you this time either. :-)
@biscuit07238 if you try and use ChatGPT, you have to be absolutely precise in your query, else you will likely get garbage of the first order.

[June7]

Advise not to build lookups in table - build comboboxes on form.

Threads are not 'closed' - any poster in thread can mark it 'solved' with Thread Tools dropdown above first post.

[Micron]

any poster in thread can mark it 'solved' with Thread Tools dropdown above first post.

I long ago noticed I could do that and though it was a glitch! Doesn't seem right to me that the decision should be anyone else's but the OP.

[June7]

See post #2 https://www.accessforums.net/showthread.php?t=1828