Best Way To Deal With Addresses Please.

Jun 10, 2005

Hi.
I have an Access 2000 database running on Windows 2000 Operating System. The database is on a shared drive on a network with approx 20 concurrent users. It runs a bit sluggishly so I am looking for ways to speed things up. The network guys say that the bit rate is OK so I need to tweak the database. I have run the performance and table analysers and get no suggestions for improvement. However, I have spotted a potential problem with the way addresses are stored.

Currently addresses are stored in the person table along with personal stuff like date of birth, gender, ethnicity etc. the address part has six fields:

Oh by the way - this is for the UK so the address structure has to be a bit flexible to accommodate quite a few variations of how addresses can be written but a reasonable guide is:

Number (or house name) and street name
(often a second line for part one of address)
Village / urban area
Town
County
Postcode

Part one of my question is - is this the best way to store this information or should I just gather it like this on a form and then store it as concatenated text in a single field.
I have tried this on a small test database and it works OK and prints address labels etc. - - But is it better or not?

Part two is - should the addresses be stored in the person table at all or should it be in a separate table. This is the bit I cannot get my head round. If it is in a separate table then each record in the table needs to be unique but because of the nature of the client base (i.e. students) many students share accommodation - OK a 1 to many relationship - but what about students in halls of residence they will have an individual room but the remainder of the address is the same. So for say 1000 students in the same hall 95% of the address is repeated although each address is unique overall. And with 4 halls there are 4000 entries that could potentially be reduced to 4 plus a room number and hall name, but I am not at all sure how to achieve this. And then what about addresses for students not in halls but shared houses? I cannot get my head round this. I can see why it was set up the way it was but there must be a better way.

Do I need one table, two tables or six tables (one for each field)?

And how do I then enter data? Do I have to have combo boxes on each line to see if that value has been entered before?

The data gathered would always be entered via forms so I can get the fields together but I don't know how many tables I need nor how best to relate them.


Can anyone suggest how I might get round this problem?

Thanks... Sprocket

View Replies


ADVERTISEMENT

How To Deal With Events Across More Than One Day

Oct 7, 2005

Right this is a fundamental issue for me here and I cant get my head around the problem!!!

The database I am working on is a database which records the training courses (run by the NHS) which staff at GP surgeries in our area have attended and acts as a booking system for them.

So we have the obvious tables - one for courses (including the name, cost, etc) and then one for course dates (linked by courseID) and one called training_record which contains who went to what when.

It seems simple but it has come to my attention that for example, "Course A" may in fact take place over 2 days, which could be a week apart. The surgeries are billed in one invoice, i.e. Course A would be £100 for 2 days.

How would you recommend I have this data? I can see all sorts of confusion arising... :(

The only way I have thus far thought would be to put each one in like:

course name: cost date
Course A: day 1 £100 - 29/10/2005
Course A: day 2 n/a - 6/10/2005
Course A: day 1 £100 - 01/11/2005
Course A: day 2 n/a - 08/11/2005

However this seems long winded as some courses may be a few days long and this means adding each person to multiple ones!!!!

Any assistance with getting my head around this would be ace! :)

View 1 Replies View Related

Normalization: How To Deal With A Field That Does Not Have Any Associated Data

Oct 31, 2005

Hello.

I have a simple modeling question. Lets say I have the following table structure:

Table 1
-------
Cust_id
Cust_name
Phone
City
Supplier_id
Dept

Table 2
-------
Supplier_id
Supplier_name
phone
City

Table 3
-------
Dept


Table 1 is M-to-1 with Table 3. Table 2 is 1-to-M with Table 1.

I have seen such as schema and do not understand why there needs to be a separate Table 3. Can someone explain why Table 1 alone would not suffice and Table 3 would not be required?

Basically, can someone explain the rationale for normalizing tables when there do not exist sufficient associated data for the field for which a new table is created (i.e. in this example, if Dept had a lot of other fileds associated with it such as Dept_desc, Dept_Mgr etc , then I can see the value in creating a different Dept table and joining it with Cust table through a Dept_id field).

A similar such design exists in the standard "Address Book" sample schema that ships with Access (the analog field in question there is Role).

Thank you for your response.

View 3 Replies View Related

Mail Merge: Best Way To Deal With Unknown Recipients?

May 13, 2007

I'm trying to create a database and it's going well accept for one facet. Some of my recipitents are not known by name. I am planning to use my database to issue letters, newsletters and the like but sometimes they will need to be addressed "Dear sir/madam" instead of "Dear Mr Smith" I was wondering to componsate for that in a database.

