I am about to create a system where I want to use MS Access for data and MS Outlook for email,contact management etc. I have a dilemma (what do I use for storing the contacts)? I know I can use Access and use VBA to send emails etc but I also want the user to be able use Outlook directly to send emails etc as normal using that contacts (Outlook) list.
Using Office XP at the moment btw.
I would appreciate comments as to the best way forward before I start.
Thanks in advance.
I need to design a database to house the following type of data (there are other fields involved to be connected to each person at each time eg tick box if attended):
Should I set up the the table structure data as: time(ie 7:00, 8:00 etc) Person(ie Person1, Person2 etc) PersonsID Attended (Yes/No)
and have four separate records for each time slot and use crosstab queries (Though I want users to be able to put their own name in, and I think using Access Crosstab that you cant do this :mad:)
and have only one record for each timeslot. If I did it this way how would I select and report on each individual person (would I need four separate queries?)
I have a table called QuizResults which has 10 Yes/No fields. The table also has a field called Site.
Each site will take a quiz and the problem I am having is with the statistics side of things.
What I need is a query that will work out the percentage correct for each site. All the data is stored in this 1 table. There are no relational fields as they are not required. I have tried a few different ways but each method I use involves me making many many queries to work this out.
To summarise I need 10 percentage correct fields, broken down by site.
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.
I'm looking for advice on the best method to accomplish the following from the esteemed members of this Forum (You all have provided excellent advice in the past to this Access Dummy, with my thanks), (I've also searched the forums without result):
I would like to make several fields "required" fields on my form, easy enough, in that I set the Required property on the table to "Yes".
What I would like to happen on the form is that when a user tabs out of a required field, a message box pops up that says "This is a required field" and/or when they click any of the following command buttons I've created, "Save Record", "New Record" or "Close Form", that a message box pop up and list the required fields that they missed.
Any ideas, with code, macros, or other solutions would be greatly appreciated, keeping in mind that I'm just not that swift to start with.
I am trying to decide which is the best way to setup the tables in a customer database.
We have customers with contacts - that bit is simple. Each of out customer has numerous sites and each site has is own contacts.
The questions are:
1. Should there be two tables for Customer sites (main site table and sub site table) or is it best practice to have all sites in a single table with a column linking subsitesID to the ID of the main site: ie
tblSites SiteID (PK) SiteName MainSite (if the site is a subsite of another) More site information fields......
2. The same question really goes for contacts. Should there be a single table for all contacts (easy if only one table for sites) or seperate tables for mainsite contacts and subsite sontact.
I guess this is really about normalisation.
I have managed to get the system I am developing to work with both methods but I would be keen to understand what is the most acceptable method.
My problem is that I would to write a query to display the number of days taken for each of the 12 months of the year.
In the example of Employee 4 their sickness spans two different months, I can't get my head around how to produce the correct result which should look like:-
My application has 12 main tables, 8 join tables connecting the main tables, 8 tables of attributes about the main tables, 21 note/classification tables, and 1 type table. The application provides relationships between data and does _no_ updating. In order for the data to report correctly, subreports are necessary.
I wish to create one report for a Section of data. There are 3 report possibilities: 1. Report of "section" item in one table, with all connections to items in main tables; 2. Report of "section" item in one table, with all connections to items in main tables and connections to items in attribute tables and note/classification tables, omitting printing of Description fields for all tables; 3. Report of "section" item in one table, with all connections to items in main tables and connections to items in attribute tables and note/classification tables, including printing of Description fields for all tables;
My questions are as follows: 1. Should I be building the report in Access or Crystal? (I have a licence for both, but so far have used only Access). 2. Can these options be built into one report, or do I need multiple reports? I.e. I could build the report with Subreport A) including a report on a main table and Subreport B) including a report on a main table, attribute table, note and classification tables. Are there better ways to do this? 3. How do I make the Description field, printable or not, flaggable at run-time?
I was going to say before I screw it up, but that would have made the title too long.
I've read some forum history and done some searches and can't see that a similar query has come up previously - but if anyone can point me at previous relevant threads then that would be much appreciated. I've looked at some of the design theory threads, but am not really sure whether I've applied some of the good stuff in there effectively - and have put this query here as it's specific to my database rather than a 'general principles' thing.
I am hoping that some kind person/people might be able to pick holes in my planned database revamp as I may not have seen some of the pitfalls - thank you.
Background: I am the first and sole employee of a grant-making trust and set up our (still relatively small) database early on - wish I could have waited, but before my database everything was paper-based and impossible to manage. The data is all on the database, but an exponential growth in number of enquiries/applications over the last year means that it can't be easily manipulated in a way that meets our needs because of my short-sighted (OK, poor owing to inexperience in job) initial design in some areas.
Database: What it's supposed to do: In brief, the way our organisation works is that we get a phone call or a letter or sometimes an entire application from a charity or community group, decide what to do with it and have to manage the funding contract (regular reviews, grant instalments) if the application is successful.
I think existing data is reasonably normalised (certainly at least equivalent to normalisation in the structure below) so migration shouldn't be a problem, but if anyone spots any normalisation or naming convention issues then please do point them out - I'm an Access amateur, but would like to get it as right as I can (this time). Small letters indicate linked tables, numbers indicate 'footnotes'.
(a) tblProjEvent PEvID (primary key) PEvDate ProjName ProjEvent (g) ProjEvDesc - additional detail - e.g. who involved, topic if not obvious from event field. ProjEvActWho - who is responsible if future action ProjEvActDue - due date ProjEvActComp - checkbox ProjEvActCompDate ProjEvNote (NB not to duplicate description - more for 'additional notes arising from completed action' or sensitivities that may need to be considered and kept separate from regular reporting)
(b) tblOrganisation Details not listed for brevity, but split one field per line of address/detail, org name as primary key - existing table can be migrated as is (2). Includes suppliers, networking orgs. (f)
(c) tblProjSourceLup How they heard about us - list of sources for speed/consistency of data entry.
(d) tblProjStatusLup What it says on the tin - where we're at right now with a project. History/status changes recorded in tblProjAction.
(e) tblProjReasonDecline List of common reasons for speed/consistency of data entry
(f) tblOrgContacts OrgContact ID (primary key) Names/personal details of organisational contacts, split one field per item (firstname, lastname, jobtitle, etc).
(g) tblProjEventLup Letter, Funding contract, review meeting, report, telecon, Board review, etc. May be a future action.
Accounts functions: Payments in/out linked to organisation with a lookup table for account types (in, out, grant, donation, expenses etc etc). Works fine and can be migrated as is (2)
The current arrangement of relationships has the organisation as the focus - I'm looking to make the project the focus, which seems to make sense as each project is unique - whereas we may get several project applications from the same organisation - and the existing structure makes it impossible to sort these out :o.
Footnotes
1) Vast number of applications that fall into the "arrive, get read, get binned" category make this duplication worthwhile - most applications won't make it into the Project Event table. Apart from the "Greetings in the name of the Lord"/419 e-mails, they do all need recording somehow so we can spot any trends and correct misinformation - save hard-working fundraisers wasting their time*.
2) Of course data validation will be necessary, but in most tables we're talking hundreds or thousands of records only, not tens of thousands so should not be that onerous. Especially as I've been the only person using the database, so there are hopefully not too many inconsistencies!
This post is too long already, but if I've been uselessly unclear in explaining anything or anyone wants any more before they want to comment on efficacy of structure please let me know. All feedback appreciated (as I said, I work alone, so really looking for idea-bouncing to check I can see the wood as well as the trees as much as anything - thanks).
*As charity is always a sensitive area and some of you may be involved with voluntary work, an OT comment: 'No response' might seem rude - but if an organisation is -clearly- excluded by the criteria on our website, or that very same person (or a large charity who should be able to manage its information effectively) has previously been told that they are ineligible by reason of their location, size or type then they have had their response already. I could spend all day every day writing 'No' letters - I'd rather be doing something more positive with my time (like checking out/meeting with organisations that are potentially eligible so they get their answer - and in some cases money ASAP). Not R-ing TFM (aka grant guidelines) apparently applies to fundraisers as much as any other profession :rolleyes:
I have been asked to create a database in Access 2000 that will hold 1.6 million postcodes. There will be four fields within the a table one holding the postcodes, and three fields holding information as to whether or not the post code is classed as good, neutral or bad. The requirements are to allow a user to run a query that asks thenm for the required postcode and then displays the relevant information (good,bad,neutral).
There requirement is that the search is done as fast as possible returning the required results. Has anybody got any ideas as to the best way of doing this.
I have a dropdown list called (cboClaimant) on a form which obviously allows the user to choose claimants from the list. However, if the user chooses a new claimant that is not in the list, they have the oppertunity to add to list or continue without adding, which is fine but:
If they then attempt to merge with one of the word documents that are linked to the Db, the merge fails because it cannot recognise the newly entered claimant. Hope this makes sense and that someone out there can help! here is the code that is running behind the cbobox:
Private Sub CboClaimant_NotInList(NewData As String, Response As Integer) Dim ctl As Control Dim stDocName As String ' Return Control object that points to combo box. Set ctl = Me!CboClaimant Response = acDataErrContinue ' Prompt user to verify they wish to add new value. If MsgBox("Claimant not in list. Do you wish to add new Claimant?", vbYesNo) = vbYes Then ' Set Response argument to indicate that data is being added.
stDocName = "frmClaimant"
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd Forms![frmClaimant]![txtClaimant] = NewData Forms![frmClaimant]![txtClaimant].SetFocus Else ' If user chooses No undo changes. ctl = NewData ctl.Requery
End If Exit_cboname_NotInList_Click: Exit Sub Err_cboname_NotInList_Click: MsgBox Err.Description Resume Exit_cboname_NotInList_Click Me.Refresh End Sub
Okay, so I'm new here. I just recently got back into database development. My first job - stepping up a department's multiple Excel spreadsheets to an Access database. Sounds pretty easy, right? Here's the problem: Out of 5 different spreadsheets, 2 of them belong to other departments and are still modified daily. So, straight linking the excel file to access is not an option. A co-worker told me that I would need to set up a front, and back-end to the database. Still, that pulls on that Excel file and doesn't allow the other department to modify it. How do you go about creating a temporary file while you are modifying the data? I don't need to put any information back into the Excel Spreadsheet. The other problem is that they want to be able to track changes that the other department makes to the spreadsheet. Is this easily accomplished? I also have to keep in mind that multiple users will be using this database. Can anyone point me in the right direction? I'm getting stressed.
Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Products.Catalogs LIKE '%[a-z]%' AND (Products.ItemName LIKE '%t'ai%') AND ((Products.NewItem = True) OR (Products.HotBuy = True)) ORDER BY Products.ItemName'. /webdev/flaghouse/NEWITEM_List_Main.asp, line 149
this results when search terms contain a ' (SINGLE QUOTE). i've tried to double up all of the SINGLE QUOTES in the code and still get an error.
heres the CODE:
' check if keywords entered, split comma-delimited list into array of keywords
If Request("Keyword") > " " Then theKeyword = Request("Keyword") listKeywords = split(theKeyword,",") maxCounter = ubound(listKeywords) whereClause = "Products.ItemName LIKE '%" & listKeywords(0) & "%'" FOR counter=1 TO maxCounter theKeyword = listKeywords(counter) whereClause = whereClause & " OR Products.ItemName LIKE '%" & theKeyword & "%'" NEXT
SQLString = SQLString & " AND (" SQLString = SQLString & whereClause & ")" Else theKeyword = "None" End If
if anyone sees anything amiss, please let me know. i'm at wit's end!!
thanks. please email me at: URL. again, thank you in advance for your help.
I have a form with a sub form that opens based on a record search for a patient, whose details are displayed at the top in the main form. The sub form displays visit information for that patient (there can be multiple visits).
On opening the sub form is filtered and only shows one record at a time. I would like it to open and show all existing visit records for the patient and be ready to enter a new visit!
Resident Experts, I am currently working on a database to track the employee training for my company. Access is the only tool that is available and I'm definitely a novice. I'm working on a form to enter training event information and I have an option group and combo box problem. How can I disable a combo box until a certain option is selected?
I had made a database earlier for this customer but I worked with no process and so therefore I did the best I could. However, the customer has brought something to my attention that needs correcting.
The problem is that although the current database does record training events it doesn't produce reports which show what the employee hasn't done.
Because there are training events that pertain to everyone and then there are events that only pertain to individual employees based off of a 'job code' (which identifies their specialty) I've struggled to establish a link between employees and these two different types of training.
In an effort to correct this problem, I've added a 'project mandatory code' to the 'EmployeeInfo' table which holds all employee information along with their job code. Each time a new employee is entered into the table, a default value is entered into the project mandatory row.
I have seperate tables called 'JobCodes', 'ProjectMandatoryCode', and 'TrainingEventFrequency' which are connected to my 'TrainingEventInfo' table. The TrainingEventInfo table holds the information for all known events.
When a new training event is recorded, an entry is made into my 'CompletedTrainingRecord' table. This table records the event name, date completed, and the employee clock number.
Although I think my logic is sound, I may have missed something and made no progress at all.
Having said all that, the form I am working on to enter training events into the TrainingEventInfo table is where my original question came in. The option group I have has two choices: Project Mandatory Training and Position Related Training. If the user selects Project Mandatory Training then I want the combo box to be unavailable and based off the value it will automatically input a default value for the ProjectMandatoryCode. If the user selects Position Related Training from the option group I want the combo box to become active. My combo box is run from a query which pulls the job codes from the appropriate table and this value can then be written to the record in the TrainingEventInfo table.
Because both the ProjectMandatoryCode and the JobCode are in the EmployeeInfo table I'm hopeful that I can write a query which will pull all events, related to the individual, whether they have been completed or not.
Thank you in advance for any assistance you may be able to provide. I've attached a document which may be helpful. Respectfully, Dale Gagnon
I have a combo box that searches for surnames in my database. if there are two surnames the same, it brings up the first one. if this is not what i want then how can I ask it to search again instead of just pulling down the list and searching manually?
I have made a form based on related tables. it requires me to fill out every field, which I don't want. I didn't make them required. Why does it do that?
I now understand that when opening and saving crosstab queries Access (2010) runs that query to ascertain the column names. Unless you hard-code them. Running the query takes at least 20 minutes. I have hard-coded where I can, but one report takes arbitrary dates so I can't hard-code them.
I believe that turning off AutoCorrect might make a difference to whether the query runs - but I don't want to turn it off.
I have a sub form in DataSheet view and I would like to lock the design so that the User should not change the layout
Even when I set the property of the Datasheet “Allow Design Changes: Design View Only” users are able to unhide the hidden columns and they can also change the size of the column by dragging the column end line
Does any one know how to lock the design of datasheet ( I am using this sub form datasheet for data entry but do not want users to change the layout)
I'm going to make up names and values -- I'm interested in the structure.
Table ALPHA:
COLA DAT1 DAT2 DAT3 1 5 7 9 2 4 14 8
Table BETA:
COLA_IND DAT1 DAT2 DATN 1 a b c
Table CHARLIE:
COLA_IND DAT1 DAT2 2 d e
Table DELTA:
COLA_IND DAT1 DAT2 2 f g
Ok, the idea here is that the data in table ALPHA contains data with COLA a key such that selecting 2 would yield the data row "4, 14, 8."
Now, COLA_IND is a "COLA" key for table ALPHA (sorry, I can never remember which side is called the foreign key). So, from tables BETA, CHARLIE and DELTA, I can access any row in ALPHA based on the key "COLA_IND"
Here's the fun part. When I build my query, it wants to use an inner join on the keys from all these tables... In order words:
SELECT blah blah blah INNER JOIN blah ON (ALPHA.COLA=BETA.COLA_IND) AND (ALPHA.COLA=CHARLIE.COLA_IND) AND (ALPHA.COLA=DELTA.COLA_IND)
What I'm looking to do is expand BETA, CHARLIE and DELTA with the information from ALPHA based on the key COLA_IND. I don't think this is doing what I want.
I've got this problem and im 95% sure its going to need a query in order to achieve this answer im looking for.
I'm creating a Software Licensing Management db and its all working lovely. However my only problem remains is the graphical representation (text box within one of the forms) of howmany licenses are/aren't(doesnt matter if this number is a +/- number) available.
In order to achieve this answer I dont think you'l need the table structure of any of my tables other than these two:
However I would like to add a column to either of these tables named 'Availability' or something similar which will show the licenses available.
I've tried a number of Update/Append queries but all have failed. I want this Available running variable held within the table due to it not changing to much of the current db design as i baisically finished the project and they asked for it! any ideas of how to efficiently achieve this will be much appreciated!
I'm looking for a keyboard shortcut to expand the columns in "design view" of a query.
What I mean by this is rather than selecting all of the columns and double clicking to see the entire text, I'd like to be able to a shortcut.
The entire process as I see it involves 3 steps so I will need thesolution to the 3rd step.
(1) [ctrl+spacebar] to select initial column (2) [shift+arrows] to select all of the columns I need (3) [keyboard shortcut] will expand all of the columns "field" names to the size of the column heading
Alternatively, if you know of a shortcut that will expand the columns without having to select them first I'll take it!!
Im in the process of building a database for a friends business, and im a bit of a newbie with access. Id like to get some opinions on structure and overall how i should build the Database. My goal is to have two types of clients ... donors and buyers. A client can be both a donor, a buyer or both. When a client is a donor, they get a certain amount of credits added to their account. When a client is a buyer, they will be purchasing those credits from a donor. heres an example of what i want to accomplish; John smith donates 500 credits; I enter John Smiths info and credits into his profile; Jim Doe buys 100 of John Smiths credits; I want the DB to automatically update Mr. Smiths Credits, and then add 100 credits to Jim Does profile. Also, I want John Smith to be able to purchase credits from Jane Johnson, and again, have the credits added to John Smith and deducted from Jane Johnson automatically. Get my meaning here? The tables will also contain the typical client info ...ie; Name, Address, Phone, SSN etc... Can i/Should i do a seperate table just for credits and link it to the client tables? Should i create seperate tables for Buyers and Donors?
Also, I have an excel spreadsheet with formulas to do credits already, but when i tried to import it into a table in access, it didnt work so well. Any opinions on table structure, design etc would be greatly appreciated Thanks all for lookin in
Hello to all, i have a non-windows application and i would like to create a vb program to print invoices. I would like to send to this program a txt file with all the values (qty, vat, customer name etc with vertical & horizontal positions in the form etc..) and then superpose all i need to print with an image (gif or jpg wich is the my customer invoice presentation. In fact i have 2 layers , one with all the value i print and another with the invoice image background. I'm a beginer with VB, so i need advices to create this program, maybe someone did this already. Thx in advance VINCENT
Im fairly new to access and im having trouble constructing a stock control system that can create sales orders and adjust stock levels accordingly, hold customer details linked to sales orders. Ive spent about 20 hours trying to do this and its just pickled my brain, ive searched everywhere but sometimes im uncertain what exactly it is im looking for. Can anyone give me some pointers?
I have 7 tables at the mo but its 4 of the tables i need for the sales order:
tblcustomerdetails customerID,first name, last name (general customer details)
what im trying to do at the minute is contruct a subform for a form that would require entering the products into through a combo box selected by productname and then autofill the product description and listprice. Ive ended up deleting all my forms and queries because nothing seemed to work right. I will then add this sub form to a form containing all the customer information and the total price for the subform this then needs to be output to a report for printing, but i can figure that out later. Ive attached my database if anyone wants a look if you dont understand my jibberish.
I was wondering if this could be done in Access. Let me explain
I work at a candies manufacturer in Puerto Rico. Right now we are not tracking any kind of inventory. Is it possible to efficiently track our kind of inventory ( raw materials, work in process and Finished Goods) in Access?? Maybe using a bar code system??
