Moving multiple Excel spreadsheets into Access + New at Access

[ldanas]

Hiya! A little bit of info before anyone jumps into conclusions: I'm hella new at Excel and Access and stuff, am a young 18 y.o who's got no clue how Access works, and has little Excel knowledge, and my boss told me to make an Access file where he could type anything within a search box and he'd get the results of the truck repairs, what have been changed and so on. Like, someone types something in excel, and it'd show up in Access too in case he tries to search a specific word like "Oil change" and *Bam* he sees all the vehicles which have had an oil change. I got no clue how to describe this to be honest. Hope someone gets the memo. And, where should I honestly start with this? Here's an example on what I mean, I got like a whole lot of spreadsheets that I wanna use in Access, where e.g. my Boss would type just 1 word in, and it'd show all of the possible results related to that word. Here's an Excel pic to understand what I mean.



Edit: Wrong screenshot, I made this kind of search box in Excel with the filter function, but I wanna do the same thing in Access. Example from Excel

[CJ_London]

and it'd show up in Access too in case he tries to search a specific word

link to the excel spreadsheets - see the external data tab on the ribbon

Beyond that, not enough info to provide any suggestions

for example

where he could type anything within a search box

you can show the navigation bar if not already visible. There is a search field on that


search will find the record but not restrict the display to just the record

So perhaps your boss wants to filter? Filtering will limit the records displayed to whatever he types or selects. He can do that by right clicking on a field which shows a menu (this is closest to Excel functionality)


if the searching/filtering is more complex - for example which truck had an oil change at 8am, then searching alone is not an option. You have two fields you need to filter, the first for the operation type (e.g. oil change), then the time (8am) or perhaps you need to search multiple spreadsheets. For this you will need to use vba to capture the search terms and then apply them.

Be aware that excel and access are completely different and beyond your basic search/filter requirement you will need to rethink what you need to do. Simplistically, Excel stores its data 'wide and short' often with repeating data (e.g. the truck reg number) in one table which acts as datastore and presentation. Access stores it's data 'narrow and tall' and in separate tables (one for trucks, one for servicing, etc) which are joined together in queries and presented in forms and reports. Excel tends to do horizontal calculations, whilst Access does vertical ones. May not make much sense to you at the moment but do a search on 'database normalisation' and you will begin to understand how they differ in the way they work.

It may be all your spreadsheets are basically the same, just in different locations. In access you could combine these into one 'dataset' - might be one table or several, depends on where you are going.

Please note many responders will not click on links regardless (I won't for example) due to the risk of hacking etc. Please use the image button to insert a picture.

[mike60smart]

Hi
Can you upload a copy of an actual Excel file?

[Micron]

As CJ_London as pointed out, your worst mistake would be to dive into this without understanding db normalization. See post 15 here for a set of links to subjects that will help eliminate a lot of mistakes that novices make. One other thing: the goal a common one so you might be able to find a template that you can modify. However, many of those contain design elements that are commonly thought to be poor design, so if you grab a template and dive in and don't review any of the links provided first then you're not doing yourself any favours.

BTW, predictive and corrective maintenance and equipment reliability was my career for a time.