Is there a program out there that can be used to do Access Queries and have a UI as shown?

[judgedredd]

I am not sure if this is the place to post such a question but since it relating to Access queries I thought I would post it here (and its my first post).

I have been trying to look for an Access database 'query viewer' that has a user interface (UI) similar to what I have in the following image:



The grey boxes (apart from the Choose database field) would be fields that one can input known data. One would choose any one of the fields to input the data. When one clicks 'SUBMIT' a query would be made to the chosen database and all the related records to that input would populate the various fields from the database itself. Obviously this would be done after mapping the various fields to the access database fields.

I am hoping that there is a program out there that does something very similar to what I want. Using the query function in MS Access is clunky and I would like something more streamlined and easier to use for those who have little to no experience of MS Access. The database(s) would be copied to the folder where this theoretical program is located. The 'Choose database' field would automatically populate with however many databases are in the folder.



Is what I am looking for actually out there somewhere or is it something that would have to be created from scratch?

[June7]

I don't understand why users would even "choose" a database. How many databases are there and why are there multiple databases?

Sorry, have to "roll your own" whatever this tool is you envision.

[judgedredd]

I have been asked to help a freind who was screwed over by his former web dev. This friend had multiple websites hosted on his home server. There were orders processed via some of these websites (totaling over 34,000 orders spread over 3-4 databases/websites) . Each website saved the purchases in a MySQL database. The interface (the code itself) between the site order page and the database was either erased intentionally by the former web dev or it was corrupted. My friend has to show any order returns to the Canadian Revenue Agency in his business tax returns and the original order data resides in one of the databases from these sites (which are no longer available on the net).

I have been able to extract all the users and their orders from these various databases and have put them into their own CSV files. These were then converted to Excel spreadsheets which were then imported into Access. I did NOT consolidate them as each database is from a particular website. Hence why the drop down menu.

Even though the data is now available in MS Access, having to create queries every time one wants to find out who ordered what when a return comes in is tedious in this program. I need a simple interface where one field can be input and everything relating to that input populates the other fields.

[judgedredd]

this was a duplicate post.

[June7]

Simple is a matter of perception.

This is what forms and reports are for. And code (VBA or macros). Or even the intrinsic search/sort tools on the ribbon.

If you want to build a form with a search/filter capability review http://allenbrowne.com/ser-62.html

In your case, there is the added twist of determining which database (table?) needs to be referenced. Do they all have the same structure?

You said "imported into excel", did you mean Access?

[davegri]

OVERVIEW:
I assume each "Database" is actually now a table in a single msaccess database.
You could select the "database" (table) via a combobox, which would become the form's recordsource.
Then fill out as many of the gray text boxes as you wish.
Then when the submit button is clicked, create a query string in VBA that filters the form's recordsource table, using the gray text box values.

[judgedredd]

Thanks June7 for spotting the error (importing into 'excel' instead of Access). This has now been corrected.

As to the structure of thew database they are all different.

[judgedredd]

OVERVIEW:
I assume each "Database" is actually now a table in a single msaccess database.
You could select the "database" (table) via a combobox, which would become the form's recordsource.
Then fill out as many of the gray text boxes as you wish.
Then when the submit button is clicked, create a query string in VBA that filters the form's recordsource table, using the gray text box values.

No. Each database has its own CSV/Excel file. The fields are different in the databases which is why they have stayed separate. VBA is beyond me at this time.

[judgedredd]

June7, thanks for the link to that search form example. I can see how the design I have at the top of this thread can be implemented using the forms module in Access.

[Edgar]

I uploaded your screenshot to ChatGPT and had it generate the tables, some records and the strings for the code in the way Access wants them. Never did something like this, but I guess it should be similar to what you want to do.

I hope it helps. Let me know what you think.

[June7]

Didn't you say data was imported into Access?

Possibly you will need a form for each db. Perhaps a menu form for selecting db and opening appropriate search form.

If you don't want to delve into VBA, look into macro coding and possibly parameterized query, also demonstrated in Allen's article.

The more "user friendly", the more code.

[judgedredd]

I uploaded your screenshot to ChatGPT and had it generate the tables, some records and the strings for the code in the way Access wants them. Never did something like this, but I guess it should be similar to what you want to do.

I hope it helps. Let me know what you think.

That's an interesting way to do it!!!

Thanks for this. I will try it out.

[judgedredd]

Thanks June7 for your input. I will try Edgar's way first.

[June7]

The database(s) would be copied to the folder where this theoretical program is located. The 'Choose database' field would automatically populate with however many databases are in the folder.

So you have imported each dataset to its own Access file so now you have multiple Access database files to work with. Can be dealt with. Just means your frontend GUI will have to establish multiple backend links.

[davegri]

No. Each database has its own CSV/Excel file. The fields are different in the databases which is why they have stayed separate. VBA is beyond me at this time.

I don't see why you couldn't put all those tables in one database.