Select records on subform using a combo box with a twist 1

[JayRo71]

First, allow me to explain that I know just enough about Access to impress someone that knows nothing about Access. I'm just getting my feet wet with VBA and do pretty well with queries in design view, but i'm not great with the written form of SQL.

I was able to use a combo box value to select records in a subform. The only problem I am having is that the primary key I am using in the combo box is made up of two fields. txtProperty and txtBuilding. So I might select a value in the combo box that is "Metro Apartments" "Building D". The result is that I get records for "Metro Apartments" but a few records from "Building B" (not "Building D") might be returned. Selecting a different value from the dropdown to "Metro Apartments" "Building A" does not change the returned records. However, if I select a different property such as "Main Street Apartments" "Building 176", the returned records will change to "Main Street Apartments", but not necessarily "Building 176". Maybe it would show records from "Building 174". It seems to disregard the value from the txtBuilding part of the key and I am not sure why it returns whatever building it displays.

If I understood what is going wrong, I might be able to ask the question better or search down a solution myself. Any help to point me in the right direction would be greatly appreciated.

Thank you...

 

[Andrzejek]

Let's get some terminology straight.

>the primary key I am using in the combo box is made up of two fields

Primary Key is Property+Building fields (?) in the table that looks like:

[pre]
PropertyAndBuildingTable
Property Building OtherFields
Metro Apartments Building A ...
Metro Apartments Building B ...
Metro Apartments Building C ...
Metro Apartments Building D ...
Metro Apartments Building E ...
Main Street Apartments Building 175 ...
Main Street Apartments Building 176 ...
Main Street Apartments Building 177 ...[/pre]

>txtProperty and txtBuilding
Names suggest that those are text boxes on a Form...(?)

>select a value in the combo box that is "Metro Apartments" "Building D"
Do you concatenate the Property and Building fields from your table in your combo box?

What SQL do you have to create "returned records"?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JayRo71]

The table you have illustrated is correct. Thank you for taking the time to type all of that. When I create my fields in a table, I put add a prefix to the field name to suggest what type of data is in the field. I'm not sure where I picked up that convention, but it was back in 2005 that I last used access and even then I was self taught from a book.

the text boxes are not on the form, perhaps my naming convention is useless, confusing and should be avoided.

I selected two fields from my tblProperties for the combo box. When I select the drop down, it shows every combination of property and building.

Combo box values are for example:
Metro Apartments Building A
Metro Apartments Building B
Metro Apartments Building C
Main Street Apartments Building 175
Main Street Apartments Building 176
Main Street Apartments Building 177

The subform uses a query to pull records from tblEnergySpikes. I also have some expressions in the query to do calculations, so I need the sub form to get its data from the query, not a table.

I was hoping I could link the sub form to the combo box. There is a relationship setup between tblProperties and tblEnergy spikes. The two fields that create the primary key, appear in the tblEnergy and used to link tblProperties to tblEnergy.

Hope this all makes sense. I'm literally an Energy Manager for a living, so the access database I created has already helped me a great deal, I'm just trying to make it better. I'm hoping that by selecting the Property and Building, the sub form will show me all the records related to that specific Property / building.

I have not concatenated the Property and Building fields from the table, but I think the lightbulb just went off in my head. I believe you are suggesting I create a query to concatenate the two fields and use that value in my combo box. I believe I would have to concatenate in both the combo box query and the subform query so that the records could be linked.

Thank you...

 

[Andrzejek]

Aside from establishing and following Relational Database Design rules, could you show a sample of data from your query (based on tblEnergySpikes)?

If it is something like:
[pre]
query from tblEnergySpikes
FieldA FieldB FieldC
Metro Apartments Building A ... ...
Metro Apartments Building B ... ...
Metro Apartments Building C ... ...
Main Street Apartments Building 175 ... ...
Main Street Apartments Building 176 ... ...
Main Street Apartments Building 177 ... ...[/pre]

You would simply say:[tt]
strSQL = "Select * from MyQuery Where FieldA = '" & ComboOnForm.Value & "'"[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JayRo71]

