Table Design Analysis Needed

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 Replies


ADVERTISEMENT

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

Modules & VBA :: Metrics Analysis Table - File Too Large

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

Help Needed For Database Design

Jul 11, 2005

Hi,

I want to make an access database but Im struggling with the relations a bit.
I understand the one-to-many and many-to-many concept but seems like there is more then that

I want to make a database that keeps track of things we lend.

I have several items with different properties we lend out:

eg

TANK
-ID
-Volume
-Weight

MACHINE
-ID
-hours
-fueltype

...

I made the following tables to keep track of it:

RENT
-ID
-Customer
-...

One-to- many relationship between these

RENTLINE
-ID
-RENT_ID
-Item


My problem lies with this Item.
I want this Item to have a relation with the ID of the different items we lend out. there needs to be a one-to-many relation here.
one rentline can exist of one TANK OR of one MACHINE.
A TANK or a MACHINE can exist on many orderlines.
So I made 2 one to many relationships from Rentline to the id's of Tank and Machine

the problem is that Access does not let me fill in the rentline with the id of a item. If I fill in the ID of a TANK it says a ID of a machine is needed and vice-versa.

I tried to solve it with an intermediate ITEM table that looks like this :
ITEM
-ID
-Sort ( Tank or Machine)
-ItemID (Id of the tank OR the Machine)

But this generates the same issue as directly in the rentline...