I've thought about using a "recipient" field, but I am not sure that would work too well as it would mean an extra field when it might not be needed (I can get "Dear Mr Smith" from fields such as Title and LastName).

I can't really get my head around it :(

One thing: Please don't think of me as some annoying mail-spam person. In truth I am a member of a charity but some of the businesses and charitable foundations we appeal to don't have named members for us to address letters to. Hence we need to use Dear Sir/Madam and the like.

:)

Please help me. I've hit my head on the wall so many times that I think I am going to demolish it!!

View 1 Replies View Related

Best Advice To Deal With Input Of Data Into Access?

Mar 6, 2008

Not sure if anyone can help but I have an issue I would love to sort out.

Each week I load several excel spreadsheets into an access database (one table) in order that I can check for duplicates across previous weeks and that week (with in excess of 20,000 records on each excel sheet). I created a find duplicate query to identify the records so I can use it to obtain credits. Unfortuantely I am not in control of the data coming to me (or else I would prevent duplicates at source)

Im not sure if this is the best way to try and do this or not. Any comments are greatly appreciated.

View 3 Replies View Related

Tables :: How To Deal With Exception To Related Tables

Aug 27, 2013

I am having an issue where a small percentage of my records do not follow my related Tables.

ex: 3 tables
tblEmployee
-intEmployeeID
-intWorkCenterID

tblWorkcenter
-intWorkCenterID

tblManager
-intManagerID
-intEmployeeID
-intWorkCenterID

where:
tblemployee.intworkcenterID -- tblworkcenter.intWorkcenterID -- tblManager.intWorkCenterID

Basically I have my general employee information in the tblemployee table, the workcenter information in the tblworkcenter, and the manager of the Workcenter in the tblManager. This is correct for 99% of the employee population. however, there are a few employees whose manager isn't the 'general' manager for the employees assigned workcenter.

How I can adjust an individual employees manager, while maintaining my structure? or would i have to go back and add the managers ID to a field in the tblEmployee Table?

View 7 Replies View Related

Use Of IP Addresses In DB

Sep 6, 2005

we want to run a ping command via a radio button or command button. We have IP addresses set up for multiple pieces of equipment, each IP address consisting of 4 different text boxes. We want to be able to launch a ping and have the program read the values from each of the 4 text boxes that make up the IP address.

Any ideas/suggestions greatly appreciated.

View 14 Replies View Related

Sorting Addresses

Feb 28, 2007

How can I sort string addresses so as to get the correct number order? I know that strings sort on each character, which causes the problem. I have tried some things (with VAL and Len), but was not completely successful. I do not want to enter an address number as 0630, when I want 630.

Why does this not work:IIf(Len(Str(Val([address]))=3),"0" & [address],[address])? Address has 3 and 4 numbers only.

View 2 Replies View Related

Storing Addresses

Apr 11, 2005

I am currently designing a contacts database for my business and need some advice on how to store addresses.

As I see it, I can either use a single field and carriage returns, or multiple fields which for some records may leave some blank fields.

The data will be used to perform mailmerges and will also be output to a contacts directory and I would be looking to display the address both horizontally and vertically. Is there a simple way of manipulating a multi line field so that it can be displayed on a form across the page on one line in a report?

View 2 Replies View Related

Filter IP Addresses

Sep 13, 2005

Hi,

I have a field in one of my tables that contains IP Addresses. These addresses can look something like 142.67.214.73/24 or 192.168.196.0. I would like create a calculated field so that ALL IP addresses just show the first 3 octet's. So the first one would just show 142.67.214 and the second one just 192.168.196

Any help would be great .......

Thanks !

View 2 Replies View Related

Email Addresses

Jun 12, 2006

Simple query from an amateur. I have a list of people in a table, each with thier email address. How can I convert this into a list of addresses to paste into a group email? (Ideally with ";" as a separator between addresses)

View 4 Replies View Related

Query With Addresses

Feb 6, 2008

Hello all I am using MS access for a little personal project and I have house number and streets along with a day they are serviced.
example:

unit road type day
1 main st friday
2 main st friday
3 main st friday
4 main st friday
5 main st friday
6 main st friday
7 main st friday
13 turtle st monday
15 turtle st monday
16 turtle st monday

I want to make a report of this data to look like this

unit road type day

1-7 main st friday
13-16 tutle st monday


I figure I have to do some sort of query before I can make the report im jsut not sure how to seperate it or what symbols to use. I know this is faily simple I just can not get the computer to give me what I want....


