Help With Database Design - Analysis
Sep 23, 2005
Hi Guys,
I have sort of problem - I'm trying to desing the database for one of the organisations.
The entities are: Family, Child, Referral, Voucher, Receipt, Provider, Activity.
That's ok so far. I've been asked to do the option for the new address of the Child - in case if they move to the new place and keep the old one as well.
Family table is the 'child table type' for the Child table - family can have more than one child, but one child can be part of only one family (one to many relationship).
I thought to make a seperate table 'tblAddress' and store all addresses in this table - then link them to the family - this however requires from me to have additional field for - for instance - second address, third address, which in case there was no change in address would remain empty value.
The other option - which obviously is absolutely waste of space is to add straight away additional address fields in the 'Family' table itself.
Don't you know of any other, more appropriate solution?
If you need more detail - I can send the copy of the database which I have created so far.
I would appreciate any help.
Thanks,
Scabro
View Replies
ADVERTISEMENT
Dec 22, 2004
I am designing a database for my company that keeps track of new classes and new courses created. I have a form for each with the create a class form taking the course title from the courses table/created course from the create a course form.
Here's my dilemma. I want to use cascading combo boxes for class location, facility and room but I think my table structures and relationships may be incorrect.
Here is a list of my tables and their fields:
tblClass
ClassID (PK)
CourseID (FK)
InstructorID (FK)
StartDate
EndDate
StartTime
EndTime
ClassMin
ClassMax
Materials (yes/no)
MatNotes
EmpID (this is the ID of the employee who requested a class creation)
LocID
FacID
RoomID
LogID (Logistical Coordinator ID, if applicable)
Course
CourseID (PK)
CourseName
more...
Employee
EmpID (PK)
FName
LName
more...
Instructor
InstructorID (PK)
FName
LName
more...
Logistical
LogID (PK)
FName
LName
more...
Locations
LocID (PK)
Location
Facilities
FacID (PK)
Facility
LocID (FK)
Rooms
RoomID (PK)
Room
FacID (FK)
LocID (FK)
When I run a query with class, locations, facilities, and rooms no information shows up. If I run the query with the relationships between facility/room, facility/location and room/location removed (leaving the only relationships between class and these three) it works. Why is that? Shouldn't there be a 1-M relationship between Location, Facility and Room? Or, should there be a M-M relationship and I should create tables with each PK?
I apologize for the long post but this has become quite frustrating (despite the simple logistics).
Thanks!
View 1 Replies
View Related
Mar 18, 2006
Hi everyone,
I'm toying with the idea of designing a database to simplify a task I currently undertake at work (using excel) - comparing price movements in the shares of a certain company with the average movement of the share prices in the sector, and displaying any material discrepancies on a certain day in a report.
From my limited understanding of access I think I would need:
- "PriceData" Table, with the fields - date, share, price, dailymovement%
- "SectorData" Table, with the fields - date, sector, price, dailymovement%
- "Relationship" table, specifying the sector that relates to each fund
- A query which summarises the above, returning the date, share, and the two movement percents and then copies them into a temp table.
- A query/report which pulls out the relevent (material) descrepancies from this temp table and presents them in a nice format.
Is this possible and am I along the right lines?! Any input would be greatly appreciated!
Thanks,
Mat
View 1 Replies
View Related
Sep 5, 2014
I work on a pre-created Access database, and the other day I was working on it, and was trying to export something to Excel to sort it and do some Pivot analysis.
Anyway, I must have pressed something, because now every time I open the database, rather than saying "record 1 of 20463" and showing the data from record 1, it shows "record 1 of 1" and all the data fields are blank. If I go to "Records" and "Show All Records" they'll all come up, but I don't want to have to do that every time, and as I import and export all the time, I'm worried that the next time I try it it'll mess up the years of data I have.
View 10 Replies
View Related
Apr 6, 2005
I work for a train maintenance company and to keep track of the defects we use access. Our data is stored in tables (eg unit1) and each defect is assigned a fault code (eg TRD.99). These codes are then used to report to our customer where errors our occuring on the trains.
There are 17 categories of code defined by the 3 letters at the start and the specific problem is stated by the digits. I need a method of tabulating the codes by unit number and a total given in another column. To do this I need a code to count the number of times each three letter code appears in the column of each units table and place the value in the corresponding column in the overview table. I then need a code to add up the total faults for each unit like the sum function in an excel spreadsheet. The final table should look something like this
Unit NoBOGTRD
30010 21
30020 17
30031 17
30040 4
30050 5
30061 18
30070 3
30081 7
30090 4
30110 0
30120 2
TOTAL3 98
Any help will be greatly appreciated
View 8 Replies
View Related
Aug 15, 2007
Is there an Add in for Microsoft access that will using a gui based method, run queries, set up automated reporting (task Scheduler) in an easy to administer method. Quest Toad has a new add in Toad for Data Analysis. I am looking for something similar for access. Right now I am doing this manually via creating macros, etc. But there really should be an easier way.
Thanks
View 1 Replies
View Related
Mar 4, 2005
My database records order details for analysis. I have created a form to monitor individual trends in orders placed by my customers. As we supply bespoke items each customer has a list of products which they alone purchase, i.e we do not supply stock items, each item is specifically for one customer (they can then buy the items on a regular basis). The products are assigned to a customer in the products table.
The Analysis form I have created displays the customers name and two graphs. The first graph shows total sales each month, this graph updates as I navigate through the records (i.e. a different graph is loaded for each customer). The form also contains another graph, this is where the problem lies! This graph shows orders of the individual products, controlled by a drop down list containing all the products we supply. I need to limit this list to only show those products relevant to the current customer select on the form. (The products are assigned to a customer in my products table). Currently I achieve this by using a query which when the form is opened asks me to enter a customer ID, the drop down list is then limited to the products supplied to that customer.
What I would like is for the drop down list to update as I navigate through the records. i.e. when customer A is displayed show all the products assigned to customer A, when customer B is displayed show all the products assigned to customer B.
I’ve attached a snapshot of the form to help explain what I mean.
Thanks for any suggestions.
View 1 Replies
View Related
Nov 8, 2005
Hello everyone. Im relatively new to access, i've only been using it for a few weeks. There are certain aspects that I cannot get my head around.
Is anyone willing to let me email them what I have done so far and the criteria for the database I am making and advise me where I am going wrong and where I need to be heading to get it right?
Thanks very much. Any help will be very much appreciated.
View 14 Replies
View Related
Apr 15, 2007
When creating a database is it true that ideally i should avoid using the lookup wizard at table level and instead do that with combo boxes at form level ?
Thanks
View 6 Replies
View Related
Feb 13, 2008
Hi
I have three tables
1 tblAssistFMWork in which is stored the fields JobNumber, SiteRefNumber, SurveyorNo
2 tbltable1 which has the field SiteRefNumber, Address1, Address2 etc
3 tblAssistFMSubJobNumbers which has the fields JobNumber, SubJobNumber.
JobNumber is the relationship between tables 2 and 3 and SiteRefNumber is the relationship between tables 1 and 2. There are other fields in each table but these are the relevant ones for this question.
Each JobNumber will have a minimum of one SubJobNumber but may have many. Each SubjobNumber starts at 1 for each new job so this may look like this in the table
JobNumber SubjobNumber
0001 1
0002 1
0002 2
etc
Each SiteRefNumber may have one or many JobNumbers
I wish to enter the date a SubJobNumber is completed and have been struggling with pulling the record into a form using a combo box because of the need to enter the JobNumber and also the SubJobNumber. So I decided to create a new table tblAssistFMJobDateCompleted which has the fields
JobNumber, SubJobNumber, DateJobCompleted
I need to report on a jobs status ie done or not done, but when I created the query for this it would only pull the records which have a completed date on them.
Could someone please give me some advice as to a way forward.
Thanks
Richard
View 3 Replies
View Related
Feb 20, 2005
I have been reading other posts, and this where i should have started from the beginning,I am not very experienced with access (or forums as yous propably know) but can learn anything in no time (hopefully).
I would like to design a database to store all the info about a drag racing event including;
• event info (Event name, track name, event date)
• car info (Car name, gearbox type, diff ratio, engine name,)
• driver info (driver firstname, driver lastname)
• Engine info (make, model, engine compression, carburetor, front carburetor jets, rear carburetor jets, fuel pressure, engine timing at idle, total engine timing)
• Race info (track temperature, relative altitude, milibars, humidity, air temperature, tyre type, tyre size, tyre pressure hot, tyre pressure cold, stage RPM, shift RPM, reaction, 60foot time, 330foot time, 660foot time, 660foot MPH, 1000foot time, elapsed time, elapsed MPH, comments.)
My issue is design of tables and relationships. as you can probably see, obviously cars will do many runs at each event, and there engine setups and car setups(diff ratio) can differ each run. there might also be different drivers for the same car. weather info like track temp etc also change from race to race so I think it is possible I might need some sort of race time as-well. My main reason for the database, apart from organizing all my results would be so that I could do a run at an event and search previous races by the current runs weather info (relative altitude, milibars, humidity, etc) as to set the car up similar to keep it’s elapsed times consistent. Please, please, please help as I am at a total loss.
Thanks in advance!!
View 14 Replies
View Related
Aug 5, 2006
Hi all
I've created a database from scratch. No data attached anywhere yet - I'm planning to input the data manually through my Form.
Well I have about 15 tables - CustomerInfo_tbl is the main table with the field RefNumber my primary key (it's an Autonumber which is sequentual). The rest of the tables are normal tables which I plan to link.(these tables also have RefNumber fields which are LongInteger) All the tables have about 10 fields in them.
So then I have 2 choices - I can make one big table (about 150 fields) or 15 tables with 10 fields each and the join them. I heard somewhere that it's better to have small tables and then link them up. So I'm going the smaller tables route.
I'll tell you what I did. I joined all the tables to my CustomerInfo_tbl with a one-to-one join. Then I made a Query, and then I set the RefNumber of the other tables equal to the RefNumber of my CustomerInfo_tbl.
I have 1 problem. When I enter some data into the form (I do not enter all the fields I leave some blank) it isn't displayed in the query, but it is displayed in the appropriate table. Only when I enter all the fields it is displayed in the query correctly and also in the tables.
Any help? I hope I made my question clear enough.
Thank you in advance.
Cheers
kruger101
View 2 Replies
View Related
Nov 7, 2007
Hi, i got back to working on databases again for the first time in years and ive got confused on the offset.I went to set about desigining my tables and cant figure out how to do it.most of its simple stuff however this one bit stumps me.
Im designing a database to hold stock information in work we manufacture parts for rally cars and when a customer rings up i need to have the price at hand.I can store the prices etc of individual parts,however some people request kits which would have there own stock number and all the same relevant data i.e. Item Name,Type,Cost,Number in stock,Selling Price.......
These kits would consist of several items from the stock table.and when a kit is sold it will be inserted into the Sold Items Table together with the other individual parts a Customer may order.
Any help would be gratefully recieved.
View 11 Replies
View Related
Oct 22, 2004
Hi all
I have a new project but before I get started, I need some advice on design.
I'm designing a student report card which I'll implement for 1300 students next year. The concept is an ongoing electronic markbook where teachers update the progress of each student and then at the end of the year print a report based on the captured data. The data is then passed on to the next teacher the following year.
My problem is data storage. During the course of a year a teacher may mark off up to 550 things a student can do. After 7 years of schooling (7 x 550) this amounts to a large amount of data. The thing is, I could reduce the number of fields if the multiselect list box stored multiple pieces of data. Option groups are no good because only one piece of data is stored and I certainly don't want thousands of check boxes.
Basically, I'm looking for a way to store multiple pieces of data in the one field. I'm also looking for a way in reducing the amount of clicks (i.e. one click instead of 2 to enter data)
I can't delete data because it's an audit requirement to keep it.
Any ideas??
I appreciate any help
View 4 Replies
View Related
Mar 4, 2005
Can someone help me out with the design of my database? I currently have it set up one way and I am running into problems when I need to update information?
here are the attributes I have to put in the database for Printer cartridges:
ProductName
Manufacturer
Category
ProductCode
Price
Weight
Description
--------------
There can be numerous products with the same name
(for example: C120 is a product name but can be made by HP and by Canon)
There can be numerous products with the same ProductCode
(for example: F416301700 is a product code but can have multiple products with that code)
Currently I have it set up like this:
3 Tables
--------
PRODUCTS
ID
PRODUCTNAME
MANUFACTURER
CATEGORY
DETAILS
ID
PRODUCTCODE
PRICE
WEIGHT
DESCRIPTION
COMBINE
ID
DETAILS$ID
PRODUCTS$ID
-----------------
I have created that combine table to link the two other tables together.
I am running into problems now when i want to update a product and I don't know where to take this from here. I think if I have a better design from the get go it will prevent problems in the long run.
Can anyone help me with this?
Thanks
dp3133
View 7 Replies
View Related
Mar 17, 2005
Hi, I’m fairly new to access and I’m creating (well, want to create) an employee database. I was wondering if anybody has created an employee database that keeps track of employees information like; personal information, salary, training the employees have taken, performance reviews and much more and if so do you have any suggestions on the design. Pretty much this database will be an electronic version of the employees personnel file.
Or does anybody know of any sample databases, not necessary an employee database that have been shared here in the past that I could look at and get ideas (formulas, qrys, tbls. etc.) from there.
Thank you.
View 2 Replies
View Related
Jan 11, 2006
Hi,
I am designing a db for my car event that happens once a year.
It needs to hold car info like engine specs, car specs. Entrant details and event details, merchandise info.
I started out designing on paper (Can’t stress how much this helped a definite must for all noobs and experienced I would imagine) and went from there.
Ended up using northwind and modified it to suit my situation.definately recommend this to beginners as it gives you a good look at databases and great starting point. anyway this db has everything I need I think, the db is for holding info about participants in a car event. There is a form that the entrant would fill out asking there details and the car details. There is also a section on the form to buy merchandise as the car event has its own T-Shirt and DVD.I have added this into the database but I am having trouble finding where to put the T-Shirt Sizes.
E.g. there has been 3 events so far (1 per year), car event 1, car event 2, car event 3.each event has had a T-shirt. this t-shirt comes in sizes, xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl.my problem is do you add the same t-shirt in the products table 10 times just with different sizes.
also the Entrants form has subform event, where you select the event each year as to not have to enter entrants twice.i can not seem to figure this out either.
Once you look at the db you will have a better idea.
The database only has a very standard form for getting around while I complete the design, all the beautification will come after this stage.
Any other ideas would be greatly appreciated.
Thanks
(PLEASE COPY AND PASTE THE LINK INTO YOUR BROWSER OR IT WILL NOT WORK DUE TO REMOTE LOADING)
Access 2000
Access 2002-2003
View 1 Replies
View Related
Oct 16, 2012
I do some error analysis for debugging an Access DB. My question is are there some regulations or frameworks, which could support my work.
View 2 Replies
View Related
Jun 19, 2013
I have a quality control database that has a QCEntry table that contains information about each sample the QC technician takes from production. This table has a one to many relationship with the TestResults table, where the tests performed on the sample and their results are stored.
QCEntry table is structured like
Code:
EntryID Product Lot Number Day Time
1 AB-500 121323 12/23 5:00
TestResults table is like
Code:
ResultID Entry ID TestName TestResult
1 1 Carbonblack 50
2 1 MFI 10
My question is: Is there a way modify large amounts of data like this using a query or some other method to look like this? Kind of denormalizing the tables?
Code:
Product Lot Number Day Time Carbonblack MFI
AB-500 12323 12/23 5:00 50 10
View 3 Replies
View Related
Jul 22, 2013
I am attempting to create a metrics analysis table from another table. What I would like to do is copy the structure (only) from table 1 into a new table. Change all the fields in the new table to text (except for an ID field which would be an autonumber). Then run a seperate group by query against each column, counting the values in each group (i.e. first query would have two fields The grouped column and the column count.
Once I have these values I would like to concatenate them (with the count in parens) and then push these values back into the new table under the appropriate column.
My code does this. I basically loop through a recordset that runs to each column/field groups and counts and then Edits the new table with the concatenated data.
My first table is 170 fields and 38K records. The issue is that it's too much for Access to handle and it blows up (on field 123) Telling me the File is too large. The file does explode to 1G. Then I can shrink it back down to 67mb by running a repair and compact... and then run the the data for the rest of the fields in that table. When I compact again I get about 80Mb.
So now I have two tables, both with an ID field... so I try to link them together (via a make table query) and meld them into one table... but it keep running into that "File Too Large" issue.
How can I have two tables in a database file with a combined size of 80Mb, but when linked together are too large for the database file? Does it have something to do with having all text fields?
I looked up the limits to MS Access and the field count doesn't appear to be an issue since it's nowhere near 255... So what's the problem here?
View 10 Replies
View Related
Dec 27, 2014
I have a table [Control Table] with the fields [Date signed] and [outcome] date signed is formatted as dd/mm/yyyy and the outcome field is a drop down with the options granted, not granted ect
I am looking for a way to present the data using specific date ranges.
I have found 2 possible avenues;
Dcount in a select query:
w/c 01/04/2014 GRANTED: DCount("[Date signed]","[control table]","[Date signed]>=#04/01/2014# And [Date signed] <=#04/06/2014# And [Outcome]='Granted'")
w/c 01/04/2014 Not GRANTED: DCount("[Date signed]","[control table]","[Date signed]>=#04/01/2014# And [Date signed] <=#04/06/2014# And [Outcome]='Not Granted' And [Reason not granted]='Assessed'")
w/c 01/04/2014 Discharged: DCount("[Date signed]","[control table]","[Date signed]>=#04/01/2014# And [Date signed] <=#04/06/2014# And [Reason not granted]='Discharged'")
etc...
But I would need to create the multiple queries 52 times each for the different count value per week
My 2nd option
I have looked at crosstab query, but I cant find a way for it to list the specific dates I need it to query e.g from
01/04/2014 - 06/04/2014
07/04/2014 - 13/04/2014
14/04/2014 - 20/04/2014
etc...
Any tips on Data analysis? I have been able to perform the task previously in excel using If statements but we are now moving to access.
View 1 Replies
View Related
Sep 11, 2005
I am combining 12 Databases. I have split all 12 between the server and the desktop. My question is “Should I combing all of the data table on the server into one database or should I leave all of the individual application data tables in separate Databases on the server”. Additionally, is there a limit to the number of tables an Access DB can handle? My inclination is to keep the functionality separated but the problem I have is that some of the functionality within the applications overlap. Recommendations!
View 2 Replies
View Related
Oct 6, 2006
To all you access Gurus
I have been asked to look at setting up and access database to do the following
To keep a record of pupils and 9 tasks the pupils have to do and also the total amount of time it took a pupil to do this task
They have a total of 25 Hours to do all tasks but this is spread out over the year.
What I need is someone to tell me the best table layout to do this
The pupil record has to have the following
First Name
Surname
Form
Total Hours taken for all 9 Objectives
A description of what the pupil did to achive each Objectives
record if that task is complete
Also Date Task was Complete
I have done a test database with just one table in it and inside that table
had all of the above but apart from name, form and total hours I repeated the rest nine times.
This just does not seem right
What I want is to pull up the pupil name and then select Task say from a Drop down box this would then insert a new field if it did not allready exist in pupil recored and then you can fill in the task details of course if the field/s all ready existed then to open that up to allow you to update this
It would then need to update the total Hours field in the pupil record with the hours it took to do that task/objective.
I have basic Access knowledge but i cannot think of best way to achive this can anyone help please
Stuart Purvis
ICT TECHNICIAN
HILBRE HIGH SCHOOL
View 3 Replies
View Related
Jul 30, 2007
Hi,
New to forum. Beginner/Intermediate Access Experience.
Here is what I am trying to accomplish:
The database i am creating will basically need to have a user check-off a list of Fire Extinguishers that he has checked, on a monthly basis. For example, the user will enter a date, and then a form with a list of all the extinguishers will pop up, and he will place a check mark by each one. When he enters the check mark, that date will be stored so that we have a history of when each extinguisher was checked and by whom (using initials or something)
Currently, my design is simple, something like this:
tblUser
pkUserTableID
fldUserName
fldUserInitial
fldINSPECTIONDATE
tblExtinguisher
pkExtinguisherID
fldExtinguisherNumber
fldExtinguisherManufacturer
fldExtinguisherType
fkUserTableID
My question is this:
-Am I going down the right track with the 2 tables?
-Do I need a third table to Store the Historical Data?
-I am not quite sure how to layout the form so that all the extinguishers are listed.
I know these questions may seem vague, but any help would by highly appreciated.
thank you.
View 4 Replies
View Related
Nov 21, 2007
I am trying to set up a database that manages responses to a variety of survey questionaires, and I want to know if I am on the right track before I proceed. Sorry if this gets a bit verbose:
Here's the general 'business rules':
1) There are many different historic survey types. Some have also not yet been defined.
2) A specific question may appear on more than one survey type.
3) Each survey type can have a different number of questions.
4) The 'valid' answers to a survey question fall into two general groupings:
- multiple choice (variable number of choices)
- freeform text (and "other__________ " could be a multiple choice response)
5) There will be a lot of repetition in the multiple-choice choices. For example, there will be lots of "TRUE/FALSE" or "YES/NO" questions, lots of 'Rate the following on a scale of 1 to 5' type questions, etc.
6) Some of the multiple choice valid-answer-sets may be more unique, such as '200,400,600,1000,other'.
7) We (sometimes) want to capture such information as 'don't know', or 'answer illegible' or 'not answered' or 'don't care', but these can just be more 'pre-set' choices in the db that do not appear on the paper forms
After some whiteboarding, I arrived at the attached db structure....
View 4 Replies
View Related
Dec 31, 2007
Hello, I'm designing a database for a small country club for skiing, and am running into an issue. One of the goals of the database is to keep track of how many times potential members try out the skiing, currently we are storing the dates they ski in the same table as all of their personal information, We need to keep track of each individual date, so there are field for first visit, second visit, etc. We need to be able to run a report in which it can search the six date fields and pull up all records within a date range, is this possible, or should I create an additional table which stores dates, and is linked to the guest ID?
your help is greatly appreciated!!!!!
Thanks, Brian
View 2 Replies
View Related
