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 Replies


ADVERTISEMENT

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

Advice Required With Access Data Pages

Jan 29, 2006

Hi,
I'm currently putting together a database for a medical NGO in Cambodia (http://www.medicorps.com/updates/cambodia.html) and am looking for some advice. The simple database is for logging client referral cases by Cambodian doctors to a team of international doctors. I haven't used access in 10 months and despite programming in access for 5 years progress is very slow. At the moment I'm designing the input and search forms. I was thinking that a more logical approach would be to convert the forms to access data pages and put the database online. I haven't used data access pages but from what i know their fairly limited?
The goal would be the ability to log/search the data with auto updated pull downlist based on the actual data. Ultimately I want the data compiled and emailed to a email list from withing the website. The trouble is I have no idea how to do it.

Thanks in advance for your help.

View 5 Replies View Related

General :: Allow Colleagues To Input Data To Access Database?

Aug 6, 2013

How do I allow colleagues to input data to the Access database, but at the same time prevent meddling with the database design? Is it possible to password protect some functions but allow others (students/ nurses!) to input data?

View 3 Replies View Related

Switchboard Front Page - Users Able To Access Forms For Data Input

Dec 8, 2011

I have created a switchboard 'front page' through which I hope for users to access the forms for inputting data.

Within this front page I have an 'enter' button - successfully created - everything.

However, I am unclear as to how you activate this, or any other button. I have linked it to the appropriate form and stated the action I want it to undertake, but nothing is happening.

What further instructions do I need to provide ...and where?!

View 5 Replies View Related

Problem Entering External Data From Excel Into Access Field Using Input Mask

Mar 5, 2008

Hi,

