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 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 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 am designing a database for my organisation. I have done most of it but am stuck on this. Ideally I would like to have a check box (in a table) that when checked a load of data is carried across from that record to a record in another table.
Given that I can't and don;t want to use VB is there anyway that this can be done easily?
This only shows the flow if the main category is "Floors", sub category "Joist", and then the choices under subcategory "Joist". There will be others for the other categories, subcategories, and choices.
Can someone help me or steer me in the right direction in setting up the database table/tables to accomplish this. Oh and BTW, there will be prices attached to each "Choice" under the subcategories.
I hope that this makes sense... any help would be much appreciated.
if i had a table with products and i want to keep a record of what has been chaged or added to this product, whilst keeping the old records how could i set it up in a databse?
the excel spread sheet looks like this
product commments
1 chnged this and that 20/08 1 modifyed component 21/08
It's a simple question really. I'm currently learning databases and am doing a project, just to show that i understand everthing i've learnt so far. I have to produce a small database.
There's nothing difficult about the project, and its a very basic database, but i was just hoping to ask a small question.
I'm producing a DVD database, to hold details on a collection. My main table holds the majority of the fields, as follows;
The problem came with boxsets. Originally i was just going to have boxsets as one entry, but some contain a few dvd's, each with different certification, etc, and i wouldn't be able to record running length, director, etc. So i added a ysnBoxset field (just to record whether a dvd was part of a boxset or not) and then made a new table with
lngDVD_ID strBoxsetName
So now i can record which are part of a boxset, and the name of the boxset they belong to. But.. then i got to the problem of Purchase Price. Obviously i cannot record an individual purchase price for those which belong to a boxset, and i want to be able to record the price of the boxset.
So my question is... is it acceptable to add a curBoxsetPrice field to tblBoxsets, which would allow me to store the price... and then just leave curPurchasePrice as £0.00 for those dvd's in a boxset??? Or is there a better way of doing it? (I know there is, but i'm a novice :( )
Thanks for any help you can give, sorry for the lengthy explanation! :rolleyes:
Within my db I have a table, tblProjects which currently has 36 fields. Each project will have a fixed set of 12 tasks, each having a begin and end date as well as a yes/no "completed" field. This would add another 36 fields to tblProjects.
Instead, I have created a new table tblTasks which has these 36 fields along with a PK(TaskID) and FK(ProjectID) and is related to tblProjects. I believe tblProjects is already normalized properly with relationships to tblCompanies and tblContacts as well.
I'm not sure if this was neccessary, or should I have just added the task fields to tblProjects. What, if anything, are the advantages of one way over the other? I'll be upsizing to SQL Server, does that make a difference?
Bit of a long one but I require some advise if people can supply it.
I've started creating a DB that is now getting out of hand due to me being inexperienced. The things I want to do now are getting way to complicated. From reading various posts I know that this is all being caused by my database design and while the DB is still young I'm go back to square one. I want your advice on this if you can to ensure 3 weeks from know I'm again not chasing up weird bits of VB code to make it all work.
I basically have a lot of information to hold that interconnects with each other. I'm making up a role profile DB (so we can assign define access rights to a computer system) I have lots of info to collate but the basic stuff is.
Staff Records (Name, business area & ID) Applications (What computer applications do staff require) Shared Drives (What access to shared drives and folders do they need) Shared Mailboxes (what access to shared mailboxes) Roles (users will be assigned roles to suit their requirements)
Basically what I have at the moment is below.
Roles Role ID Role Name and about 15 other fields that dont really matter for this
Applications Application ID Application
Shared Drives Shared Drive ID Shared Drive
Shared Mailbox Mailbox ID Mailbox
Each role will only have a select number of applications, mailboxes and drives from the overall list. so role 1 may give access to 2 of the 10 applications, 1 out of 5 shared drives and 3 out of 4 mailboxes. Role 2 on the other hand may need 9 out of the 10 applications, 1 out of the 5 shared drives and 1 out of 4 of the shared mailboxes.
I just don't understand how I can create a form from these tables that would display all the different roles with their respective apps, mailboxes and drives they would need.
any advice you can give to help whould be appreciated.
I am designing a db for our Law office. I want to have tables for different types of Civil Cases-ie Divorces, Deeds, Contracts, Personal Injury, Auto Accident, Slip and Fall, Product Liability, etc. The divorce, Deeds, tables are easy but I am having difficulty deciding on the personal injury tables. Heres my dilemna. Can I get away with one or two Personal Injury tables even though there will be blank data fields. For example, I will need a field for the name of the product in a Producrs Liability field. This field will be blank in all other Personal Injury Cases. I know the rule is you don't do that but my alternative is 15-20 tables for every concievable Personal Injury Case- Auto Accidents, Slip and Falls, Fraud, Prouduct Liability, Accounting, Attorney, Dental and Medical Malpractice, etc. This is in addition to other civil case tables, criminal case tables and federal case tables. Anyone have a suggestion?
I am not sure if which I should be using as the main entity for my tables. I am creating a database for hourly information on individual employees. Each employee will have the same types of hours to keep track of (weekly, bi-weekly, monthly etc) I naturally thought to treat each employee as the databases own entity and created fields for each type of hours but hit some trouble when trying to pull queries to compare each employees hourly information against each other to see who is being efficient or not. Each employee has the same exact fields but with just different numbers. We track each type of hour by week (Field One: Week 36, Field Two: Aug 28-Sep 03)This is the first time I am creating a database from scratch. All advice greatly appreciated and TIA :confused:
I have a table within a database and i was wondering whether or not i should split the table into 2 seperate tables.
The layout of the existing table is to record quotation information ie product codes, cost price, sell price, delivery dates available etc and it also contains fields that allow the user to state if the quote has been authorised or declined, when it was authorised and a number of options to explain the reason the quote wasn't accepted.
QuotationID - Primary Key EmployeeID - Foreign Key CustomerID - Foreign Key EnquiryID - Foreign Key ProductCode Quantity CostPrice SellPrice DeliveryDateAvailable Notes Authorised - Yes/No field Type DateAuthorised Declined - Yes/No field Type ReasonDeclined LostPrice - Yes/No field Type LostStock - Yes/No field Type LostDelivery - Yes/No field Type LostCustomerClient - Yes/No field Type LostNoFeedback - Yes/No field Type
above is the layout of the table, does anyone think that it might be a good idea to split this table into two tables, one table to record the quote information and another to hold the authorisation or decline information??????
i am creating a database for a local football club. I want to show player information (the main focus of the database), such as, what guardians are responsible for this player, what are the Doctors details for this player, what team does the player play for etc. I have seperate tables for the Guardian, Doctor and Team information.
I also want to hold Staff details, (Name, address, staff title etc)
My problem is that someone in the staff table, could also be in the guardian table.
Should i combine the Guardian and Staff tables into one table as I dont want to have to enter the data twice for both a staff memger and a guardian
Note: a player can have more than one guardian a guardian can be responsible for more than one player.
What is the best way to approach the design of these database tables.
Have a database that I am working on this is constructed to perform Compliance tracking of wellness activity by employees. Have all my tables, queries and a form that has 3 tab controls as well as a subform on one of the tabs.
It is necessary to create a quarterly report card based on 6 wellness cateogories that have specific criteria. I thought I was OK but now I am having difficulty pulling it all together on the report. I now think that it has to do with my table construction and I am looking for advice. If there is someone I could send my DB to who can take a look and give advise or assistance I would greatly appreciate it. Thanks.:confused:
I'm currently in an internship working solely with databases. I've become really interested in designing databases and I now want to design my own. I was wondering if I explained my table structure then someone could give me their thoughts and ideas for improvement. I have a huge sport trading card collection and I wanted to create a database for it. This way I could run some quick queries to find out how much my collection could be worth, how many cards I have, easily keep track of duplicates. Things like that.. This might seem strange, but I think it would be a good learning experience that would help me utilize many of the features of Access.
My Table Structure:
Ok I have a Company table(Topps, UpperDeck, etc) and they sell many boxsets(which would be another table).. Boxsets have many Cards(which would be my third table)...
Now here is where I get confused. I want have a sports table that would categorize my cards by what sport they are, but I don't know where I could link this. I also have a players table. This table will have a 1:M with Cards because 1 player can have many cards, but couldn't Cards and Players have a 1:1 because 1 card shows one player???
Finally, I want a Price Table that will be linked somewhere, but I've yet to figure out where to link this too.. Have I missed anything???
Your ideas and opinions are greatly appreciated!!!
i have a table(patients) with more than 1500 records.this table contains patients information like diagnosis etc.I use cbo boxes in my form to choose info.(i have separate tables to keep this information e.g.table for diagnosis, table for treatment procedures etc.
of course these info are kept in the patients table as text fields.and also i have many query with criteria to select specific records(for example ) parameter value is [Enter diagnosis] and i type for example "cardiac" to get the list of cardiac patients etc.
now , the size has become large . so i want to do somthing to solve this i designed this when i was very new to access.
how if i want to change the field type from text to number which can look up the required info from other table.
but if i do this , i think that my queries want work .
any advice on this.
i have about ten fields to be changed from text to number.
tblreservation (cottage reservation details) cottage (text) - linked to tblCottage customer (test) - linked to tblCustomer start date (date) number of nights (number) Adults (number) Price (currency) etc..... ReservationID (autonum)
tblCottage (list of cottages available for rental) cottage (text) CottageID (autonum)
I need to add a table structure which defines the price band for each cottage at several times throughout the year, in order to take account of peak holiday periods etc. The price field in the tblReservation table needs to refer to the correct price band based on the cottage and the start date of the reservation.
Any thoughts how best to approach this would be greatly appreciated.
I am looking at creating a database for a charity, to help them maintain there volunteer information.
The database will need to hold information about the charity, volunteers and there roles, language and status.
This is the db design so far.
Branch id PK - charity number name * house * street1 * street2 street3 town * city postcode * p_office * p_land * p_mobile fax email not sure of data type and length www not sure of data type and length
volunteer id PK - unique number, auto number vol_name * f_name * s_name * house * street1 * street2 street3 town * city postcode * p_home * p_work p_mobile email last_change date/time, when record last updated
vol_link_lan vid FK, PK lid FK, PK level qua
language id PK, Auto name *
vol_link_rol id PK, vid FK rid FK sdate * edate *
role id PK, auto name * description
vol_link_sta id PK, vid FK sid FK sdate * edate *
status id PK, auto name * description
I am not sure if the vol_link_rol and vol_link_sta are correct, I have created a PK to ensure there is a record of the volunteer and the status and the same for the roles table.
I have been asked to create a simple 5 question quiz and can not for the life of me figure out the table and form design. The goal is to have a question listed and then 5 answers to choose from. I need the answers to be in an options control format meaning they will read the question and check one of the option boxes for their choice. I need it allow only one of the 5 to be chosen...that is the problem. If I make each answer a field then they can check all of them and I don't want that. I don't think this is that difficult but would like to stay away from coding it as I am very good in access except for the code part.
