MS Access gurus in the house? Hopefully basic question

[Not DJSapp]

Hi database folk

So I spent the morning figuring out that I even had MS Access on my laptop, and afternoon trying to figure out how to use access. I think it can do what I need it to do and Excel won't present stuff nicely for my purposes (yes, I know sorting and filters are a thing, but I'm not going there, it's a format disaster). I need a report generated from a dataset to do the following:

From the report setup/entry screen, I want to have dropdowns to pick a category. No free form

One the category is picked, optionally pick a sub-category that only contains choices from the category selected

Once the sub-category is picked (or not), optionally pick an ID number from a dropdown that is available given the selections above

Push a button and ping the database, return a report with beautifully formatted data that matches the inputs above.

That's it. Only selecting one category at a time. Maybe add an option for a sub-sub-category, I haven't decided if that's going to be necessary in the data set.

I feel like this should all be possible, and it's likely easy to do. The barrier to understanding how queries, forms and reports all work together still eludes me. I need this to be easy enough for a COO to understand how to operate it, and pretty enough that they'll do something with the data.

[m2711c]

That sounds like a job for linux.

[TAFKALVS]

People still use Access?

[Skidog]

You can link to excel sheets for data. Might need odbc connections configured

Sent from my Pixel 4a (5G) using TGR Forums mobile app

[Not DJSapp]

People still use Access?

You have a better program to quickly filter and generate reports from a 50k+ item database?

The data is already in excel, it just looks hideous and is unwieldly. I also don't trust the people that need this to manipulate it to get the information they need without borking the data.

[retirwntm]

Why not use pivot tables?


Sent from my iPhone using Tapatalk

[Not DJSapp]

Why not use pivot tables?


Sent from my iPhone using Tapatalk

See above. I don't trust the seagulls with anything more than dropdown choices and a run report button.

[PB]

Dammit NDJ, thought you had multiple sclerosis.

[retirwntm]

I think a table linked to the database that can be refreshed then pivot tables are your best option. PM me if you want me to run you thru it. We can set up a teams meeting if need be.


Sent from my iPhone using Tapatalk

[bagtagley]

Our R&D peeps generally point data neophytes to Airtable if spreadsheets aren’t cutting it (which is rare, Excel is pretty powerful and much less of an asshole than Access). I don’t have much experience with it, but our users seem to like it. May be overkill for this, though.

[TAFKALVS]

You have a better program to quickly filter and generate reports from a 50k+ item database?

The data is already in excel, it just looks hideous and is unwieldly. I also don't trust the people that need this to manipulate it to get the information they need without borking the data.

50k rows is nothing. PivotTable with a hidden/separate data sheet or use MS PowerBI as the interface on top of the spreadsheet.

[Percy Rideout]

50k rows is nothing. PivotTable with a hidden/separate data sheet or use MS PowerBI as the interface on top of the spreadsheet.

This guy amazes old partners in company meetings with his necromancer computer skills.

[Not DJSapp]

Ugh, I think I need to watch a day's worth of youtube to figure out how to do this. Maybe I'm asking the wrong thing. Here's what I have and what I need:

I have a large dataset. Each line contains 16 pieces of information. I need to be able to search by selecting 3 or 4 of these, and then have it present in a report that is visually pleasing without additional effort, and I need this to come from a UI that a troglodyte could operate. If I were the only person using this, I'd just run filters on the excel table and be done. I also cannot trust ANYBODY to have direct access to the dataset because those folks always manage to delete something or screw something up.

This dataset will also grow over time. The 3-4 fields that we are using for search need to be selected from a dropdown so I don't have 47 different spellings of 'concrete' as a search/filter term.

[wendigo]

Know anyone around with R Shiny skills?

[Not DJSapp]

Know anyone around with R Shiny skills?

Sir, this is a McDonalds.

Is there a way to make an excel pivot table update filter results to limit the choices based upon report filters already applied? For example:

Report Filters--
Category: Concrete
Subcategory: Formwork
Sub-sub-category: Walls

Once I choose concrete, I want the dropdown for the subcategory to read the filter applied and only display items that are in the category of Concrete. And so on down the line. There are way too many subcategories to make this useful without filtering the filter

edit: nevermind - found slicers. This should do the trick. I wanted the report filters to be dependent

[irul&ublo]

People still use Access?

Send him a pm on AOL Messenger and he’ll explain it

[dannynoonan]

How is it, that people who are apparently this stupid, need to be presented with this much information?

Have your admin/secretary send them a PDF of whatever their area of responsibility is, on some regular basis, and call it good.

[shroom]

naw not with a standard pivot table. you can do it with formulas like getpivotdata + list + a dashboard, but the files get heavy quick and its not super fun code to write ifififififififif

it’s the same in every business, i am convinced. password protect that file, lock sheets…