I'm new to this. I'm trying to enter data (it's actually Latitude and Longitude co-ordinates) from an existing Excel source into an Access database which has input masks of 00°00'00.00"L;0;0 (Latitude) and 000°00'00.00"L;0;0 (Longitude) in the respective fields. However I cannot get the information to import or display correctly. I did an "export data" of the respective table (hence fields) to Excel to try and get the correct entry format. An example of the Lat exported was 24°49'41.81"N and Long was 067°01'44.02"E (but with a very small ' in front but only visible in the data entry line in Excel, not in the actual spreadsheet table???)

However when I try to enter the data (even using the exact same little degree symbol, apostrophe, and quotation marks) it does not enter the access fields correctly. On closer scrutiny of the exported Excel format I note a small ' at the very beginning of the 24°49'41.81"N or 067°01'44.02"E string. But as I said previously only visible in the data entry line next to the formula button. Not on the spreadsheet cell.

However even when I "Paste Special" "values only" my new co-ordinates into the same entry location as one exported, it will still not import, or display correctly. If I go into the Access database directly there is a form where if I need to enter the new co-ordinates (using lat example above) I only have to enter 24 49 41 81 N (spaces between) and it will show correctly as 24°49'41.81"N

I'm getting desparate as I don't want to have to change all the details manually. Anyone know what my correct format from an Excel spreadsheet should be?

Apologies for lengthy story! Difficult to describe problem with degree symbols etc

View 2 Replies View Related

Forms :: Input Data Into Multiple Tables Using Single Form - MS Access 2013

Jan 15, 2015

I have 5 tables that I would like to input data in. It can only be done with a single form. The fields I want to input in have the same names in all 5 tables, for example:

Table 1:
Name
Age
DOB

Table 2:
Name
Age
DOB

Table 3:
Name
Age
DOB

Table 4:
Name
Age
DOB

Table 5:
Name
Age
DOB

Is it possible to input data into all of these fields in each table using one textbox for each field?

Preferably without having to use code but if it cannot be done without it then that would be fine.

View 7 Replies View Related

Forms :: Changing Text Box To Allow Fixed Data Input Options - Access 2010

Jun 17, 2014

I have created a member data base form in Access2010.

That has been populated with 366 member records

I have two text box fields that are currently free type fields, Member Type & Member Status.

What is the best way I can change these to show only the relevant options:

i.e. Member Type ( Full, Family, Pensioner, Life. )
Member status ( Current, Lapsed, Deceased)

I have tried to right click the field but the "change to" option is greyed out & not available.

I have also tried to insert a combo box or list box to show these options. The "use control wizard" option has been highlighted but when I drag either a combo box or list box into the form. the wizard does not activate.

View 14 Replies View Related

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 4 Replies View Related

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

Data Modeling Advice

Sep 21, 2005

Hi there, long-time lurker, first-time poster.

I'm working on an Access project at work, and I was hoping I could get some pointers/tips on my data modeling. Basically, attorney information is being kept on an Excel spreadsheet, and I ported it over to an Access database. About half of the firms have a contact attorney, the other half doesn't, but regardless of attorneys there is still data for each firm.

In many cases, one firm can have multiple locations across the United States. Also, there are unique records that pertain to a firm that are spread out across each of their locations, and there is also information that is unique to each individual location within the same firm entity.

Here are my tables:

tblGlobalFirmAttributes
FirmID
Firm
FirmURL
TINNumber
CurrentFeeAgreement
FirmSpecialty
FirmStatus

tblFirmLocationAttributes
FirmID
LocID
Address1
Address2
City
State
Zip
Fax
Phone

tblAttorneyAttributes
AttorneyKey
LocID
Title
FirstName
MiddleName
LastName
Surname
AttorneyStatus
AttorneySpecialty
AttorneyRate
AttorneyPreviousRate
EmailAddress
MobileNumber
AttorneyNotes

My thought process is the following. Each attorney has a location ID, pertaining to a location in tblFirmLocationAttributes (LocID). There is a FirmID key in tblFirmLocationAttributes that connects a location to the firm it belongs to, which is done via the FirmID key in tblGlobalFirmAttributes. Many attorneys can work in the same location, and there can be many locations that pertain to the same firm, and I set up my relationships accordingly.

Attached is a picture showing my relationships. If you guys can offer some tips and/or advice, it would be greatly appreciated! This is my first time using Access extensively, so i'm all ears for suggestions/constructive criticism. Thanks!

View 4 Replies View Related

Data Entry/Import - Advice?

Apr 26, 2006

Hi There,

I'm building a database for a friend's company.

My query is really to do with the best way to add new records to their database.

The database has four tables:

[Species] which is linked to multiple loci in the [Locus] table. For each Locus their are multiple alleles in the [Allele] table. Additionally, there is the [Reference] table, each reference can be linked to one or more loci in [Locus].

Users could add a new species, it's loci, the alleles for each locus and select the reference for each locus from a list (or add a new reference) using the Form used to view the data. However, this is very time-consuming when there are a large number of species to add but is easiest for me, as it requires little or no extra coding.

The other way I was thinking, would be to do a kind of batch update from an excel file. This would suit the users better as their data are already in this format.

The problem is that I guess I can't do a simple import spreadsheet due to the one-to-many relationships, as there would be no primary/foriegn keys in the excel sheet.

Would the best way around this be to add the first species, then for this species add the first locus and its alleles, then the next locus and so on.. then the next species? This way I could use the keys as they are generated..

Alternatively, I could get excel to generate the keys, and query the Access database to make sure it is not generating keys already in use. Then I can do a more simple import procedure...

I can do either using VB. Which do you think would be 'best'? Or should I just tell the users they'll have to enter stuff by hand the long way?!

Cheers

View 8 Replies View Related

Data Entry Advice Needed

Jan 5, 2006

Hi. I am developing a db for juvenile salmon-focussed fishery survey data and have encountered something of a conundrum which I could use some advice on. Apologies in advance for the length of the post.

Background
Juvenile salmon move from freshwater to saltwater. During this transition they require time to adapt physiologically and are thought to seek out nearshore areas with intermediate salinities, or with freshwater overlaying the saltwater. They also experience problems with elevated temperatures.

We are interested in tracking salinity and temperature information at each site where we sample for fish to aid in interpreting our catch results.

Data Collection
Our convention is to collect temp/salinity at the surface and at 3-feet below the surface wherever we beach seine (or just at the surface if the site is shallower than 3-feet). However, we use a depth-temp-salinity data-logger attached to the lead-line of a lampera net for openwater sets. The logger provides measurements of depth/temp/salinity every 5 seconds during the set, down to depths of 20-30 feet.

So, for some 'sets' we have one or two measurements of depth/temp/salinity, and for other sets we might have over one hundred measurements.

Problem

1.How best to get that data entered into the db?
2.I'm just starting to get my toes wet with VBA

Ideally, I could directly enter the values into a subform for sets with only one or two measurements, but could instead 'import' the extensive data for those sets where the logger was used. Entering the logger data manually would be ridiculously time-consuming.

Existing DB Setup
Records for temp/salinity subform/table linked to other set information by a unique Set_ID field.

Subform for depth-temp-salinity information bound to a dedicated depth-temp-salinity table. The subform is currently viewed as a continuous form.There would be one excel file for each set where a data logger was used, but no excel files for sets where no data logger was used..

My thoughts so far.

Somehow create a subform with the ability to enter up to two records manually or else click a button that imports the data from an excel file. One thought is to pop open a window to navigate to the excel file that contains the data for that set. However, I'm thinking that if I place all such excel files into a particular directory and name them using the appropriate Set_ID number convention, that maybe clicking the button with be able to find the file directly, without navigation required, and bring in the records automatically.

Is this possible? How would I go about creating a subform that provides both an 'import data' button and allows for manual data entry of up to two records?

Can anyone show me a similar example for both the data entry (form) and for how to automate the importing of data from excel files to append to an existing database table?

Aim:
The eventual goal of this is to have a command button that could be clicked on the form/subform that would produce a popup window containg a scatterplot graph of salinty versus depth. another button to produce a scatterplot of temperature versus depth. A third button to open a line graph with time on the x-axis, and temperature/salinity series on the y-axis. Before I can get there, however, I need to get the data into the table somehow.

I would appreciate any input/advice on this matter, (especially custom code! ;) ) As, I mentioned, I'm just starting out in VBA and I have a lot to learn. I know how to open a MsgBox, but have no clue on what the command is to open an explorer 'window'.