ok I think i figured it out what I did was take unit and I put it in 2 times one fo min and one for max and then I grouped the road by ascending and it worked out

View 1 Replies View Related

Choosing Addresses

Sep 28, 2007

Hello everyone. I hope you are well. I am new to this forum and need some help with access.

I have a database which records an organisations addresses. There is the main address, where the activity takes place, and then there is the postal address, which is often different. What I want to be able to do is to select all the organisations in one town (e.g. Town1), but send information in the post to their postal address (Town2).

Is this something that can easily be done? I hope somebody can help me (especially with some user friendly terms).

Many thanks,

Chadder

View 10 Replies View Related

All Email Addresses In The To: Field

Dec 14, 2006

Please please please!

It's driving me crazy!

Is it possible to send e-mails to all of your e-mail addresses in one single e-mail?

Right now I am able to open up an individual e-mails for each of my stored e-mailo adresses but it would be so much better (and more user friendly) if I could just have one e-mail with all addresses in the To: field.

Any help would be really appreciated.

Rob

View 3 Replies View Related

Number Format For IP Addresses

Feb 20, 2007

I need some input on what to use for a field that contains a range of IP addresses, and have it sort numerically by octet. I currently have that field assigned as text, but were not happy with the sort order. I've played a little with the number format type, but still did not get the correct sort order. Any suggestions would be appreciated. Thanks

View 2 Replies View Related

Formatting Numbers As Addresses

Mar 24, 2008

My database is a maintenance record of our building complex. The unit/building number is the key field. The problem I am having is having access recognizing the unit numbers and building numbers, not as whole numbers.
Example: I-380, 10-380, 2-390, 13-370, and so on. When sorted, access treats them as whole numbers. I have tried two separate fields but it would be nice to have only one field and be sorted by building numbers.
I am not a coder so be gentle.
Can you help?

View 9 Replies View Related

Validating Email Addresses

Mar 25, 2008

Hi
I have a large file where I am trying to clean up the data.
One of the fields is the email address.
I want to check if the email address has something followed by an @ symbol followed by something else. If it does not, then I want to replace it with a null value.
Can you help?
I thought that I could use the wildcard features such as <>*@* but this does not appear to be working.
Noel

View 6 Replies View Related

Linking Names And Addresses

Apr 6, 2005

This might be a basic Access application, but I'm having trouble. I want to create a name and address table that will include all patients in my clinic, identified by unique patient ID numbers. I then want to create 3 other tables for the information collected at the 3 appointments I will have with each of these patients. I've created 4 forms, 1 for each table; each a control for patient ID, name and address. How do I create a link so that I can enter the patient ID number in the appointment form and the name and address will appear automatically in my form? Is this possible? Thanks for your help on this.
-Lorna

View 5 Replies View Related

Using A Query To Extract Email Addresses

Aug 17, 2005

Hi there,

I am a relative newcomer to Access, and am trying to work out if there is a way to isolate email addresses in a line of text, and delete all the text around them.

I have basically got a column which contains blocks of text and I want to extract the email addresses that are embedded within them. Does anyone know if this is possible by running queries?

Thank you in advance for any help! :confused:

Dan

View 5 Replies View Related

Combining Fields To Filter For Same Addresses

Sep 17, 2005

Hello
I have a political database with a separate field for street number, street name and apartment number. I've been asked to prepare a query that will only list the first member of a household. Example, the query would only show one member from a two person household if they both have the same address. I figured that I would somehow prepare a query that combined these 3 fields in a expression and then display the total row and choose FIRST. That didn't work, perhaps my syntax was off. Here's what I tried:

Expression: (Trim(StrConv([AddressStreetNumber]&" "&[AddressAptNumber]&" "[AddressStreetName],3)

Then I set the Total line to FIRST

Is it my syntax or is it that I can't join separate fields together like you can in a report ?

View 2 Replies View Related

Allow Users To Enter Hyperlink Addresses

Aug 30, 2006

I have a database set with user level security (via the wizard) and I want to give "Full Data
Users" the ability to enter hyperlink addresses. Currently only I can do
this as I am set to "Full Permissions / Admin"

The associated table is set to accept hyperlinks, and the relevant text
boxes are in place and unlocked on the forms, however when logged in as a Full Data User, you currently can not set or add a hyperlink address, only follow the hyperlink path's that i've already put in.

It's driving me nuts.... help please!

View 2 Replies View Related

Updating 4-digit Zips For My Addresses

Nov 22, 2004

Hello,

I have a database in which I want to automatically update all the addresses
which have United States Zip Codes to contain the 4-digit zip which can be located at
www.usps.com.

I am a novice at setting up dbases, but seem to be able to get around when trying to figure
something out. Yet this one has stumped me. Could anyone help me update my data
automatically without having to do the tedious copy and paste of each record into the usps.com
website to get the correct address information.

Thank you,
tommyatoes <><

View 1 Replies View Related

Multiple Email Addresses In SendObject

Jun 13, 2005

Im using the code below to send information by email. BUT:

I want to send the same information to more than one recipient. can i write more than one address in the SendObject?

What can i do if i do not know prior to sending how many addresses i want to include. (this means that once i might have tosend it to 1, then later to 3, etc...)?

The code is:

Private Sub cmdMailTicket_Click()
On Error GoTo Err_cmdMailTicket_Click

Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim stText As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stTicketID As String '-- The ticket ID from form
Dim stWho As String '-- Reference to tblUsers
Dim stHelpDesk As String '-- Person who assigned ticket
Dim strSQL As String '-- Create SQL update statement
Dim errLoop As Error

'-- Combo of names to assign ticket to
stWho = Me.cboAssignee
stWhere = "tblUsers.strUserID = " & "'" & stWho & "'"
'-- Looks up email address from TblUsers
varTo = DLookup("[strEMail]", "tblUsers", stWhere)

stSubject = ":: New Help Desk Ticket ::"

stTicketID = Format(Me.txtTicketID, "00000")
RecDate = Me.txtDateReceived
'-- Helpdesk employee who assigns ticket
strHelpDesk = Me.cboReceivedBy.Column(1)


stText = "You have been assigned a new ticket." & Chr$(13) & Chr$(13) & _
"Ticket number: " & stTicketID & Chr$(13) & _
"This ticket has been assigned to you by: " & strHelpDesk & Chr$(13) & _
"Received Date: " & RecDate & Chr$(13) & Chr$(13) & _
"This is an automated message. Please do not respond to this e-mail."

'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1

'Set the update statement to disable command button
'once e-mail is sent
strSQL = "UPDATE tblHelpDeskTickets SET tblHelpDeskTickets.ysnTicketAssigned = -1 " & _
"Where tblHelpDeskTickets.lngTicketID = " & Me.txtTicketID & ";"


On Error GoTo Err_Execute
CurrentDb.Execute strSQL, dbFailOnError
On Error GoTo 0

'Requery checkbox to show checked
'after update statement has ran
'and disable send mail command button
Me.chkTicketAssigned.Requery
Me.chkTicketAssigned.SetFocus
Me.cmdMailTicket.Enabled = False

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next


Exit_cmdMailTicket_Click:
Exit Sub

Err_cmdMailTicket_Click:
MsgBox Err.Description
Resume Exit_cmdMailTicket_Click

End Sub

View 3 Replies View Related

Forms :: Calculate Distance Between Two Addresses

May 3, 2013

I am using Access 2010 and am developing a form where the user provides a full address - street number, street name, city, province and postal code.

I want a control to calculate the distance from this address and a fixed address.

View 2 Replies View Related

Tables :: Change Hyperlink Addresses?

Nov 11, 2013

I have a massive database...100,000 records (well, it seems massive to me right now!).

A part of each record is a hyperlink to a PDF document on the server. I didn't map a drive letter for the path, as everything I've researched has said to not use drive letters but the actual server name and the path.

I did that. Unfortunately, they had an issue with the server a couple of weeks ago...and in mapping it again, they added another layer of folders on the way to the location of these PDFs.

Now I have to go in and edit each individual hyperlink to reflect the new path.

1. Is there a faster way to do this? They want their first report half an hour ago.

2. They have a whole new set of these PDFs to be attached to the records that don't have them yet. Is there a faster way to do it than to have to open each record and put in the path to the document?

View 1 Replies View Related

Modules & VBA :: Consolidate Addresses By Similarities?

Apr 9, 2015

I need to consolidate mailing addresses so that many records are grouped to one invoice based on the address they fall under. I am currently using an update query to change addresses that are similar to match exactly. Is there a way to make it so the addresses group together based on similarities rather than exact matches? For example I have two addresses:

1301 W Johnson Street Phoenix, Arizona 85725
1301 West Johnson St Phoenix, AZ 85725-7201

These are the same property however without cleaning them up manually to match, two invoices will be printed. Is there a way to consolidate these?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved