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]

Thank you for your examples.

What if I want to assign characteristics to "Building A" such as number of apartments, square footage, number of bedrooms, etc? Wouldn't I need multiple "Building A's" ?

I'm not positive, but am I correct in saying that it would not work to have a first name table and a last name table. If I wanted to put in weight or eye color for Bob, I would not be able to put those fields on the first name table or the last name table. Perhaps there is a way to add these additional characteristics to the aggregate (relational) records, rather than assigning them to the granular (normalized) records?

Please give me a pass on the terms I am using. As I talk more with experts such as yourself, I will get better at using the correct terms.

Thank you.

 

[Andrzejek]

What if I want to assign characteristics to "Building A" [in particular property] such as number of apartments, square footage, number of bedrooms, etc? Wouldn't I need multiple "Building A's" ?

No, you do not.

[pre]
tblBuildings
ID PropID Building NoOfApt SQFoot NoOfBdrms
1 1 A 30 154871 65
2 1 B
3 1 C
4 2 175
5 2 176
6 2 177
7 2 A 50 123456 75
[/pre]
This way, Building A in Metro Apartments has 30 apartments, 154871 SF and 65 Bedrooms, where Building A in Main Street Apartments property has 50 apartments, 123456 SF and 75 bedrooms

---- Andy

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

 

[JayRo71]

Sorry it took a while but what you are saying is starting to click. I suppose in the real world, I would never talk about a building, without first identifying the property. In the database, I will have to make sure that a building can never be selected, without first choosing the property. Maybe I will find that the relationships in Access give me no choice but to choose the property when selecting a building. If this does not happen automatically, I will be mindful to make it impossible on my forms to select only the building, using cascading combo boxes.

When I say that I need to train my brain, I mean that I have to start looking at the building name as just another characteristic, no different than number of bedrooms, or the color of paint. It is not the primary key and does not have to be unique. Even in the real world, the building name by itself is not enough information to identify the actual building, so nor should it be in the database. I am getting there and will go back to separating the tables, as I did when you first suggested it, before I started overthinking it.

Thanks for your patience...

 

[Andrzejek]

I don’t mind answering some questions here, but you will be a LOT better off if you familiarize yourself with the proper relational, normalized data base design by reading the suggested web sites, plus more. As much as I would want to, I cannot teach you and explain here all these rules that go with the data base set-up.

You will find other people who can help you here, in forum669


---- Andy

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

 

[Andrzejek]

>I will have to make sure that a building can never be selected, without first choosing the property.
People (usually) process information on the screen: Left to Right, Top to Bottom, so you can give user this:

So, naturally, they would select Property first, and then you give them all available Buildings for that selected Property (like I stated before in one of my previous posts)

---- Andy

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

 

[JayRo71]

Hi,

I have a pretty good understanding of normalization of a database, but certainly have a long way to go before I fully grasp the concept in all scenarios.

The problem is that I am working with a dataset from a third party that is not normalized. Trying to normalize this data takes a lot of creativity and experience. The good news is that I now have a table for properties and a table for buildings. The primary key for the tblBuildings is an AutoNumber and the primary key for tblProperty actually uses numbers that are already assigned to each property by my employer. The tblProperties does not have the tblBuildings foreign key in it, but tblbuildings has tblProperties populated foreign key in it.

Here is where I'm really struggling. I still have the tblSpikes that has neither the fkeyPropertyID or the fkeyBuildingID. That is an easy fix, I will just put those fields in there now. However, I have no idea how to update the correct ID's to each of those fields. My best guess is that it will involve an update query with a criteria involving concatenation?

I must have done something correctly as now when I am in tblProperties, I can click the + next to the property and see all of the buildings along with the unit count and bedroom count.

Thank you

 

[strongm]

>What is really hard to find is (IMO) - the simple rule

Normalisation ...

(Edit: oops, looks like normalisation has already come up in the thread)

 

[Andrzejek]

tblProperties is your main/top (parent) table and it will not have any Foreign Key(s).
tblBuildings is your 'child' table and should have Foreign Key field (to Primary Key field in the parent table: tblProperties)

>see all of the buildings along with the unit count and bedroom count.
The way I see it - you should have another table: tblApartments (or tblUnits) where you would have all attributes of an apartment (unit) like: apt_number, bedroom count, square footage, number of bathrooms, storage, balcony, etc. All data per apartment (unit)
To get all of that information 'per building' (or per property), you just do SUM() or COUNT() with your tables joined.