I hope the problem is sufficiently interesting to generate some response.

Cheers!

View 2 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

Access Advice

Apr 26, 2005

I was hoping someone could offer me some advice.

I have created an MS Access 2000 solution for a company that utilises replication and remote synchronization. The company have about 12 people working out in the field (on laptops) who use replicas of the database (held on the company's server). The solution has become quite unstable and the amount of database conflicts is growing daily.

Could anyone suggest a more robust solution for the senario described? Would MySQL or MSDE be a more stable option? Is there anything I can do to make the MS Access solution work?!!

Any advice or suggestions would be appreciated.

Kind regards.

View 1 Replies View Related

Access FE To SQL Advice

Oct 9, 2005

Hi

I am creating and using an access front end with a SQL server back end (which I manage thru access project), and I am wondering if I am losing my way.

I have some heavy tables which hold upwards of 60,000+ records. Before when I held it all on access, the database would swell up to 200 mb+ and used to grind to a near halt, now with the tables on SQL, it is much quicker, and no corruption problems so far. Also using SQL, I am forced to a better table structure.

I have linked the SQL tables thru to the access front end no problem and have queries running of the them, I find I can do more with access queries as they seem easier to construct and fault diagnose. This is especially true when using the ‘choose’ function. However, I am not sure if this is correct or intended way.

Therefore, in a good access front end, SQL server back end set up, should I

1) Where possible, put all updates, append, and delete queries as project stored procedures and pass them thru to the access FE

2)rather than link the tables from SQL to the access FE, and then make access FE queries to feed the forms, as I currently do, make the query views in project, and link them to access FE, and then make queries off these to the forms.

3) i note comments about using a WHERE function, should this be in SQL if possible

Ultimately, my thinking is that if I can make the project / SQL do as much as possible, it will be easier (for somebody else with better knowledge) to migrate the front end to another platform such as ‘asp’, if required.

Pardon my lay mans language, I need a course or a good book

View 10 Replies View Related

Access VBA Advice

Aug 25, 2004

Hi,
Can someone help me with the following?

I am building an application where the database is created from a large flat file. As a part of the process I need to build 10 temporary tables using VBA recordsets. I have designed the necessary algorithm and have tried simplified model that appears to be working with two tables. Before I go any further I would appreciate if someone could enlighten me in regard to Access 2003 constraints and limitations. The data (text) files are likely to have more than a million rows each, and each temporary table in the database will have more than 10 fields. It appears that using the processed data to populate the appropriate tables (with one “pass” through the file) simultaneously will be most efficient. In the past similar application was build using Access 2000 and the developer had to (or so I have been led to believe) use extraordinary number of macros and queries with numerous passes through the source file. My question is: If the code works with a small file and reduced number of variables and recordsets, shouldn’t the “full size” version of the same code work accordingly?