I guess this is pretty common stuff Im asking here but its confusing me like hell :(

thanks in advance for all advice

View 6 Replies View Related

Help Needed For Database Design

Oct 30, 2006

I would like to make a human Resource database that shows in a tabbed form some informations about our employees.

I would like to show per employee:
tab 1: general info (name, address, starting date, type of contract, department)
tab 2: salary history
tab 3: capabilities -whether they are available YES/ NO tick field and for the department the person is assigned to

I am thinking of the following tables:
Employees - EmployeeID, Name, address, starting date, type of contract
Department - DepartmentID, Capabilities
Salary History - EmployeeID, Date, Salary, Notes
EmployeeCapabilities - EmployeeID, DepartmentID, Capabilites [Yes/ No]

The only problem I have is to make the correct relations between the lot of them.

Can anyobe help out here?

View 1 Replies View Related

Database Design Issue - Help Needed!

Jan 30, 2007

Hi,

i need to design a database but am abit unsure the best way to go about it and am looking for sum thoughts and suggestions.

I need to create a database for part numbers and competitior cross references, however the problem i have is were we list one number a competitior could list upto 5, and where we list 5 numbers a competitior could list one, so there could be literally thousands of duplications per competitior both ways around.

for instance we list DG897, our main competitor would list AB458, AB459, AB 487 etc. Basically the part number is a car part, and we say one part number fits serveral vechicles, but out competitior says its one part per vechicle. (sorry for the poor explaination)

has nebody came up against a database where the possibilty for duplications is so high before and how did they go bout designing the tables, I am far far from a regular user of access, however am very keen to learn.

hope you all can help

View 1 Replies View Related

Analysis Database

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

Data Analysis Issue

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

Access Data Business Analysis

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

Analysis Graphs For Individual Customers

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

How To Do Document Error Analysis And Debugging In Access

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

General :: Denormalizing Data For Easy Analysis In Excel

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

Data Analysis Options DCount - Crosstab Query

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

Tables :: DateTime - Running Pivot Queries To Excel To Do Analysis Of Data

Aug 27, 2013

I have a larget transaction data set in access with Datetime column/filed.

I have been running pivot queries to excel to do analysis of the data but the datetime field is returning too many unique values for the pivot table to run.

What is the best way to reduce the datatime field to date only and where should this be done?

i.e. should I have a calculated field that trims datetime or should I set someohting up in Powerpivot?

View 7 Replies View Related

Tables :: Pivot Analysis - All Data Fields Are Blank Every Time On Opening Database

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

Help Needed :If Table Exists, Then Delete Table

Jun 6, 2006

I need urgent help, I am required at my job to come up with a command that will check to see if a table exists, and if so, delete the table. The whole process goes like this:


There's a form, and a listbox. 2 buttons...1 is import a file and 2nd is generate report. In the listbox theres 3 excel files. You select one excel file then click on Import, followed by clicking on Generate Report button.

The whole technical process is this:
1. A csv file is imported into a temp table
2. A temp table is created and named "_ImportedSKUS".
3. An append query is exectued to add the data from the "_ImportedSKUS" temp table to the final table, "Imported SKUs".
4. Then the temp table, "_ImportedSKUs" is closed and deleted.

I have a delete command at the end of the sub, which goes like this:

DoCmd.DeleteObject acTable, "_ImportedSKUS".

But I'm also need to generate an If statement before the loop that will check to see if the "_ImportedSKUs" table exists, if so, delete it (using same code listed above) and end if.

Can anyone help me out here???

View 4 Replies View Related

Design Table So That When A Box Is Checked Data Is Transferred To Another Table

Mar 23, 2012

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?

View 8 Replies View Related

Duplicates In A Table-Help Needed

May 19, 2005

Hi Everyone, I need help in selection of primary key and designing of a tables. I am having a table called Current trailers with Trailer number as a primary key. I have similar table to current trailers called history table in which the trailers that left the yard are stored. As a trailer can enter and exit the yard couple of times in a week, its not letting me to have duplicates in the history table.So can anyone help in selection of primary key for the history table. Or can anyone tell me how I can have duplicates in a table.

Thanks in Advance,
Naveen

View 2 Replies View Related

Junction Table Needed??

Apr 24, 2006

I’m having trouble defining Relationships I’m thinking I need a “Junction” Table and I have tried looking at the Orders.mdb but it hasn’t helped (I’m sure I’m just missing something) I just don’t see how it works. If at all possible please don’t just give the info try to help me understand so I can get the answer myself.
Here is what I have:
Far table:
FarNumID (PK) > autonumber
FarNumber > Text “224-10C”
FarTitle> Text

FarParagraph table:
FarParaID (PK) > autonumber
FarNumID > Number
FarParaTitle > Text
FarParaText > Text

AC table:
ACNumID (PK) > autonumber
ACNumber > Text
ACTitle> Text

ACParagraph table:
ACParaID (PK) > autonumber
ACNumID > Number
ACParaTitle > Text
ACParaText > Text

1. Each FarNumber can have only 1 FarTitle 1:1
Each FarNumber can have many FarParaTitles 1:Many
Each FarNumber can have many FarParaText 1:Many
2. Each FarTitle can have many FarParaTitle 1:Many
Each FarTitle can have many FarParaText 1:Many
3. Each FarParaTitle and have only 1 FarparaText 1:1

Thanks so much.

View 4 Replies View Related

If A AND B Then C Lookup Table Needed

Oct 30, 2006

I am looking to set up tables in such a way that will allow for the concept:
If Variable 1=A and Variable 2=B then Output of 3=C

For Example:
If the House is "Blue"
And the Man drinks "Beer"
Then his pet is a "Dog"

If the House is "Green"
And the Man drinks "Vodka"
Then his pet is a "Bird"

NOTE:
There is no equational relationship between the first two variables to get the 3rd output. It is simply a lookup table. In excel you would have the color house down the left, the drink across the top and in the cells would be the type of pet. But I am struggling with how this can be made to function within Access since I will want the user to obviously be able to pick the first two variables and have the "answer" spit out.

View 2 Replies View Related

Help Needed With Table Formation

Mar 14, 2007

Hi

I will give you an overview of what I have.
I have a database that records sites in Blackburn and each site has a unique ID (RTP_ID). Each site has 10 objectives with 25 Measures in which the sites are scored against.
Objective 1 has 2 measures
Objective 2 has 8 measures
Objective 3 has 1 measure
Objective 4 has 1 measure
Objective 5 has 1 measure
Objective 6 has 3 measures
Objective 7 has 2 measures
Objective 8 has 1 measure
Objective 9 has 2 measures
Objective 10 has 3 measures

Each site has the same objectives and the same measures, but the scores for each are individual.

Table: SiteDetails
RTP_ID, SiteName, Postcode

Table: Objectives
OBJ_ID, ObjName, ObjScore, RTP_ID

Table: Measures
OBJ_ID, MSR_ID, MsrName, MsrWeight, MsrScore

I don't know how to create the relationship between them so that each site can have 10 objectives and 25 measures (with scores) individually. At the moment, I have them connecting as SiteDetails.RTP_ID > Objectives.RTP_ID and Objectives.OBJ_ID > Measures.OBJ_ID but it doesn't work, it thinks that the scores apply to every site.

Please help me, I know that this may not make sense without seeing the database, but unfortunately it is too large to attach.

View 4 Replies View Related

Log Table Solution Needed :(

Nov 4, 2006

I have almost finished my current database but I was asked to create a log table/log file that would list changes made to every record. Now my current database don't allow duplicate records, so any advice pointing me into the right direction will be helpful. I have ran through the search area and found nothing that I can use. Can any one help me out in this specific problem. I picked up a few books and none of them give examples of such things. Thanking you all in advance...

View 14 Replies View Related

Linked Table Relationship Help Needed

Nov 30, 2005

I have a database that has a linked table within it. I need to enforce referential integrity on this relationship but it won't allow me too.

Does anyone know if this can be done?

If so, could you possibly point me in the right direction

Thanks

View 4 Replies View Related

Opinion Needed On Table Structure

Nov 16, 2004

Hi everyone,

Could someone please share their opinion with me on the following:

I have a database with a Table which stores People's details, e.g. ID, Name, Surname, ...., etc (e.g. tblPeople) .

Now I have people filling out a questionnaire, of which the results I want to save in an Access table. Each person will fill out this questionnaire only once. The number of fields I will need to accomodate the answers to questions asked, is around 120. For example, an answer to Question1 will appear in a field called Q1.
I know that the limit for fields in a table are 255, and my total fields in tblPeople so far are about 20.

Should I keep Q1 and all other fields in the same table as tblPeople, or create another table to hold all answers and create a one-to-one relationship between tblPeople and tblQuestions&Answers ?

Can someone give me a tip, in particular if this will cause performance problems?

I appreciate your effort for reading this.

Kind Regards,

Jean

View 2 Replies View Related

Table Structure Assistance Needed.

Dec 29, 2004

I manage academic papers whom are written by multiple authors and reviewed by multiple reviewers. Currently, all the data is stored in one big table and I'm not able to get any statistics or real tracking out of it.

I have set up a test DB with 3 tables so far:

Main
Table_ID -auto sequential key
CORP_ID -internal paper id
STATUS -open, overdue, accepted, rejected
REC_DATE -received date
MOD_DATE -record last modified date
TITLE
AUTH1_ID -linked to Author.ID
AUTH2_ID -linked to Author.ID
REVR1_ID -linked to Reviewer.ID
REVR2_ID -linked to Reviewer.ID

Author
ID -key field
NAME
DESIG -designation
EMAIL
PHONE

Reviewer
ID -key field
NAME
DESIG
EMAIL
PHONE
RATING

My goals are these:

Create a module that manages the authors. They are usually the same group of people. Each author should only appear once.

Create a module that manages the reviewers. Also, usually the same people (but different than authors). Each reviewer should only appear once.

Create a main form that allows the end user to add a record (paper), assign author(s), assign reviewer(s).

Create Query/Report that would then be able to:
- track authors and their papers
- track reviewers and their reviews
- as well as track papers and their authors/reviewers

When I create subforms, they appear to be 'backwards' meaning that the Author form tends to be the main form and the Main form tends to be the subform. I think this is b/c the Author.ID field is the key field, whereas the Main.TABLE_ID is the key field, but not relevant for other than keeping things straight, nor is it related.

Can someone point me in the right direction?

Alternatively, has someone created a database like this already that I can buy? :)

Thanks!

View 1 Replies View Related

Tables And Table Structure Needed For New DB

Oct 31, 2005

Firstly, I want to admit that my knowledge about data base construction is fairly basic.
I am trying to build a data base for humanitarian projects that do not at all resemple of the usual examples including customers, invoices, suppliers and what have you. My problem in constructing the DB is that most of the projects will address more than one subject and also include more than one target group and even operate in more than one country.
In my first attempt I filled the relevant fields (subjects, target groups and country) with more than one value using a form with multi select lists. The problem was that it was very difficult to query these multi-value fields and kind people in this forum strongly advised my to reconstruct the DB avoiding such fields.
What I need is to be able to view/print various selections based on precise criteria that include a region (or country), a subject and a target group in all kind of combinations.
The problem is to decide on what tables to establish and their interrelation. I guess that when entering a project record (using a form) I will have to store somewhere what subjects (could be more than five) and target groups the project include. It is, of course, not a problem to establish special tables or value lists for the subjects and target groups and countries, but where/how do I store the basic project information for a project and the multi-values that are related to the individual project?
I hope that this is not too confusing and I would appreciate any suggestion for a simple table structure and the interrelation between the tables.
Many thanks in advance.
Niels

View 1 Replies View Related







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