Solved Using excel over tables (2 Viewers)

[GPGeorge]

RDBMS can also handle unstructured data. You just define the column as text, either long or short and put in whatever you want to. So, Excel has no advantage there. Excel does have manipulation advantages when you are trying to duplicate data or clean it up. But once you commit to an RDBMS, you are essentially committing to mostly structured data and the need for clean up in aisle one no longer exists unless you have a constant stream of bad input data and have no way to control the source of the data to fix the problem before it gets to you. You add validation code to your forms and RI to the schema and you keep out bad data going forward. Bulk updates when necessary are done with queries, not by copying and pasting.

True, but to even begin to handle it, "...you just define the column as text, either long or short..." I would argue that the data is immediately structured, at least at a minimum level, by virtue of being part of a table.

 

[Pat Hartman]

I'm not arguing with that per se but even Excel has a "data type" although it is fluid. That is my objection to it. Why bother with a data type if you can put any ol' crap in the cell?

 

[MarlonVisser0408]

@MarlonVisser0408 So far, it seems that you are here to complain about how inferior Access is to Excel, to the point where you actually think it is better to use Excel spreadsheets to hold your data than actual tables because spreadsheets support unique functions and data types for every single cell.

Nope, not even close.

Do you honestly think that having 5 million potentially different functions rather than one is some kind of advantage? Yes, when you are manually manipulating data, some things are easier with Excel than with a relational database (notice I don't say "Access") but that is because Excel doesn't care what is in the cell whereas the RDBMS does care and does enforce rules because in an RDBMS, consistency counts for something. I admit to sometimes resorting to Excel to help me to clean up bad data before I convert an old app to Access. But once the data is in Access, then my forms and tables enforce RI and contain validation rules to prevent bad data from ever being saved again.

I was simply asking if there was a benefit to be had there, simply because I do not have the knowledge of how things in the end turn out. I rather save myself hundreds of hours of learning and testing stuff than not to ask some questions. The only reason that I'm even naming excel is because of my minimalistic background, any overlap I have from excel is about all my experience in access - I hope that this time around, you grasp that I don't have the experience to go too far beyond the small references that I can make; it has nothing to do with either Access or Excel actually; it has to do with my experience.

I'm pretty sure that no expert here agrees with either using Excel as your data store or your preference for 5 million functions rather than one. You are on such a bad track that you needed a wakeup call, so I rocked the boat. Sorry, you're right. I was appalled by this approach being proposed by someone who I would have thought would know better given the level of experience you claim. If you actually want to learn how to use Access, we are here to help. Nothing a novice developer does appalls me. They don't know any better.

I'm here for access; don't worry. If I thought that Excel was the solution here, I wouldn't have came down here to waste my time. The very reason that I'm having a hard time getting to start in Access is actually the same reason that Excel doesn't fit for the data that I'm trying to connect and store. It's data from more then a hundred tables and it's driving me insane.

Since you are unfamiliar with Access, it is probably better to state your problem and ask for a solution to the problem rather than asking how to implement what you think should be the solution based on your strong preference for how Excel works.

I think my biggest issue is with the time frame that my state of mind is in and the pressure of me wanting to do something with the product. It's a personal thing that unfortunately, I cannot turn off. This results in me thinking about too much in too little time. Don't get me wrong about wanting to do something with the product, I very much look forward to learning and making it.

Once you actually make the move in your mind from Excel to a RDBMS, you give up the loosey- goosey no rules way Excel handles data for something structured and firm. Right now you seem to be pining for this flexibility. If I define a field as numeric, a RDBMS will simply not allow me to enter "N/A" as a value. If you don't like that, you should probably stick with Excel rather than asking how to make a RDBMS allow "N/A" in a numeric field (just an example).

RDBMS is the only thing really that fits what I'm trying to do. I person can only learn so much at a time - it's not easy to learn something without having reference to things you've done before. For me it feels like learning Chinese by ear sometimes. If anything, I'm proud of the things that I've already managed to do with my own research; not that it's anywhere close to where it's going to be once I'm done

I posted before as an ordinary member, having had some negative experiences with using Excel as a direct tabular data source. However, I now have to put on my moderator hat.

Pat Hartman has apologized for her harsh response. None of us intend to be harsh with other members, new OR old, in any of the technical forums. (Non-tech forums? No promises.)

I think we both have more information now to make sure that this doesn't happen in the future.

You do, however, have to realize that we are all volunteers here and (last time I checked) also all humans. We can get frustrated when we see someone going down a costly or questionably-effective path even after telling them of the potential massive pitfalls. It frustrates us because it seems like we have wasted our time or our many years of experience are being ignored. Have you ever been watching a movie and want to yell to the protagonists "Don't open the door..." after which they open it anyway and the bug-eyed monster immediately jumps out?

I was just gathering information and opinions. It was about having the information for me to let go off the subject. Not only did the post before showed me having closed the idea of having to do anything with this; the topic was already put to solved.

I get your point about the movie, but do you get how it feels to me as someone who's dipping his toes to something new - that was not a good experience. Don't let the tool be a bigger problem then the problem; it's like you said yourself - we're all human. That'd include the recipient of the message.

I hope this helps you to understand better why you got the response you got. We also hope that you can find help here, short-term OR long-term, to better facilitate your projects. But finally, please DO understand that it is our way to warn you of those pitfalls in your path even if that really isn't what you wanted to hear. Would you want your doctor to hide a dire diagnosis from you on your next wellness check? Or would you prefer blunt honesty?

I understand where the tone comes from, but I'll never respect it. Kicking and bashing are not considered to be tools in a healthy learning environment. If I wanted to be gaslighted about something stupid, I'd not join a forum to learn access; I would've joined Facebook or X. Blunt honesty looks differently from what's over there Docman, you know this as well as I do; so let's not compare appels and bananas here. I'm not here to cause a problem, but I'm highly allergic to being shit on and false assumptions. Either ask or zip it - seems pretty simple.

As you said, we've got a better understanding now. I'm sure that future conversations will be better in tone.

 

[GPGeorge]

I'm not arguing with that per se but even Excel has a "data type" although it is fluid. That is my objection to it. Why bother with a data type if you can put any ol' crap in the cell?

I was focusing more on the fact that the data has to be part of a column, which is part of a table, plus the fact that it has a defined data type that doesn't change depending on the user's mood.

 

[MarlonVisser0408]

I'm not arguing with that per se but even Excel has a "data type" although it is fluid. That is my objection to it. Why bother with a data type if you can put any ol' crap in the cell?

One does not need knowledge to put something in a excel cell. It's a good way to solve skill issues.

 

[Pat Hartman]

One does not need knowledge to put something in a excel cell. It's a good way to solve skill issues.

Skill has nothing to do with anything. Why do you not care if the data entered is valid? Doesn't seem much point in keeping random keystrokes. If a field is labeled DOB, it should contain a date or be null. If you want to allow partial dates, then use three fields. AND fields like dates should be checked for sanity, you wouldn't just accept xyz and let it go at that. Or even 3/14/205. Unless you are the SSA, 205 probably isn't a valid birth year unless we are talking about historical figures.

 

[CJ_London]

Other issues with excel - 1 million row limit and no indexing

 

[Pat Hartman]

One does not need knowledge to put something in a excel cell. It's a good way to solve skill issues.

You actually solve skill issues by creating a coherent interface that doesn't accept bad data. The ball is 100% in your court. No user needs to know he is using Access rather than Excel if you don't let him get behind your interface.

 

[Pat Hartman]

It's data from more then a hundred tables and it's driving me insane.

Start by cleaning up the hundreds of separate sets of data. This will familiarize yourself with the underlying data. This may or may not be easier with Excel and it is invariably a tedious job which hopefully, you can automate to some degree rather than having to do it all manually. Depends on what the inconsistencies are. A database application is all about the data. If you are consolidating data from 100 different users, the first step is consistency. Define the Entities (Customers, Orders, Products, Classes, Teachers Suppliers, etc) Define the columns associated with each Entity. Define the valid value ranges as best you can. If you are combining data sources, the objective is ultimately that you want to compare apples to apples and the only way to do that is to ensure the integrity of the data in any given column.

Why not start by describing the current application? How is the data stored now? Who maintains it? What is the goal of the new application? What kinds of outputs to you ultimately want from the consolidated data? Is everyone working on the same LAN? Because if they are not, Access may be problematic depending on the infrastructure your employer can support. You may actually need a WEB app and can't use Access at all if the users of the system will be anonymous. This website supports anonymous users and so Access, the RAD tool would be totally inappropriate as a development platform although ACE the database that underlies Access could be used to hold the data. I would never recommend that but ACE is a RDBMS and so technically can support a WEB app but there are far better RDBMS' for that task.

When you create an application, YOU are the master of the universe as far as structure goes. The users describe their goals and what they want out of the application and you need to guide them into simplifying the interface so even dummies can successfully enter data correctly. And you need to write code to enforce the business rules the domain experts explain to you. i.e. Customers may be individuals or they may be companies. When they are companies, CompanyName is required but when they are individuals, LastName is required. An easy way to implement this rule is to ask the users to tell you their intention. Are they entering a new company or a new individual? That question allows you to actually show/hide fields to eliminate confusion regarding what data is required. If you just give them all the fields, then you need to create different logic to avoid conflicts. Should you allow both CompanyName AND LastName? Does a company have human contacts? If so, you may want to use the FirstName/LastName to allow a contact name to be entered. The users will provide the business rules that tell you how the data needs to be validated. It takes thousands of similar decisions to create even a simple application.

 

[MarlonVisser0408]

I think I just might open up a seperate topic. There's much for me to learn and when asking questions related to it, it might as well be there - to keep on topic with the post.

 

[The_Doc_Man]

I think I just might open up a seperate topic. There's much for me to learn and when asking questions related to it, it might as well be there - to keep on topic with the post.

It might help keep the discussions about different issues separate so that they will be less likely to get all tangled with each other. And we don't charge by the thread count.