If you decide to have a tblUnits, this table would have a Foreign Key field to a Primary Key field in tblBuilding. This way tblBuildings is you parent table and tblUnits is your child table.

I don't know if you keep track of hallways, stairs, laundry facilities, etc. Attributes of the Building that cannot be assigned to any Unit. If you do, this data should be in tblBuildings table.

I don't know what data is in your tblSpikes table. Is it per building? Per unit?

>My best guess is that it will involve an update query with a criteria involving concatenation?
No. Very unlikely that you would need any concatenation.

---- Andy

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

 

[JayRo71]

The purpose of the database is to track all spikes, their cause and their remedy while providing historical reports, plenty of room to document troubleshooting steps, close resolved spikes, mark sold properties as inactive (credit = Andy) and the ability to leverage past successes, report avoided costs, etc.

tblSpikes data comes from a third party that reports every gas, electric or water consumption variance when compared to a projected energy model based on historical use and industry benchmark. The data included is projected cost, actual cost, percentage of increase or decrease, actual kWh (electricity), actual therms (gas) and actual gallons (water) and the projected kWh, therms, gallons, meter read dates, and bill cycle dates. Each record could be said to represent a meter, however the meter numbers are not included, so the meter numbers are represented by their locations. I just made a phone call and it turns out that they can provide me with account numbers. I think it will be a good idea to make a separate table with those and all of the water/electric/gas information. Hopefully the account numbers will be what I can use to link all of the data to the properties / buildings

I think at this point I should expect to be on my own. I just need to finish normalizing my tables and define the relationships. I'm pretty much there now. As far as linking new imported data to the tables, I'm going to have to do some creative queries. I think it is one of those situations where there is more than one way to add the data and distribute it to the tables. The trick will be to make sure that new Properties/Buildings/meters get new index numbers and others inherit the established indexes.

Thanks for all of your help and it is my hope that others be as generous to you as you have been to me.

 

[Andrzejek]

I would assume 'meters' are associated with Units, you wouldn't have a water meter (or electric meter) at the building. I guess utility charge units / apartments for their services, not the entire building with many Units.

>data comes from a third party that reports every gas, electric or water consumption variance
... per Unit, right?

>Hopefully the account numbers will be what I can use to link all of the data to the properties / buildings Unit (?)

>I'm going to have to do some creative queries
Your queries will be/should be very much straight forward, nothing 'creative' about them

>there is more than one way to add the data and distribute it to the tables.
I can see pretty much just one way to accomplish this, but that's me.

---- Andy

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

 

[JayRo71]

Good morning...

I wrote a big long reply yesterday and was pulled away and never posted. The energy I am tracking is only commons area as the residents pay for their own electricity and gas. Legally the utilities can not share their data with me without a signed consent form. The water is provided to the residents and typically there is only one master meter per building. Therefore, the unit numbers are not critical for this database.

Clicking on the Access icon to begin with is creativity enough for an Energy Professional and very little is straight forward. I'm looking forward to the day when I see this as straight forward. I'm getting there though and I am glad to have a real life need to learn from as I learn much faster by troubleshooting and problem solving than I do from a book.

Currently I have the relationship tblProperties -> tblBuildings, now I need to create the relationship tblBuildings -> tblSpikes. I'm going to struggle with that for a little bit because the data for tblSpikes is not imported with tblProperty or tblBuildings foreign key. Somehow, I have to use an update query to match plug in the correct foreign keys into the new records.

Thanks,

 

[JayRo71]

My tables look great now and even the duplicate building names are not an issue. I tried the following to update the foreign keys with no success..

UPDATE tblSpikes SET tblSpikes.fkeyProperty = [tblProperties]![pkeyPropertyID], tblSpikes.fkeyBuilding = [tblBuildings]![pkeyNewBuildingID]
WHERE (([tblSpikes]![Property]=[tblProperties]![Property]) AND ([tblSpikes]![Property]=[tblProperties]![Property]));

Is the reason it is not working is that I need to specify strings or use " " ?

Any thoughts?

Thank you.

 

[JayRo71]

I also tried this...

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

 

[JayRo71]

