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

[RBCCFD]

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.

 

[moke123]

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

 

[CJ_London]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.