Hi Andy, This is the best I can do to answer what you are asked. I might be missing your question altogether.

SELECT tblSpikes.ID, tblSpikes.curCost, tblSpikes.nbrDeviation, tblSpikes.txtSpikeUsage, tblSpikes.txtType, tblSpikes.[txtBill Period], tblSpikes.[txtAccount Number], tblSpikes.txtProperty, tblSpikes.txtBuildings, tblSpikes.txtCoverage, tblSpikes.[txtAccount Nickname], tblSpikes.[datDate Detected], tblSpikes.[curBill Total], tblSpikes.txtStatus, tblSpikes.txtReason, tblSpikes.txtNotes, DateValue(Right([tblSpikes]![txtBill Period],8)) AS [Bill Period End Date], tblSpikes.hypChart, tblProperties_NoDuplicates.nbrUnits, tblProperties_NoDuplicates.nbrBedrooms, [tblSpikes]![curCost]/(Left([tblSpikes]![txtSpikeUsage],Len([tblSpikes]![txtSpikeUsage])-4)) AS ExprRate, [curBill Total]/[ExprRate] AS ExprTotalGallons, DateValue(Left([tblSpikes]![txtBill Period],8)) AS [Bill Period Start Date], [Bill Period End Date]-[Bill Period Start Date] AS ExprBillCyleLength, [ExprTotalGallons]/[ExprBillCyleLength]/[nbrBedrooms] AS ExprTotalGalBedDay, Left([tblSpikes]![txtSpikeUsage],Len([tblSpikes]![txtSpikeUsage])-4)/[nbrBedrooms]/[ExprBillCyleLength] AS ExprLeakGalBedDay, [ExprTotalGalBedDay]-[ExprLeakGalBedDay] AS ExprForcastGalBedDay
FROM tblProperties_NoDuplicates INNER JOIN tblSpikes ON (tblProperties_NoDuplicates.txtBuildings = tblSpikes.txtBuildings) AND (tblProperties_NoDuplicates.txtProperty = tblSpikes.txtProperty)
WHERE (((tblSpikes.txtType)="water") AND ((tblSpikes.txtStatus)="unresolved" Or (tblSpikes.txtStatus)="In Progress") AND ((DateValue(Right([tblSpikes]![txtBill Period],8))) Between Date() And DateAdd("d",-200,Date())))
ORDER BY DateValue(Right([tblSpikes]![txtBill Period],8)) DESC , DateValue(Left([tblSpikes]![txtBill Period],8)) DESC;

Thanks

 

[JayRo71]

Please overlook the fact that my example was somewhat hypothetical with regard to table, query and field names. I was trying to give an example. The actual table name with the subform data is tblSpikes. The actual table name with the property and building information is tblProperties_NoDuplicates

Thanks

 

[Andrzejek]

