Need to create a database that can retrieve records from multiple tables. (1 Viewer)

RBCCFD

New member
Local time
Today, 09:37
Joined
Sep 11, 2020
Messages
7
I am new to Access. I know the very basics. For my work I need to create a database that search for any keyword to retrieve records. I have 4 tables. A SITE table with file number, address, description, TMS # and links to file location and GIS image. A BUIDINGS table, because we have multiple buildings at some sites, with similar info. A PROJECTS table for work order or tasks that can involve numerous sites and building at the same time. A DEPARTMENT table, for departments effected/involved in the project. We need to be able to search by keyword or fields from all the tables to retrieve all records that involved and related to each other, all that in a form design. I have created one form with SITE fields and made subforms for other tables into this form to view or edit them. Had to use subforms in order to get it to work, I was going to use tabs but all records would not show when I entered all table data into the form. I would guess I need to create queries for a keyword text box with list box requery, and queries for text boxes for field text boxes, but not sure how to write the functions or codes needed to make them work. Any help or advice would be appreciated.
 

Attachments

  • BetaDatabase.accdb
    2 MB · Views: 397

moke123

AWF VIP
Local time
Today, 09:37
Joined
Jan 11, 2013
Messages
3,849
A few things before you get started:
Fix your naming convention. Do not use spaces in object and field names. Do not use special characters in object and field names. You have a field "# of structures"
Get rid of the lookup fields in your tables- project list table
Google "the evil of lookup fields"
I would use an autonumber primary key
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 13:37
Joined
Feb 19, 2013
Messages
16,553
You would need filter/criteria for each table. Each sub form would need to be unbound to the parent form

your criteria/filter would need to look at each field so use OR between each comparison
 

RBCCFD

New member
Local time
Today, 09:37
Joined
Sep 11, 2020
Messages
7
A few things before you get started:
Fix your naming convention. Do not use spaces in object and field names. Do not use special characters in object and field names. You have a field "# of structures"
Get rid of the lookup fields in your tables- project list table
Google "the evil of lookup fields"
I would use an autonumber primary key
The field names don't actually have spaces, the caption in general properties does so it displays that way, will that work fine? The verified and # of structures was intended only for my use and not the search form, forgot to mention that, I had to track/verify that info. Read the article, definitely reformatting those fields. Thank you
 

moke123

AWF VIP
Local time
Today, 09:37
Joined
Jan 11, 2013
Messages
3,849
The field names don't actually have spaces, the caption in general properties does so it displays that way, will that work fine?
The sample you posted has spaces in the field names and no entry in the caption property. If you have spaces in the names you will have to enclose all your field names in [brackets ] in your code. The octothorpe # is used to delimit dates so using it leaves you exposed to access mis-interpreting your data.
 

RBCCFD

New member
Local time
Today, 09:37
Joined
Sep 11, 2020
Messages
7
The sample you posted has spaces in the field names and no entry in the caption property. If you have spaces in the names you will have to enclose all your field names in [brackets ] in your code. The octothorpe # is used to delimit dates so using it leaves you exposed to access mis-interpreting your data.
I just remembered why I did Look up Fields in Projects List table, I need to be able to pick multiple Sites or Buildings or Dept for each project. So how else would I be able to do that without having redundant records or would it just be better to copy the record and change info in those fields?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:37
Joined
May 21, 2018
Messages
8,463
So how else would I be able to do that without having redundant records
You do data entry via forms not directly in tables. So no lookups in tables, you put them in the controls on the forms instead.
 

RBCCFD

New member
Local time
Today, 09:37
Joined
Sep 11, 2020
Messages
7
You do data entry via forms not directly in tables. So no lookups in tables, you put them in the controls on the forms instead.
Hi, I tried playing around with it today. I changed table fields to short text. In the form, I inserted all the fields but couldn't figure out how to change the text box to a combo box allowing muliple entries. I tried a combo box on the side which shows a list, but doesn't show check boxes to allow multiple entries. Am I doing it right or in the right direction? Thanks
 

RBCCFD

New member
Local time
Today, 09:37
Joined
Sep 11, 2020
Messages
7
You would need filter/criteria for each table. Each sub form would need to be unbound to the parent form

your criteria/filter would need to look at each field so use OR between each comparison
I so lost with the query. Do I need to add all fields from all tables into this query? what expressions do I need to use next? I tired Like "**" under some fields, seemed to work. Not sure how to translate all that to a form with botton. Thanks
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:37
Joined
May 21, 2018
Messages
8,463
Hi, I tried playing around with it today. I changed table fields to short text. In the form, I inserted all the fields but couldn't figure out how to change the text box to a combo box allowing muliple entries. I tried a combo box on the side which shows a list, but doesn't show check boxes to allow multiple entries. Am I doing it right or in the right direction? T

I did not look at your database. If you want multiple values in a single field that requires a "Multi Value Field". That is the only way to get a control that allows you to select and store multiple values. My guess, you must of had that already. MVFs are often frowned on because they can be very confusing. The traditional approach is to store multiple values in child tables. This requires more work, but it is more transparent. My statement dealt with normal fields. People sometimes put comboboxes in the tables where they should only be on the form. With MVF there is nothing you can do since the MVF control will automatically show up in the table.
 

Isaac

Lifelong Learner
Local time
Today, 06:37
Joined
Mar 14, 2017
Messages
8,738
The field names don't actually have spaces, the caption in general properties does so it displays that way, will that work fine?
I couldn't tell that this particular confusion had been cleared up yet so here goes:

The "spaces in fields" issue that you have is evident if you right-click a Table object (in the left-hand pane of objects) and select Design View. There are a few tables that have spaces in the column name.

However, you might have spaces in form (captions), labels (a control on a form), etc., which is fine.

This conversation is one reason why, despite Microsoft playing fast and loose [in the MS Access context] with the term "field", I like to stick with the following nomenclature, any time I can remember to do so:
- Columns are in tables
- Fields are something on a screen or interface, and maybe queries if one insists

The way MS uses "fields" in ms access is awful, and leaves people ill-equipped to conversate between app developers and database developers in most non-Access contexts. I'm not trying to be a joe celko here nor overly theoretical, but when I can remember I refer to columns as columns despite MS Access's best efforts to confuse me into doing otherwise. :)
 

moke123

AWF VIP
Local time
Today, 09:37
Joined
Jan 11, 2013
Messages
3,849
I think you need to normalize you data before you start building forms, etc.
For instance your tables Building codes and Site codes are nearly the same. It appears site codes has just a couple extra fields although being unfamiliar with your data I dont know their relevance. You should not have redundant data in your tables. Can these 2 tables be combined?

Google database normalization. You'll find with properly organized data the rest is a lot easier.
 

Users who are viewing this thread

Top Bottom