New to Access

[fishsixteen16]

I have an Excel file that I'd like to turn into a searchable database. I need help importing the Excel file to an Access database, and from there, I would like to configure the database to solve simple equations. Is there anyone willing to help? Thank you.

[Micron]

We're all volunteers here, so unpaid. If you want assistance in your efforts you will find lots of help here. If you want someone to build you a database the list of volunteers will be very short. Not saying no one would because one never knows.

Not sure what you mean by solving simple equations. That sounds like a job for Excel because a db is mostly about storing data in a way that makes it easy to ask questions and get answers. Sure, it is suitable for simple calculations and aggregate functions, but not for complex ones.

BTW, you don't "import" a workbook into Access - spreadsheets (lots of related data in columns) are not suitable for db tables (data in rows, related data in other tables). Might be best if you explained what the wb does and why you think Access is the right tool for the job. It just may be that they can work nicely together. I'd also urge you to note that the importance of db normalization cannot be over stressed and you need to understand it. Also there are many traps that novices can fall into, so I've often posted a set of links that cover a lot of this stuff. You would be wise to review before embarking on any db project.

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

[fishsixteen16]

I'm new to Accees, but I'm not new to Excel. My question is: is it possible to set up an Excel spreadsheet so that it behaves like a database? [I cannot upload an Excel file, it seems... so I don't know what to do in terms of attaching an example file]. In the full file, there are 134 sheets that represent an FCS school, but I've only included two sheets due to file size limit restrictions. In the full file the sheet will also have sheets labeled "W1", "W2"... and so on until "W15". Each team's stats for each week will be displayed (in contrast to their opponent) in the "W(?)" sheets. I'd like to make a custom database query on the "Query" sheet in which stats are generated based upon variables of known data displayed in all the sheets of the file.


So, let's say one wanted to create a query for: when a team has >= 8 days of rest before playing a game what is the average number of offensive turnovers in games that meet that criterion. The database would have to look through each team's sheet (i.e. "Akr" and "BG") and find instances in which the value in the ED column was >=8 and then average all the qualifying values found in the column AS to solve the query. Please let me know if this is possible, if not, what program must be used to accomplish this? Thank you.

[Welshgasman]

As for size, you could just zip it?
What you are asking for is possible, you just need to get the structure correct.
With 134 sheets plus W1, W2 etc, it really sounds like you need a database.

However Excel thinking and Access thinking are worlds apart, if that.

[Micron]

I have to assume that you didn't review the normalization links. If you did, I don't think you'd be asking if you could do this with so many individual sheets.
Your example might be a case of relating a team to games played where there were 8 days in between game dates and there were x turnovers in the latter game. In a db for that I think you'd need at least 3 tables, and maybe more if 'turnover' is a type of error you want to categorize.

In Access you think this way

OffensiveErrorIDpk
1	turnover
2	fumble
3	holding
4	etc.

In Excel you think this way

Offense Errors	turnover	fumble	holding	etc.

The latter will never work well, if at all, in Access. As for posting file samples, this site gives the appearance that you can paste in pics or files, but you cannot. See How to Attach Files at the top of the page.

[fishsixteen16]

Here is an excerpt of the Excel file, but I had to zip it in order to upload it. Also, the full Excel file is too big when it's zipped to upload.

[fishsixteen16]

Gonna be honest, I'm in way over my head with Access... I watched a "how-to" video on YouTube and I was still lost. ��

[Welshgasman]

I am off to bed in a minute, but does that file have the fcs sheets and respective w? sheets?
Also give us some heads up as to how it is meant to work.
Don't expect us to evaluate all the formulae to see how it is meant to work


Help us to help you.

[fishsixteen16]

The attached file only has two of the 134 FCS sheets (otherwise the file is too big, even zipped). Essentially each FCS school has its own sheet with game-by-game stats. I'm interested in calculating stats based upon variables (like the turnover example I provided).

[madpiet]

Not new to Excel...
so use PowerQuery to clean it up and append all the separate sheets into one properly designed sheet, then import that.

The reason I'd use PowerQuery instead of VBA or queries is that I can do waaaaaay more in Excel. Absolutely no contest.

[fishsixteen16]

#1: The remark "Not new to Excel..." is condescending. If you're not going to be polite, keep it to yourself. #2: Since you've made the decision that anyone who doesn't know how to use Power Query is new to Excel, then I guess I'm new to Excel. Glad to clear that up. #3: It's entirely possible that PQ can achieve the results I'm looking for, but you didn't provide enough detail to assist, and from what I can tell by viewing What is Power Query? (youtube.com) this doesn't meet my needs.

[pbaldy]

Post 11 was moderated, I'm posting to trigger email notifications.

[fishsixteen16]

Why was my post moderated? Why allow post #10 to still remain unmoderated? Post #10 encourages others to be disrespectful and unhelpful.

[pbaldy]

Why was my post moderated? Why allow post #10 to still remain unmoderated? Post #10 encourages others to be disrespectful and unhelpful.

Yours was moderated because you are new and your post contained a link. It's a setting the forum has to reduce spam.

[Welshgasman]

My Excel is not good enough to work out what is what.
So I am out.