Sorry, that does not help me to help you :-(
Maybe somebody else will jump in and provide you with some help....

Looking at your Select statement... you already use some aliases for your fields (columns), but you may benefit from using aliases for your tables. That will make your Select a lot shorter and, hopefully, easier to read.
[tt]
FROM tblProperties_NoDuplicates [highlight #FCE94F]P[/highlight] INNER JOIN tblSpikes [highlight #FCE94F]S[/highlight]
ON ([highlight #FCE94F]P[/highlight].txtBuildings = [highlight #FCE94F]S[/highlight].txtBuildings) AND ([highlight #FCE94F]P[/highlight].txtProperty = [highlight #FCE94F]S[/highlight].txtProperty)[/tt]
etc.

And - if you can - avoid Spaces (or Access reserved words) in your field names.
This way you can eliminate all [tt][][/tt] in your syntax.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JayRo71]

Andy,
Thank you for posting the article about Relational Database Design Rules. I'm very much aware of the need to properly normalize a relational database, I'm just not good at doing it. The information you posted will be a big help. That said, the data I am getting is provided in table form from a third party. The data for the spikes table is imported once a week and only new unique records are added. The separate property table relationship was my idea as a way to get a history of energy or water spikes by property by building. Unfortunately, I have very no control over the source or structure of the data in tblSpikes and can only manipulate it through the query above.
Thanks

 

[JayRo71]

Thanks for the tips. I was typing as you were. I was wondering about the []. Thanks for pointing that out. Have a great day and thanks for your efforts!!!

 

[Andrzejek]

I took another look at your SQL and you may have [blue]this part[/blue] missing where you ask for a particular Property/Building

FROM tblProperties_NoDuplicates P INNER JOIN tblSpikes S
ON (P.txtBuildings = S.txtBuildings) AND (P.txtProperty = S.txtProperty)
WHERE ...[blue]
AND  P.txtProperty = 'Metro Apartments'
AND P.txtBuildings = 'Building D'[/blue]

or

AND  P.txtProperty & ' ' & P.txtBuildings = 'Metro Apartments Building D'

If it was me, I would use 2 combo boxes: one would have (Distinct) Properties, and based on the selection of a Property I would populate second combo with Buildings for selected Property.

FROM tblProperties_NoDuplicates P INNER JOIN tblSpikes S
ON (P.txtBuildings = S.txtBuildings) AND (P.txtProperty = S.txtProperty)
WHERE ...
AND  P.txtProperty = [blue]cboProperty[/blue]
AND P.txtBuildings = [blue]cboBuilding[/blue]

Access allows you to use Spaces and reserved words in the names of the fields, but just because it is allowed, it should not be used, IMHO
Life is a lot easier if you do not do that.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JayRo71]

Good morning. As always, thank you for your thoughtful responses. Perhaps I need to get back to the start of this and how it has evolved.

My workflow begins when I export a csv from a third party website. I then convert the csv into an excel file for import into access. From the start, I have thousands of records with no primary keys, lousy naming conventions, and mixed data types in single fields for example a field value might be "20 Gallons" or a bill period might be "12/16/2020 to 12/30/2020" I created some expressions in the append query to extract correct data from each of these fields.

I download this dataset from the third party once a week and upload it into access once a week. I had to find a way so that only unique records would import, so in the tblSpikes, I created an index that includes 3 fields... the combination of which must be unique. Usually this prevents about 95% of the records from appending, using an append query because they already exist.

The tblProperties_NoDuplicates has a primary key that is made up of two fields = txtProperty and txtBuildings. The tblSpikes includes those two fields as a foreign key and the tables are linked.

I think before I even get into creating a form, this whole process is butchered and needs to be straightened out. If I could get away from the multi field primary key, I think it would help.

If I were to create an a single autonumber ID for each property / building combination in the tblProperties_NoDuplicates, there would be no way to put that ID into the imported table as a foreign key. Therefore, I think I am stuck with using a multifield primary key as those two fields exist in the imported table and can be used as a foreign key.

I like your idea of a cascading combo box. I am just not sure how to get the subform to look at both combo boxes for child and master fields?

I believe I could straighten out the naming conventions inherited from the third party software in tblSpikes and then just make the changes in the append query so that I do not pass along the poor naming conventions inherited from the third party database.

This will be my first step, and I am going to share another SQL with you.

Thanks

 

[Andrzejek]

I would probably start with creating some look-up tables, like:

[pre]
tblProperties
ID Property
1 Metro Apartments
2 Main Street Apartments
3 ...[/pre]

and then:

[pre]
tblBuildings
ID PropID Building
1 1 A
2 1 B
3 1 C
4 2 175
5 2 176
6 2 177[/pre]

Do you see where I am going with it

If you follow relational normalized data base approach, there is no limit of what you can do.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Eventually, as far as going: CSV file -> Excel -> Access tables, I would skip Excel all together and go directly from CSV to Access by writing my own code for data validation and data integrity. I know it's a pain to deal with messed-up data like you have in CSV file, but (hopefully) most, if not all, issues you may already have resolved.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JayRo71]

Yes, I see where you are going with it and also see the benefit. I will try creating separate tables for properties and buildings. This makes a lot of sense and should not be too difficult to maintain as there are only about 70 properties and maybe a total of 120 +/- buildings.

The piece that I am still unsure of is how I assign the foreign key to the imported tables? I think that is where the "Look-Up" table comes into play. Intuitively, I would think that an update query would be used to do the lookups and add the foreign key? No need to answer that as I am going to find a video on Look-up tables. Since the property names are a constant and can't be changed in the third party database, it should be pretty easy to look up the id based on the name. I think I am answering my own question now that you have pointed me in the right direction.

It is going to take me a few days to revamp my database. In the meantime I will check back, but have my work cut out for me. Once I get the database more normalized, I may or may not need to revisit my original question about the form. Considering that my process needs an overhaul, sticking to my original question would be putting the cart before the horse.

Thanks again for all of your help and I will post back one way or another how it worked out.

 

[Andrzejek]

>how I assign the foreign key to the imported tables?
You do that when you establish the tables in the data base. In my example, you would assign [tt]PropID[/tt] in [tt]tblBuildings[/tt] table as a Foreign Key field to [tt]ID[/tt] (Primary Key) field in [tt]tblProperties[/tt] table. And that's it.

You will benefit a lot from reading about the proper relational data base design.

What is really hard to find is (IMO) - the simple rule: any data should be in one and only one place in your data base. So, if you have the data: "Metro Apartments", these words should not be anywhere else other than in tblProperties table. Period. No copying data from one table to other table(s). The only 'relation' should be done by Primary/Foreign Key

Just a thought... If, for any reason, any of your Properties may become 'not active' (let's say the property is sold..?), you may also have additional field so you still can keep the (historical) data:

[pre]
tblProperties
ID Property IsActive
1 Metro Apartments 1
2 Main Street Apartments 1
3 Far Away Place 0
4 ... [/pre]

You may even go further with this idea, like a Status of the Property, or Status field in Building table (all based on the data in [tt]tblStatus[/tt] table), etc.
The possibilities are endless

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[JayRo71]

Great idea on the IsActive field.

I have a concern about tblBuildings. "Building A" may exist at multiple properties. It is not a unique name of a building. Would you agree that if there is a potential for duplicate building names, I need to keep the Property Name and Building name together in one table?

 

[JayRo71]

Sorry, another thought. I am trying to compare building names to color names if I had tblColor. If I were to assign the color "green" to multiple properties, there would not be a problem, because there is only one color green. However, if I were to assign "Building A" to multiple properties, it would be a problem because each "Building A" has different characteristics. Unfortunately, I think there is no way around keeping properties and buildings in the same table?
Thank you.

 

[Andrzejek]

>I need to keep the Property Name and Building name together in one table?
No, you do not.
You can have Building "A" on every Property if you want to, not a problem.
Let me use some colors to show you:

[pre]
tblProperties
ID Property[blue]
[highlight #FCE94F]1[/highlight] Metro Apartments[/blue][green]
[highlight #8AE234]2[/highlight] Main Street Apartments[/green]
3 ...

tblBuildings
ID PropID Building[blue]
1 [highlight #FCE94F]1[/highlight] A
2 [highlight #FCE94F]1[/highlight] B
3 [highlight #FCE94F]1[/highlight] C[/blue][green]
4 [highlight #8AE234]2[/highlight] 175
5 [highlight #8AE234]2[/highlight] 176
6 [highlight #8AE234]2[/highlight] 177
7 [highlight #8AE234]2[/highlight] A[/green]
[/pre]

This way [blue]Metro Apartments[/blue] has building [blue]"A"[/blue] and [green]Main Street Apartments[/green] has building [green]"A"[/green]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

This is along 'parent/child' relationship (that is actually the name that is used in the data base). Imagine 5 families: White, Brown, Smith, Johnes, and Presley. In every family there could be a child named Bob. So, with the structure I have shown you, you still can have:
Bob White
Bob Brown
Bob Smith
Bob Johnes
Bob Presley

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Some recommended readings (for your sleepless nights):
A Quick-Start Tutorial on Relational Database Design
Introduction to Relational Database Design
Designing a Relational Database
and any other info from here

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson