Solved New to vba code

[MajP]

Answer 1 and 2....Each building has a PropertyID number and each building has many graphics. I used the building abbreviation as the PK as none are ever the same and it is easier to use the abbreviation.

Using the abbreviation is fine if it follows the rules (unique, never changing, and IMO short and simple)
I would be clear in the naming convention, because if you have a PK and it is not called ID and another field called ID it will be super confusing. I would use some form of ID in the abbreviation name bldgAbbr_ID, and take ID out of property, PropertyNumber.

Answer 3 and 4....A graphic has many BasPoints which is the label on the graphic (some for equip. and some are just software points). Each BasPoint has only one note which can be for a software point or a piece of equipment.

I am not sure on this. But I would think if notes are related to bas points and you can only have a single note then the note field should be in the bas point table. A bas point is related to a piece of equipment so I think you should have a fk to an equipmentID in tblbaspoint and remove the noteID from the equipment table. Is there also a software table for software points.
[qupte]
Answer 5....Yes the BasPoint could be on two or more graphics in the same building. I've just handled this by linking multiple graphics to one record to add or delete note. Then I create a new GraphicID with all the graphics that the record is on (eg. Steam/DHW). I did this so you can see all the graphics it is on with the one record on the form. Probably could of done something better here.
[/quote]
It should be done with a junction table
tblGraphicsBasPoint
---GraphicID_FK
---BasPointID_FK

However, if you have not worked with junction tables building an interface is a little different
example would be
1 A
1 B
1 C
2 B

In this case bas points A,B,C are on Grapic1 and bas Point B is also on graphic 2.

Answer 6....The equipment could have up to three asset tags one for the equip., starter, air handling unit (some could have none like a software point). Each Asset Tag has separate fields because you want to see them all when you bring up the form with one record.

If this is as you describe then I do not see the reason for the asset tag table unless there is a need to make seperate asset tag records. These are properties of a piece of equipment and could be in the equipment table.

 

[HelpNewtoAccess]

How does this look now? My thinking was to make the tables a little smaller but I rather make things more efficient and correct. My thinking is that pictures should almost be in the equipment table as well. The software points are the same as the equipment points so I can use one search form. Thanks

 

[MajP]

If a piece of equipment can only ever have one picture then get rid of that table and move those fields into the equipment table. You are not gaining anything splitting it out and making more tables.
However, if you want the flexibility to store multiple pictures for a single piece of equipment then keep the picture table. You do need to add
EquipmentID_FK to the picture table, and do not link PK to PK as you have it. In this configuration you can add a single photo if you want, but you have the ability to add multiple photos for the piece of equipment.

There are few times to do a true one to one (PK to PK), but not often. Lets assume you have a patient table and some of the information is private and limited to certain data systems. You split the table so that protected information is in another table. However both tables would have a PatientID as the PK.

So based on that discussion can tblBasPoint be combined with tblEquipment? Is a bas point simply defining characteristics of a piece of equipment.
I do not think so because not every basPoint is a piece of equipment, some are simply software. So a one to one does not make sense here anyways. I think tblBasPoint should have an EquipID_FK and related that way.

 

[HelpNewtoAccess]

I adjusted the things you suggested but got to thinking that a picture could be the same for more then one piece of equipment if I had a picture of a group of fans. I did a junction just in case I need multiple pictures as well for a piece of equip. I've also added three more tables that I needed and think they would work like I have them. The tblBasLink might need some explanation. It is a link to web pages and one web page could be used for multiple buildings. Thanks for all this help I've never had things explained so well.

 

[MajP]

Some of this is not hard and fast. You may need to think about the downstream effects. Proper normalization can be an art as much as science.

I adjusted the things you suggested but got to thinking that a picture could be the same for more then one piece of equipment if I had a picture of a group of fans. I did a junction just in case I need multiple pictures as well for a piece of equip

Making a table design that is super flexible, may make it harder to build the application. So you have to weigh the outcomes.
For example using a junction table will make building the forms and queries more challenging. So if you have 1000 pictures but you only have a few cases where you need to assign the same picture to different pieces of equipment (many to many), then the bang may not be worth the buck of using a junction table. In this case you could just use a 1 to many relationship and add the image twice as a unique record.

Similarly Asset Tags. You can have up to three asset tags, but you are not tracking other information about asset tags. If you said you had from 1 to 5 then I would say you are better off making a separate record for each in its own table. If you thought that assets tags might in the future have other meta data to track (tagDate, issuedby, ....) then you should make another table now.

The tblBasLink might need some explanation. It is a link to web pages and one web page could be used for multiple buildings.

So it is a one to many where many buildings could reference the same link, but a building only has one link. If a building can have multiple links then you may need another junction. However you show Address1 to Address4. Normally that sends up alarms as non-normalized data. Again this goes to weighing the effects. It looks to me that a building can be related to a group of links from 1 to N. So without knowing how you use this you may need

TblBasLink
BasAlarmsID 'PK
Group ' I assume this is the name of the group of links

tblLinks
linkID
linkAddress
linkDescription
.... other fields about a link
BasAlarmsID_FK ' links back to tblBasLink

Now a building can "link" to a group of web links and the group relates to multiple records.

 

[MajP]

Besides that I think it makes sense to me.
One more thing. I am not a fan of composite keys (two or more keys in conjunction defining the primary key). In my junction tables i will add an autonumber key although it can be done using the two foreign keys to make a composite PK

tblPicturesEquipment
  Picture_Equipment_ID  'PK
  PictureID_FK
  EquipID_FK