Scratch all of that. I had to run two separate update queries, but I can now expand property in a table, expand building, expand spikes. I also tested using a duplicate building name and when expanding to see spike records, only the correct building shows spikes, while the fictitious building correctly shows no spikes. So now that I understand how this is going to work, I have to find a way to streamline the steps so I am not running two update queries each time I do an import. This should be pretty easy.

I think I am all set now and again thank you for all the help!!!!

 

[Andrzejek]

>the data for tblSpikes is not imported with tblProperty or tblBuildings foreign key
Please, do not do that.
No Foreign Key field to tblProperty table in tblSpikes table. Your tblSpikes table should only be connected to tblBuildings by Foreign Key field.

And I can envision 3 Spike tables: tblSpikesGas, tblSpikesElectris, and tblSpikesWater, because you consider 3 different sets of data based on the Utility, right?

---- Andy

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

 

[JayRo71]

Okay, success. I deleted the fkey tblProperty from tblSpikes.

I have properly setup the relationships under database tools. tblProperty is linked to tblBuildings is linked to spikes using the following fields:

tblSpikes is linked to tblBuildings using tblBuildings fkey
tblBuildings is linked to tblPropery using tblProperty fkey

When I run an update query to update tblSpikes with the fkey for tblBuildings, it only works if I create a join between tblSpikes.fkeyBuildings to tblBuildings.pkeyBuildings AND tblSpikes.properties to tblBuildings.properties .. If I remove the second link between properties the update adds the same spike to multiple buildings if the buildings have the same name. So If I have 10 buildings and two of those buildings have the same name, the query incorrectly updates 12 records. If I put the join between the buildings field back in, the query correctly updates 10 records.

Are joins created in a query any different than relationships created in database tools > relationships? I think the only difference is the join created in a query is only used in the query and the join created in the database relationships are global. Is this correct?

Thanks.

 

[Andrzejek]

>AND tblSpikes.properties to tblBuildings.properties
Wrong.
Your tblSpikes should not have any connection to tblProperties or anything else to do with Properties, including tblBuildings.properties

Looks like (most of) your tables should have a Primary Key field (which you have) and one Foreign Key field connecting it to a Parent table.

tblProperties (parent) and tblBuildings (child)
tblBuildings (parent) and tblSpikes (child)

---- Andy

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

 

[Andrzejek]

Your issues (IMO) are the result of not properly setting your tables and relationships between them.
You need to do that first, before you attempt to do any coding, queries, inserts, updates, etc.

I don't know what you have at this moment, but my guess is (or should I say: you should have):[tt]
tblProperties[blue]
PropertyID [/blue](Primary Key)
Name
... (other fields)

tblBuildings
BuildingID (Primary Key)[blue]
PropertyID[/blue]
Name
... (other fields)

tblUtilities
UtilityID (Primary Key)
Utility_Type[/tt]
[pre]

tblUtilities
UtilityID Utility_Type
1 Gas
2 Water
3 Electric[/pre]
[tt]
tblBldg_Utility
Bldg_UtilityID (Primary Key)
BuildingID
UtilityID
Account_No[/tt]

Based on:
>they can provide me with account numbers
and I assume you will have an account number per building per Utility (gas, water, electric)

> I think it will be a good idea to make a separate table with those and all of the water/electric/gas information

I would have:[tt]
tblSpikesGas
tblSpikesWater
tblSpikesElectric[/tt]


---- Andy

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

 

[JayRo71]

So how would one link the data in tblspikes to the correct building? There are duplicate building names and no indexes in the imported dataset. One has to consider the property name when linking spike to building. If I were to ask you to add the monthly water consumption to Building A, you could not do it without knowing either the numerical index for building A or the Property Name. Since the imported table only has the Property Name and not the index number, the property name has to be part of the link. I am sure you are correct, I just do not understand...

 

[Andrzejek]

Did you get the Account Numbers to be added to your data you get from outside?
If so, do you get Account Number per Utility per Building?

So:
Building A in Metro Apartments:
- Water Account Number is 123ABC
- Gas Account Number is XYZ987
- Electric Account Number is 23GH65
Building A in Main Street Apartments:
- Water Account Number is 65FG89
- Gas Account Number is BN87654RF
- Electric Account Number is oiu678TYh
Etc.


---- Andy

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