Attempting to make Tech Support Data Base and have a few questions

[wals9331]

I am a newbie here so i dont know all the cool names and phrases for everything so be gentle with me .

My boss wants me to make it so the address of a company automatically pops up in the database, so if they call multiple times, the user doesnt have to keep putting the address in. If the company hasnt called before, the user would have to fill out a company form that pops up if the companys name isnt already in the database. Any info on how to do this would be EXTREMELY helpful.

I also need to know how to make access send you an email if certain information hasn't been finalized after a certain period of time.

Thanks for your help guys. Ive been stuck on these problems for A WHILE to say the least...

 

[ScottGem]

You need to design your database properly first. You need several tables here, but its not clear from your explanation what. You will need a companies table as well as a calls table. You may need a contacts table along with a staff table and maybe some lookups.

I would suggest looking at the Northwinds sample database to see some of the techniques you are asking about. But the key here is using primary and foreign keys. Each table should have a primary key (I recommend using an autonumber field as the PK). Each related table is related by adding a Foreign Key field that holds the value of the related record's PK. Lets use a simplified example. In your Call table you might have a FK field for CompanyID. So what the user would do is pick the company from a combobox and the CompanyID would be stored in the FK field. You can then use a few different options to DISPLAY the address info. I would recommend using a subform.

Notice I emphasized DISPLAY. This is because you should NOT have any info about the company other then the CompanyID in the call table. To do so would violate Normalization rules. You might want to do some research on normalization before you design any tables.

I also said this was a simplified example. In your situation you may have companies that have multiple addresses. In which case you might need to a CompanyID FK AND an AddressID FK.

As for e-mailing a notice, you would have to create a query that returns the records past the time frame you specified. Then use the SendObject method to send e-mails for those records.

 

[wals9331]

You need to design your database properly first. You need several tables here, but its not clear from your explanation what. You will need a companies table as well as a calls table. You may need a contacts table along with a staff table and maybe some lookups.

I would suggest looking at the Northwinds sample database to see some of the techniques you are asking about. But the key here is using primary and foreign keys. Each table should have a primary key (I recommend using an autonumber field as the PK). Each related table is related by adding a Foreign Key field that holds the value of the related record's PK. Lets use a simplified example. In your Call table you might have a FK field for CompanyID. So what the user would do is pick the company from a combobox and the CompanyID would be stored in the FK field. You can then use a few different options to DISPLAY the address info. I would recommend using a subform.

Notice I emphasized DISPLAY. This is because you should NOT have any info about the company other then the CompanyID in the call table. To do so would violate Normalization rules. You might want to do some research on normalization before you design any tables.

I also said this was a simplified example. In your situation you may have companies that have multiple addresses. In which case you might need to a CompanyID FK AND an AddressID FK.

As for e-mailing a notice, you would have to create a query that returns the records past the time frame you specified. Then use the SendObject method to send e-mails for those records.

The info that i would require in the companys address field would be:address, city, state, zip. Where is this Northwinds database that you are talking about? I would like to check it out....

Any examples of what this emailing querey would look like? Could i make it display just the actual number of the entry in the email, or would it physically be linked to the database?

Thanks for the help!

 

[LarsUSN]

Wals,

I am pretty much doing the exact same thing and trying to figure it out from my newbie status as well. I found a form that is along the lines or what we are looking to do, but the solution didn't work for me. Maybe you can figure it out and better relay how you did it in both forums

http://www.access-programmers.co.uk/forums/showthread.php?t=89722&page=1&pp=15

Respectfully,
Lars

 

[wals9331]

Wals,

I am pretty much doing the exact same thing and trying to figure it out from my newbie status as well. I found a form that is along the lines or what we are looking to do, but the solution didn't work for me. Maybe you can figure it out and better relay how you did it in both forums

http://www.access-programmers.co.uk/forums/showthread.php?t=89722&page=1&pp=15

Respectfully,
Lars

I knew i couldnt be the only one trying to do this! Ill check it out, and see what i can do with it. WARNING - not only am i a newbie member, i am also a newbie access guy

 

[ScottGem]

The info that i would require in the companys address field would be:address, city, state, zip. Where is this Northwinds database that you are talking about? I would like to check it out....

Any examples of what this emailing querey would look like? Could i make it display just the actual number of the entry in the email, or would it physically be linked to the database?

Thanks for the help!

The Northwinds sample is installed with Office. Just do a search on northwinds.mdb to find it.

There would not be a "company address field". Again, you need to give us more info about your data. Do companies have multiple addresses? Can a company share an address with another company? Such info dictates how the relationships are created.

As for the SendObject, Access Help should give an example of how to use it.

 

[ScottGem]

Wals,

I am pretty much doing the exact same thing and trying to figure it out from my newbie status as well. I found a form that is along the lines or what we are looking to do, but the solution didn't work for me. Maybe you can figure it out and better relay how you did it in both forums
Respectfully,
Lars

Lars,
I took a look at the sample you posted and you need to do some redesign of your tables. I don't understand why you have duplicate records for each state or what all the fields are in the trouble table.

 

[LarsUSN]

Lars,
I took a look at the sample you posted and you need to do some redesign of your tables. I don't understand why you have duplicate records for each state or what all the fields are in the trouble table.

Scott

Each state has 2 completely differnt contacts(sites), one being a govorner's office the other being a differnt office, no way related; that's why the 2 of each state.

In the trouble table I have fields for: What the porblem is, another for how it was resolved, and a combo box which I want to propogate the selected sites main point of contact (from the contacts table)'s contact information to the trouble table (and form) so that the trouble table has an accurate and complete set of contact information.

I'm trying to get information from a table to show on a form for another table. I'm not sure if that requires queries, macro's, Visual Basic code, relationships or what - that's what i'm here trying to find out

 

[ScottGem]

Scott

Each state has 2 completely differnt contacts(sites), one being a govorner's office the other being a differnt office, no way related; that's why the 2 of each state.

In the trouble table I have fields for: What the porblem is, another for how it was resolved, and a combo box which I want to propogate the selected sites main point of contact (from the contacts table)'s contact information to the trouble table (and form) so that the trouble table has an accurate and complete set of contact information.

I'm trying to get information from a table to show on a form for another table. I'm not sure if that requires queries, macro's, Visual Basic code, relationships or what - that's what i'm here trying to find out

Then you should have a separate State table, with StateID a Foreign key.

The key issue here is that you want to DISPLAY the address and other info only. You do NOT want to store anything other then the ContactID in the other tables.

There are three ways to display data from another table; subforms, the Column property and DLookups. if the number of fields to display is 3 or more I would use a subform. If the number of fields is 3 or less I would use the Column property. To do so means adding the fields to the query behind the dropdown, then using UNBOUND controls and setting their datasource to the columns, Ex:

=[cboContact].Column(2)

Note column count starts with 0. Column 0 would the ContactID, column 1 the name so column 2 would be the next piece of info etc.

If there are only 1 or 2 fields or they come from different tables, then I would use DLookups. Again you would use Unbound controls and set the datasource to a DLookup expression.