Pre-Design Dilemma - Advice Required
Apr 13, 2006
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.
View Replies
ADVERTISEMENT
Jan 31, 2008
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):
Time---Person1---Person2---Person3---Person4
6:00-----John-----Peter-----Paul-----Sam
7:00 -----Mary-----Lisa -----Julie-----Mike
8:00-----etc
etc
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:)
OR
Should I set up the table as follows:
Time(ie 7:00, 8:00 etc)
Person1
Person1Attended
Person2
Person2Attended
Person3
Person3Attended
Person4
Person4Attended
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?)
Thanks in anticipation
Penny
View 8 Replies
View Related
Mar 28, 2007
Hi All
Im after some help with a query im building.
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.
Please can anyone help?
Many Thanks
Dazstarr
View 5 Replies
View Related
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
Jul 1, 2005
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.
Many Thanks,
Photoguy
View 9 Replies
View Related
Mar 9, 2006
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.
Thanks for any help
View 1 Replies
View Related
Feb 4, 2008
Hi all,
I have a table of employee sickness / absence records with the following structure and data:
http://www.geocities.com/cyngorsir_ynys_mon/TBL_SICKNESS.gif
I have figure out how to write a query which displays the number of days taken for each sickness (END_DATE minus START_DATE).
http://www.geocities.com/cyngorsir_ynys_mon/QRY_DAYS.gif
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:-
http://www.geocities.com/cyngorsir_ynys_mon/OUTPUT.gif
The only way I can think of doing it is by recording each day of sick individually rather than just the start and end days?
ANY input / comments / observations would be greatfully received!
Thanks
View 3 Replies
View Related
Mar 28, 2008
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?
Any advice on how to proceed appreciated. Thanks.
View 1 Replies
View Related
Aug 8, 2007
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'.
tblProject
ProjID (primary key)
ProjName (a)
ProjDateRecd
Organisation (b)
ProjSource (c)
ProjStatus (d)
ProjResponse (Lookup: Letter, E-mail, No response*) (1)
ProjRespDate
ProjReasonDecline (e)
ProjNote (description)
(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:
View 4 Replies
View Related
Apr 27, 2007
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.
View 2 Replies
View Related
Mar 10, 2005
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
View 6 Replies
View Related
Aug 8, 2005
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.
View 1 Replies
View Related
Aug 31, 2003
i have an error:
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.
kwc
View 5 Replies
View Related
May 26, 2005
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!
View 2 Replies
View Related
Jun 17, 2006
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
View 3 Replies
View Related
Dec 6, 2006
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?
View 1 Replies
View Related
Nov 3, 2014
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?
View 3 Replies
View Related
Oct 6, 2014
I have had to use my first crosstab queries.
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.
View 6 Replies
View Related
May 18, 2007
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)
Thanks
Rahul
View 1 Replies
View Related
May 5, 2005
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.
Any comments?
Thanks in advance...
View 1 Replies
View Related
Sep 10, 2007
Hey,
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:
tblLicenseInformation
License_ID
LicenseDescription
NumOfLicensesPurch
SoftwareOverview_ID
tblHardwareSoftwareLicense
HSL_ID
Hardware_ID
Vender_ID
Software_ID
AppEdition_ID
AppVersion_ID
SoftwareOverview_ID
License_ID
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!
cheers
View 3 Replies
View Related
Jun 5, 2014
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!!
View 4 Replies
View Related
May 12, 2005
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
View 3 Replies
View Related
Jun 4, 2005
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
View 2 Replies
View Related
Sep 3, 2005
Hi all,
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)
tblorder
orderID, customerID, delilvery address fields..., subtotal, total, delivery
tblorderdetails
orderID, productID, productname, description, listprice, quantity, discount, linetotal
tblproducts
productID, catagoryname, productname, description, costprice, listprice, profitmargin, suppliername, instock, reorderlevel, quantityperunit.
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.
thanks
View 6 Replies
View Related
Oct 29, 2005
Hi all
First post.
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??
Is it possible??
Please advice
Thank you
View 2 Replies
View Related
