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...

 

[JayRo71]

Here is a slimmed down example of 2 typical records that I am importing.

Summerville Commons, Building A, 24 Gallons, 1/15/2021
Metro West, Building A, 60 Gallons, 2/26/2021

If I am to divide the data into 3 tables, I would need to find a way to look up the index number for Building A at Summerville Commons and Building A at Metro West. I'm not sure how this can be done without linking the property name? I have found that if I do not include the property name in my append query, the new record is appended to Building A at both Properties.

 

[Andrzejek]

So, you do not get the Account Numbers in your data like you were hoping. :-(

And yes, if you want to transport this kind of data into 'properly designed normalized relational data base', you would split every record and do some look-ups, including: "24 Gallons" would not have to do anything with Gas or Electric, right? Another look-up.

---- Andy

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

 

[JayRo71]

Still working on getting account numbers. That would make everything much easier as they should be unique, even though from multiple utilities. The solution there might be to add a prefix to the account numbers that describes the utility provider. Ie. NG12345 for National Grid. and EV12345 for Eversource. This way I will not end up with a duplicate account number 12345 from both utilities. So that will lead me to have to add another table = Utility Providers and manually assign the correct utility providers to each property, which should not be that hard as there are only about 70 properties. Here again though, in order to assign the prefix, I would need to look at both the Utility type and the Property Name to assign the correct prefix. It's almost as if I am passing along the same problem of having to link two fields instead of one

 

[Andrzejek]

If you 'decipher' my post from [tt]8 Feb 22 16:04[/tt], you may realize that your Account Numbers do not have to be unique, and you do not need to add any prefixes to anything.

You would have a Many-to-Many relationship, since you have many Buildings with many (3) Utilities each.

---- Andy

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

 

[JayRo71]

I thank you for the generous amount of time you have spent helping me to figure this out. While I am unable to accomplish the perfect database, it appears to be accomplishing what I set out to do, which is to create a worksheet for each spike that will allow me to report or list a history of spikes by building, what the cause was and what has been done to correct them. The worksheet will have an active status as you suggested as well as an "in progress" "ignored" and "resolved" status that will leave the record open or close it for the purpose of keeping me focused only on active.

I have learned a lot from you and I am much better at databases because of it.

Thank you very much.

 

[JayRo71]

Thanks for pointing me back to the post. I completely understand the structure and understand its necessity and I find it very easy to create a normalized database if I am creating the records. I would select a property from a property dropdown and a building from the building dropdown and all of the records would be perfect and linked using only one join from each table to another. I just cannot figure out how to do it in the reverse. The records are provided with no index accept for maybe the account number which I think you are saying does not have to be unique. No Field in the table has unique values, so the only way to index records is to create an index that has a combination of two fields, or use joins in my queries that connect two fields from each table.

If there is a workaround, it seems that I do not have exposure to an operation that creates some kind of a lookup that does not rely on a unique index within a single table. Perhaps it is something using sql or VB and does not fall neatly into a single design view append or update query.

 

[Andrzejek]

>No Field in the table has unique values
Well, Primary Key field is unique...

>so the only way to index records is to create an index that has a combination of two fields, or use joins in my queries that connect two fields from each table.
Again - No.

But - if you refer to 'the table' as your CSV file (if so, it is NOT a table):

Summerville Commons, Building A, 24 Gallons, 1/15/2021
Metro West, Building A, 60 Gallons, 2/26/2021

You can always get the PropertyID by:
[tt]Select PropertyID from tblProperties Where Name = 'Summerville Commons'[/tt]
Let's say that gives you PropertyID of 12

To find the Summerville Commons Building A's ID:[tt]
Select BuildingID from tblBuildings
Where PropertyID = 12 and Name = 'A' [/tt]
(or [tt]Name = 'Building A'[/tt], depending how you keep your data in your tables)

And if you get the BuildingID of 123, that's ALL what you need (123) to know which Building in which Property this record belongs to.

---- Andy

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

 

[JayRo71]

Thank you for the very important correction. I am referring to the excel file that was a csv and I do plan to take your advice and not put it in excel at all and I will stop referring to it as a table. These are the tips that I really appreciate because if I learn to speak the language, it will be much easier to ask the right questions.

UPDATE tblProperties INNER JOIN (tblBuildings INNER JOIN tblSpikes ON (tblBuildings.Property = tblSpikes.Property) AND (tblBuildings.Buildings = tblSpikes.Buildings)) ON tblProperties.pkeyPropertyID = tblBuildings.fkeyPropertyID SET tblSpikes.fkeyBuilding = [tblBuildings].[pkeyNewBuildingID];


This query is successful at putting the correct building number id into each record in the spikes table. Does this look like a variation of what you wrote? It works perfectly as much as it may be incorrect?

UPDATE tblProperties, tblProperties INNER JOIN (tblBuildings INNER JOIN tblSpikes ON (tblBuildings.Buildings = tblSpikes.Buildings) AND (tblBuildings.Property = tblSpikes.Property) AND (tblBuildings.Buildings = tblSpikes.Buildings)) ON (tblProperties.pkeyPropertyID = tblBuildings.fkeyPropertyID) AND (tblProperties.pkeyPropertyID = tblBuildings.fkeyPropertyID) SET;

This query does not work as it disregards what property the building belongs to.

 

[JayRo71]

This also seems to work...

UPDATE tblBuildings INNER JOIN tblSpikes ON (tblSpikes.Property = tblBuildings.Property) AND (tblBuildings.Buildings = tblSpikes.Buildings) SET tblSpikes.fkeyBuilding = [tblBuildings].[pkeyNewBuildingID];

 

[Andrzejek]

We are going in a circle...
[tt]
UPDATE tblProperties
INNER JOIN (tblBuildings INNER JOIN tblSpikes
ON (tblBuildings.Property = tblSpikes.Property)
AND (tblBuildings.Buildings = tblSpikes.Buildings))
ON tblProperties.pkeyPropertyID = tblBuildings.fkeyPropertyID
SET tblSpikes.fkeyBuilding = [tblBuildings].[pkeyNewBuildingID];
[/tt]
Your tblSpikes table should NOT know about tblProperties.
As long as you have correct BuildingID - you are set.

tblProperties -> tblBuildings -> tblSpikes

tblProperties is your main table, it does not care about exsistance of any other tables.
tblBuildings knows only about tblProperties, it does not care about tblSpikes
tblSpikes knows only about tblBuildings, it does not care about tblProperties

---- Andy

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

 

[JayRo71]

The final query that I posted uses only spike and buildings table to assign building id to the building foreign key in tblSpikes. In that last query, tblProperties is not used and it works.. Is this one finally correct?

 

[Andrzejek]

I see your:[tt]
... ON (tblSpikes[red].Property[/red] ...[/tt]
and that - in my opinion - is wrong. Or should I say: superfluous

---- Andy

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

 

[Andrzejek]

And why do you have:
UPDATE tblProperties ...
or
UPDATE tblBuildings ...

these 2 tables should NOT require any Updates. Once they are set, the data should not change. Unless you rename your Property, or rename your Building.

You should not even have any:
UPDATE tblSpikes ...
I would only expect statements like:
INSERT INTO tblSpikes ...


---- Andy

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

 

[JayRo71]

I think I finally figured out where I am going wrong. I need to do more with the original temporary dataset to assign the correct building id's to the fkeyBuildings field in the spikes table as I am appending the new records to tblSpikes. This way, tblProperties, tblBuildings and tblSpikes will have normalized form and can be used in forms and reports, while the original dataset gets discarded and is not part of any relationships. The way I am approaching it now is that I am trying to do too much with the tblSpikes that should really have been established when pushing the data from the original dataset to the 3 tables.

 

[JayRo71]

I am using update in tblSpikes because it has no values in the fkeybuildings field. I need to update those values.

 

[Andrzejek]

You should get the value of fkeybuildings that you need in tblSpikes just before you INSERT a record into tblSpikes table. That field (tblSpikes.fkeybuildings) should be a Foreign Key field to tblBuildings' Primary Key field and you should NOT be able to Insert a record into tblSpikes without that information.

---- Andy

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

 

[JayRo71]

Okay, I think with my new mindset, this should be pretty easy. The goal is to get tblProperties > tblBuildings > tblSpikes normalized. Those three tables alone will make up the database, unless of course I decide to create a separate table for each utility. If I keep my eye on the goal, it should not matter how I get the data from the imported dataset to those tables. The only place I should be doing any lookups that require more than one field is when I am farming data from the dataset to put into the normalized tables. Once a week, I will be importing a new temporary dataset and only adding the new data to the tables and not overwriting the old data. This can easily be accomplished creating indexes in the tables that require unique values, so when I append new records, the duplicates will be omitted.

When all is said and done, I will have a database with 3 normalized tables. I feel like I can do this now and will have at it again tomorrow.

Thanks