I will make a Composite index (may need to google). So the same combination of the two foreign keys can not be duplicated.
1 A 'picture 1 related to equipment A
1 B
2 A
2 B

but will not allow me to enter a duplicated
1 A

Rarely do you ev

 

[HelpNewtoAccess]

For the tblBasLink table it has 6 records with three web pages on a single tab control form (one record could be same for 20 plus buildings). I also have a web address in the graphic table to bring up web pages for each specific graphic.

I took your advice on the pictures table just made it one to many from the equipment just in case more then one picture is needed.

Asset Tags...The only thing is I want one record to show all the Asset Tags on a form in separate fields. I didn't even think about (tag date/issued by) good catch but I'll only be dealing with end result tag number. If I was to change my mind it would be something like you see in the image tied back to the equip table right?

I did a composite PK like you suggested for the tblGraphicBasPoint.

One other question I have is it possible to overlay command buttons over a web page in access if you tie the web page to a form?

 

[MajP]

I did a composite PK like you suggested for the tblGraphicBasPoint.

To be clear I suggested not to use a composite key and use an autonumber instead. A composite key is where you have multiple fields to make up the key. For example you could use FirstName and LastName. So the combination of first and last is unique. Then in the related table you would have FirstName_FK and LastName_FK and link to both fields. My suggestion was not to use a composite key because you can see how this becomes more complicated. I suggested to simply make an artificial, autonumber key. Some people like composite keys because they tend to be natural keys (keys with meaning like first and last name). I never use them, but the is my preference.

 

[pbaldy]

The guy who taught me Access 20+ years ago loved composite keys, so many of my older apps still have them. He was an Oracle DBA so theoretically knew his stuff. In time I've come to see them as a mistake, or at least more trouble than they're worth, and would agree with MajP's recommendation against. Think of it this way: sooner or later Mary Jones will get married and become Mary Smith and your key field is changing. Plus sooner or later you're going to have more than one John Doe. We've got a couple of thousand drivers, and a number of duplicate names.

 

[HelpNewtoAccess]

Yes sorry you did say Autonumber key I screwed up.

 

[HelpNewtoAccess]

Hi MajP,
I think I might have my table structure somewhat correct now. I thought about a few things you said and adjusted accordingly. Can you give opinion on what you think? I was thinking about taking out the AhuAssetTag because it will have quite a few records with the same number but a large number with nothing. I don't know if it would be worth it unless it had tagDate/IssuedBy like you said. Thoughts....

 

[MajP]

TblLinks may be OK if this is a many to many where a many Graphics are related to a set Group of links, but links within a group are not also in another group. If that is the case, I believe it makes sense. If links are in more than one group, it would be different. If a group of links relates to a single graphic then you would get rid of table link and only have tblOtherLinks.

I think it makes sense. I would change the very generic name of table Attachments and Documents. I would make them more descriptive so there is some understanding of what goes in those. What type of information goes in table attachments? Are those Building Specifications, designs, grapics? What is the difference from what goes in table documents.

Before you start spending a lot of time on forms. You should put some dummy records in the tables to see if it makes sense. Do some simple queries to see if you can put the data together. One way to see if you made a mistake in design is that the queries are not update able. So can enter data into a single table but not in a query linking them together.

 

[HelpNewtoAccess]

tblLinks
So what I was thinking is in tblLinks …. 5 groups with 5 different graphics in each group..(Basically to scroll on a form)
…………………………………………………………... 2 groups with 3 different webpages from tblOtherLInks (Alarms/misc links)

I did a query and the only way it works the way I have it is if I just do (tblLinks to tblOtherLinks) or (tblLinks to tblGraphics) to see the records. I think this should be okay but if I wanted to see all the records together in a query what would you do?

I would change the very generic name of table Attachments and Documents....
I changed the tblDocuments to tblProceduresInfo. This is basically binders having a wide range of information on misc. stuff like what to do when a window is broken, procedures for emergencies, how to complete a task, Conversion tables etc. This has mostly been done by someone else on another database but he didn't use table relationships at all. I was thinking about redoing it but too much to do with what I've got already.

The attachment table is any attachment that is directly related to a building (eg. Renovation document with numbers of contractors doing the work). So I'm thinking maybe calling this tblBuildingAttachments to help be more specific.

You should put some dummy records in the tables to see if it makes sense. Do some simple queries to see if you can put the data together....
Yes good idea tried a couple but definitely need to do some more testing. Thanks again for all the help!

 

[MajP]

tblLinks
So what I was thinking is in tblLinks …. 5 groups with 5 different graphics in each group..(Basically to scroll on a form)
…………………………………………………………... 2 groups with 3 different webpages from tblOtherLInks (Alarms/misc links)

I did a query and the only way it works the way I have it is if I just do (tblLinks to tblOtherLinks) or (tblLinks to tblGraphics) to see the records. I think this should be okay but if I wanted to see all the records together in a query what would you do?

I still do not really understand this. Maybe a simple example could help. My guess is this could be done differently based on what you said.

 

[HelpNewtoAccess]

Ok I quickly put together an example db. So basically what I may need in the future is have the query show data from both groups in tblLinks so I can see all the web pages.

Another off topic question is it possible to have a control button in access go on a web image just curious?

 

[MajP]

You just need to use a UNION query

SELECT * from qryGraphics
UNION Select * from qryOtherLinks;

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

For your other question, I really do not work with web pages so you may want a seperate thread.

 

[HelpNewtoAccess]

Thanks for all your help really appreciate it!