Joining/looking up data from various tables to create new table

[mattadams84]

Hello,

I am a complete newbie to access, i believe the last time i used it was probably in 1997. I am looking for some advice/help.

I have created a database that pulls in data from an excel file which in turn is connected to a web API. I have sucessfuly created linked tables in access.

I am trying to create a database that would output a list of of football match fixtures with the prematch stats of the match.

In excel i have an API that successfully pulls in data (the statistics) across multiple different sheets (1 sheet per league). Each league statistics sheet has the teams of that league in the rows and then many columns which contain statistics for the team's home matches, away matches, and some other stats that excel calculates.

Each league has a unique numerical ID and each team has a unique numerical ID.

I then have another sheet that pulls in via the API a list of todays fixtures. Each fixture is on its own row and the columns contain data including the unique team ID's and the unique league ID. What l would like to achieve is to dynamically/automatically create a whole new table that has this list of the fixtures but also shows the correlating stats of each team. So I guess by using the ID's of the teams from the "todays fixture" sheet is searches through all of the other tables, finds the relevant data and then generates a new table with all the fixtures and stats. Obviously it would need to work out who is playing at home and who away so it finds the relevant stats. I basically want to merge various pieces of information from different tables in a new unique table which i could then export to an excel file.

I have no idea how to create this, i started playing with queries but got completely lost.

Is this possible in access? Is it difficult?

Kind regards

 

[The_Doc_Man]

First,... since it is your 1st post: Hello and welcome to the forum.

Now, as to your stated goals, there is a factor you need to understand. Access has a learning curve a bit steeper than Excel - but that is because it has so much more power to complex things. Your discussion betrays "flat-file thinking" and will limit you very severely. I have no doubt of your ability to learn SQL and VBA to manipulate data, but you need to learn a little bit about data design principles. Otherwise, you will immediately drag yourself down by having a poor DB design.

You are talking about dynamically creating tables on a frequent basis. Yes, that is certainly possible. However, Access has limits on the number of objects it can handle. If you create several tables daily, you will slam into that limit sooner than you might wish. Access has far higher limits on the number of records you can insert into a table, and queries give you a way to isolate things - by year, by team, by opponent, by player, - all sorts of combinations by extraction from a single table. The way that you stop table creation from getting out of hand is to recognize that by adding a date field in the records of the table, you can still keep daily information separate. In fact, with careful design, the odds are extremely high that one table would hold everything you need for the basic team statistics. Maybe you need another table for player stats. Depends on your goals.

If I may suggest this, even though it would delay implementation of your project by a couple of days, I think you need to study NORMALIZATION. If you search this forum (our Search button is to the upper right, to the right on the line that shows your login name) then just search for that topic because this IS a database forum. If, on the other hand, you choose to search the general web, then you must search for DATABASE NORMALIZATION - because by itself, the word NORMALIZATION appears in more than one discipline - e.g. chemistry, politics, mathematics, medicine, social work, ...

Also, if you search the general web, you will get hits from multiple domains. Start with the .EDU domains - those are usually college articles. Try more than one of them, because you need to study the concepts enough to understand them. Read the hits from .COM later, because they will probably include something that the .COM sites want to sell you, and that might initially confuse you as to what is "general" and what is "specific" information.

Once you understand normalization, you would be able to design tables that more compactly and efficiently store the data you want in a form that is easily usable for subsequent analysis or display.

One last thought: If you previously got lost in understanding queries, search for info there, too. Queries are the work-horses of ANY of the SQL-based database products out there, from Access to ORACLE, SQL Server, SYBASE, INFORMIX, ... a whole BUNCH of SQL-based products - more of them then I can even remember. So get back into the saddle and start reading up on queries. You cannot do without them. They are the heartbeat of database work. Sometimes not noticeable, but never absent.

 

[mattadams84]

Many thanks for the response. I will look in to normalisation. As far as creating tables each day there would only be one table that gets created each day, and then deleted the next.

The other tables just update the values in the columns each day (via the linked table excel. (there are 80 tables (one for each league) there is one table that contains the names of the leagues, and the other table that has the days fixtures.

I am not sure how i can normalise this better as the API provides the data that way.

 

[Mike Krailo]

If the fields for each league are the same or very similar, then only one table is needed for main data and league lookup table.

 

[Pat Hartman]

One reason that Access (or any relational database application builder) opens up data access for you is because with Excel, the data layer and presentation layer are merged so data is stored the way you want to view it "now" rather than in a logical view that can be transformed easily using queries/forms/reports to however you might want to view it "tomorrow". Not just one fixed view. Excel thinking is column orientated whereas Relational table thinking is row oriented. Instead of adding a new workbook, sheet, or bunch of columns, you add rows to a table. That means that none of your existing forms/reports/queries ever have to change. You just use criteria to control what is shown "now".

Proper table design is the foundation of everything else that you will ever want to do. If you don't get the foundation laid down correctly, your lovely skyscraper will come crashing to the ground when you realize, you left out the elevator shaft and stairwell section so you can't get off the ground floor.