Thanks!

View 3 Replies View Related

Access Version 1 Advice For Newbie !

Dec 26, 2006

Hi everyone,
I have a very old Access Database (version 1) which I need to open however I only have Access 2003.

Do I need to buy an old copy of Access or can I download it anywhere ?

P.S - IT is not my strong point so I hope it's simple...


Many thanks
Pat

:confused:

View 2 Replies View Related

Advice On Updating Someone Else's Access Database!

Mar 15, 2007

Hi, im currently working on a database which is for someone else. Whe it is handed over to them, they will no doubt want some changes done every so oftern eg new reports, changes to forms etc.

What is the best way to do this? The database holds a lot of data, so I dont think its feasible for them to send it to me via email everytime they need something changed!

Ive noticed a few posts here about splitting the database into a front/back end. If this was done, would they just have to send me the front end; this would probably be a smaller file ye?

If this is going to be the best way round the problem; how easy is it to split a database which is basically already made?

Thank!!!

View 3 Replies View Related

Advice On Updating Someone Else's Access Database!

Mar 15, 2007

Hi, im currently working on a database which is for someone else. Whe it is handed over to them, they will no doubt want some changes done every so oftern eg new reports, changes to forms etc.

What is the best way to do this? The database holds a lot of data, so I dont think its feasible for them to send it to me via email everytime they need something changed!

Ive noticed a few posts here about splitting the database into a front/back end. If this was done, would they just have to send me the front end; this would probably be a smaller file ye?

If this is going to be the best way round the problem; how easy is it to split a database which is basically already made?

Thank!!!

View 3 Replies View Related

Looking For Advice About Using Access As A Document Retrieval Tool

Mar 1, 2006

Our organization (a University) has been engaged in becoming "paperless."

As part of that initiative, we have spent the past several months scanning several hundred thousand pages of documents relating to building systems (HVAC, e.g.) as well a a massive quantity of detailed lead and asbestos surveys and abatement records.

The initiative is indeed reducing the quanity of paper stored in binders and file cabinets, however now it is becomming evident that retrieving some of that information is a real problem for some folks.

At a meeting this morning a question arose about whether we could develop an Access application to retrieve the documents (which are very logically filed on a network share, but it seems to be beyond the compreshension of some staff how to actually navigate through the maze).

My question is this: has anyone in this forum ever tried to implement such a solution with Access?

Any advice or insight would be very much appreciated.

Thanks in advance.

View 1 Replies View Related

Forms :: Create Data Entry Form To Input Data For Field

Mar 5, 2015

I an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)

I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.

I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.

View 1 Replies View Related

Queries :: Filter Data From A Table Using Query (from Data Input Form)

Dec 30, 2014

I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:

IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])

However, is not providing any result when the input field (MaxDiffInput) as a value.

View 5 Replies View Related

Import Data From Excel Monthly - Not Manual Input All Data?

Jan 5, 2015

I was assigned by my manager to design an Access database system that is able to import all data from excel file monthly and creating charts & tables to analysis how each sales people and industry perform.

We originally have a big excel master sheet that has more than 10 sheets. I tried to import the current excel into access, but then i realized that this is not gonna work. because for next month, there will be new data and I can't do the whole import process over and over. Plus, after this system is designed, the users will be someone who has no knowledge in access, so i need to create a user-friendly system for them to use.

My questions is:since the data is always cumulative number, if I imported current excel file into access, when the next month comes, how to update the new data into excel. p.s. EXP. Mike's sale volume is different each month, and with the access system, for that column, it will be a cumulative number, like the total from the month of November to this month. how do i achieve this kind of update/import goal?I tried to link the excel to access, but by doing that, I will not be able to set relationship or change the attributes of any data type in access.

View 3 Replies View Related

Data Type To Input Data Number

Feb 16, 2014

I want to input data number such as 0.5 in my table, but it doesn't work. I already fill field size : integer with format : Standard with Decimal : 2, but the result is always 0.00 not 0.50 as my expectation. How to define that in my table?

View 5 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







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