Different Types Of Entities In The Same Table

Apr 20, 2005

Hi,
I am designing a database.
It is going to store data from questionnaires.
The natural way seems to be to have a table - that stores where the data comes from, what is it about, what is the question and what is the answer, like this:
DataProviderID; FocusID; QuestionID; AnswerValue

My problem is that a Focus could be a eg. a Person, a Company or a Job - meaning that somebody may be giving info on an other person, on a Company or on a Job.
Now Persons, Companies and Jobs would have very different properties.
So how should I solve this shold I have a common table for Peoples, Companies and Jobs and use a very complicated structure of properties for them or should I store these entities in separate tables but than how will I store the results?

Thanks for your thoughts.
SWK

View Replies

Help With Entities

Jul 17, 2005

Hi

First off I want to say I have tried to answer thsi for myself, I have a number of books and I have looked there for the answer, but not sure whether I am understanding it all properly, perhaps I am making it more complicated than I need to :confused:

I want to design a simple database for keeping track of an audit I do monthly at work. I have made sense of the following:

1. I know for sure that I have 2 entities for sure, PrimaryNurse and Patient;
2. I know that each PrimaryNurse is "PrimaryNurse" to 1 or more Patients;
3. I know that each Patient has 1 and only 1 PrimaryNurse;

What I am not sure about is whether the items I audit (they are all to do with the patient, for example is there a photo of them in their clinical file, have they had their legal rights read to them, etc) are to remain in the Patient Table as simple "yes/No" or to be moved to a table of there own.

I apologise if this is yet another simple question for those of you who already understand. If you could shed some light on this that would be great as I want to learn as opposed to just asking for answers.

Thanks

View 5 Replies View Related

Entities

Aug 24, 2006

Can people give their thoughts on the following sernario.

When I have been taught database design in the past it has always been with the aid of examples that have very distinct entities. For example: People loan copies of books.

For the sake of simplicity say you have a booking system.

Event(EventID, EventName, EventDate)
Person(PersonID, Firstname, Surname, ... Postcode)
Booking(BookingID, Person, Event, Guests)

This is the same level of simplicity. However I have never been shown an example where the person entity is more specialised.

What if a database needs to hold details on people of different types? Say you hold college students, school techers and business owners. The details that is required on each is:

Student(Firstname, Surname, College, Address, DOB)
Teacher(Firstname, Surname, School, JobTitle)
BusinessOwner(Firstname, Surname, Business)

I assume that this requires three tables as appose to the one Person table but how does the booking relationship work?

How would you link Events to perople of all three types?

View 4 Replies View Related

Compare Two Types Of Records From One Table

Aug 9, 2011

I am trying to create a query based on a transaction table which compares the time between two types of transactions.

The initial record would have "A' in the type field and the second record would have "D" in the type field.

each pair of A and D records would be matched on o_time and idnum fields, and I would want to pull each pair and us the datediff function on c_time, using records marked with A as the initial time and records marked with D as the followup time.

Is there any way to accomplish this in a single query or do I need to create two subqueries?

View 1 Replies View Related

Simple DB Trouble - Entities, Tables Etc

Sep 6, 2006

Hi

i currently have a simple spreadsheet in Excel containgin the following fields.

## Date - not unique
simpy shows the date of the transaction

## Debt - who the debt will be paid to - unique per date (e.g. CompanyA)

## Type of debt - what service is it - not unique by date or debtor (e.g. Wages)

## Net Price before VAT (X)

## VAT (value added tax, a UK tax) (Y)

## Price of Service (Z = X+Y)

## Running total of Net Price before VAT (X + X + X...)

## Running total of VAT (Y + Y + Y...)

##Balance - (Z + Z + Z...)

The spreadsheet currently calculates this automatically using simple formulas.

I'd like to create a nice little interface for this data entry using MS access forms. From then I can easily create queries and reports etc.

can somebody please help me out! :D I've tried to list my entities and create an ERD to no avail!

Many Thanks

Mike

View 7 Replies View Related

Attaching Files Of Different Types To Table Field Thru VBA

Apr 23, 2007

Hi Everyone,

I have a PO table. Each PO has 3 documents of multiple types (one may be Word, other may be Excel, Acrobat). When I display the PO, the attachments should be shown as Icons, which when double-clicked, will open in its own Application.

I have a pop-up screen where I enter new PO details. In this screen, I have Attach/Detach buttons for each of the Documents.

My Requirement:
When I press 'ATTACH' of Doc1, the File Selection screen should be displayed from where the user will select the file to be attached. This file should be updated into the table field "doc1".

When I press 'DETACH' of Doc1, the file in table field "doc1" must be cleared.

How can I achieve this???:mad:

View 1 Replies View Related

Modules & VBA :: List Of Orders - How To Keep New Types In Same Table

Oct 26, 2013

I have a query which is my list of orders. Now I want to create other type of orders. New types don't use the code in red (tbl Offset and tblRodzajZlecenia). Is it possible to modify the query, in order to keep new types too?

New types of orders are in the same tables. But if I don't fill fields in tblOffset then query can't show new type of oder - that's the problem.

Code:
SELECT z.ID_Zlecenia AS ID, tblRodzajPracy.RodzajPracy AS [Rodzaj pracy], tblRodzajZlecenia.RodzajZlecenia AS [Rodzaj zlecenia], z.NazwaZlecenia AS [Nazwa Zlecenia], k.NazwaSkrocona AS Klient, z.DataPrzyjecia AS [Data Przyjęcia], z.TerminOdbioru AS Termin, tblStatus.Status, tblStatus.lp
FROM (tblKlienci AS k INNER JOIN ((tblStatus INNER JOIN tblZlecenia AS z ON tblStatus.Id_Status = z.Status)

[Code] .....

View 1 Replies View Related

Queries :: Query To Count Types In A Table

Jun 5, 2014

I have a query that selects invdate, status and type from a table, I want to be able to search for invdate=today status between 50 and 70 and to count types 30, 31+32, 33+34 and 35.

View 4 Replies View Related

Make Table Query Returns Binary Data Types

Jan 31, 2008

This the first time I post in this forum and I tried looking for an answer to my question before posting, so I apologize if my question has been answered previously.

Is it possible to determine, in Access 2000, the data type in a Make-Table query of a new column with null value so that it does not default to binary.

Here is my sample sql:

SELECT tblAddressBook.Name, Null AS Email INTO tblTestTable
FROM tblAddressBook;

The output is tblTestTable with two columns: Name and Email. The data type of column Email defaults to Binary. How do I make it default to Text.

Thank you.

View 4 Replies View Related

Connecting Super Type & Sub Type Entities With A Condition

Sep 21, 2004

hi friends,
i have tried had to connect sub type tabels (Saving, Checking, Loan... they have their own ids...) with super type (Account...it has account id...) on the condition of account_type (either "S","C" or "L") attribute in ACCOUNT entity.
how to joint them??? with query or with expression??
i expect help from you.........please.
........thanks.

View 5 Replies View Related

Data Types

Sep 5, 2005

I have an Excel file (sourced externally) linked as a table. All fields are defined as text fields. In some of the Excel cells, data has somehow been entered as numeric (eg phone number). When I query the Excel file thru Access, these fields show up as errors (#num!).

I need to find a way of interogating the field, and if in error (like these ones), then ignore it, or replace with zeros etc.

I cant find a way of doing this. Help :)

Rod

View 1 Replies View Related

Relationship Types

Jan 12, 2006

Hi,

I'm a student trying to get to grips with relational databases and Access is a great tool to learn.

Does access support 0..1 and 0..many relationships? I've been playing around with Access for a while and can't seem to model the 0..1 and 0..many relationships.

Thanks for your help

Jack

View 2 Replies View Related

Yes/no Data Types?

Jul 18, 2007

Hi all :o

Just a quick stupid question, I have a Yes / No data type in my table but I want it to automatically put no unless I change it to Yes, is there an easy way to do this, probably a really simple question but am new to this and any help would be great

Thanks

Jackie

View 1 Replies View Related

Types Of Drop Down

Sep 8, 2006

I need to display a drop down list to users.
I know that in a combo box, the user can also type text. However, I want to stop them from doing this and only be able to select values.

Is there a better control I could use? I tried the listbox but this does not seem to "drop down" the data like a combo

View 1 Replies View Related

Field Types

Sep 10, 2006

I am building a table where one of the fields is a yes/no field type. When the yes box is selected, I need to "jump" to a different set of field names than field names for selecting no. I have researched on internet and have done some reading but have found no information. Is it possible to do what I want to do in Access? My skill level is that I can build tables, create forms, create reports and run simple queries. Any help offered is appreciated.

View 8 Replies View Related

VBA Data Types

Feb 2, 2005

I'm trying to append a group of fields to a SQL table using ADO in VBA. One of the fields I'm writing back to is a datatime and in certain situations there won't be a value, however, when I try this it throws in the beginning of time 1/1/1900 or whatever it is. Any ideas?

Thanks.

View 4 Replies View Related

Changing Data Types

Oct 19, 2005

Is there a way to change the data type for multiple fields at once? Such as 20 text fields to numeric in one step.

Thanks

View 1 Replies View Related

Numeric Data Types

Jan 17, 2006

Hi,

Strange but true.
I am trying to numeric data types on my access 2000 where I could input values in table like 0.3567 or 2.3454
Anyway any values of this kind with 4 decimal places. however somehow the values always get rounded to number + 4 zeros as of example:
if I put 0.756769 I want it to be as 0.7568 but I get 1.0000
I don't understand this. Can anyone help please?
I have not used access for ages and I seemed to be forgeting all this stuff.

Thanks

dfuas

View 2 Replies View Related

Appointments With Three Types Of Roles

Dec 16, 2004

hi everyone, i sort of run into a dead end trying to figure how to structure this. I have three tables for the 3 roles, tblClients, tblDoctors, tblBusinessContacts, each with a Primary key and some unique information about each role. I'm not sure how i can track appointments to each of these roles, i know how to do it if it was one role. I want to be able to later view all appointments for that day with all 3 of these roles.

Also, for clients i have a multiselect listbox describing their reason for the appointment and somewhow would like to associate that to the appointments. Some info i would like about these appointments:

1. date and time
2. details of contact
3. date for next appointment

What would be my best bet?

Thanks!

Joso

View 9 Replies View Related

Replace And Data Types

Jan 26, 2006

Hi all!
I'm experiencing an apparently simple problem, but it's taking me too long to solve :confused:
Two tables with a "text" (100) column on each. I want to select those who are identical. ¡So easy! thats the where clause:

AND a.sDesEntidad = b.sNombreLargo

That works fine. But... i want to "relax" the clause, by not considering dots ( . ) when comparing, and i do like this:

AND (Replace([a]![sDesEntidad],'.','') = Replace([b]![sNombreLargo],'.',''));

and i get a run-time Error 3464, Data type mismatch in criteria expression. Oooops!
My access version is 2003, spanish. I would really appreciate ur help, since this matter shouldn't be keeping me busy for so long :(
Regards and thx in advance!
Alejandro

View 1 Replies View Related

Omit Types Of Records

Jan 29, 2006

I have a table with a field for "type". I want a query to run that will show everyone exept "type" A and B. I can get it to work with one of them using Criteria "Not A" and it works. But cannot figure out how to make it not show both A and B

View 2 Replies View Related

Data Types From Different Sources?

Jan 31, 2008

Hi,

Wondering if anyone has a solution for my problem.

I have 2 data sources, one which is an ODBC and one which is an import table. I have a 2 queries each querying the 2 data sources seperatly.

My problem is... One of the fields which I need to do a join to a reference table has a different data type in each of the data sources. I have tried changing this in the imported table NO JOY!! You cant change the data type in the ODBC link.

Is the anyway round this?

Any help appreciated...

View 4 Replies View Related

Access Data Types

Feb 9, 2006

I am trying to do a comparison between two values and am getting the following error:

Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.

The code that is creating the error is as follows:

sb = Request.Form("storyboardscreen")

if Not Lcase(Request.Form("AllStoryBoard")) = "all" then
whereClause = whereClause + "theStoryboard = " + sb
end if

I am using the statement to build the where clause of my query. The sb value is retrieved from a pull down menu and "theStoryboard" column is the db is of data type "text". The values stored in the field are 12 digit numbers. I have tried doing a cstr to sb but that doesnt work, any tips on what I am doing wrong? I am using asp with an access db....

Thanks

View 5 Replies View Related

Trying To Consolidate Information With Different Value Types

Jul 10, 2013

I have a database that, vaguely looks like the following

NAME Value Category A Value Category B Value Category C

Name A -50 0 0
Name A 0 50 0
Name B -100 0 0
Name B 0 75 10
Name B -25 0 0
Name C -100 0 0

Etc. etc.

What I'd like is to find a query that will first, generate a Category D which should be a total of all the values in the three different categories and consolidates the names to one line. So it should spit something like this out.

NAME Value Category A Value Category B Value Category C Total
Name A -50 50 0 0
Name B -125 75 10 -40
Name C -100 0 0 -100

After this, I'd like to have another query that will only display the values that are NOT zero.

NAME Val Cat A Val Cat B Val Cat C Total
Name B -125 75 10 -40
Name C -100 0 0 -100

View 3 Replies View Related

Updating Between Fields Of Different Data Types

Jun 21, 2005

WorkID is a look-up field, so its Data Type is Number.
In the same table, DailyGrind is a field with the Data Type of text.

I would like to update WorkID with the jobs in DailyGrind.

Surely there must be a way to do it, gurus?

View 4 Replies View Related

Survey - Multiple Answer Types

May 16, 2007

I am struggling with an answer table for multiple type answers. Some questions have yes/no answers, some have option group answers (agree, neutral, disagree), and some require text input.

I have reviewed posts on normalization on survey design. In it's simplest form I would have 3 tables.

tblQuestions
QuestionID
QuestionText

tblAnswerCodes
AnswerID
AnswerText

tblResponses
PersonID
date
QuestionID
AnswerID

If all answers required an option group value then I would use the structure above.

If all the answers were yes/no I would eliminate tbl AnswerCodes and simply have an answer field with data type yes/no in tblResponses.

But if an answer requires a user text comment, I would need either a memo field or a 255 length text field. This is where I get confused. It seems inefficient to use a memo or text field for all answers when most of the questions require a yes/no answer. I feel the answer is somewhere in setting up another table with answer types, but I am struggling with this.

On a further note I am struggling with the response data entry form. For an option group answer I could setup an append query for each new survey response. But how about for multiple answer (check all that apply). I guess I need a form based on a query linking tblQuestions to tbl AnswerCodes. This would be a subform on the master form for tblResponses. I keep getting all the answers for all questions. But handling multiple question types on one continuous form seems awkward.

Thanks.

View 4 Replies View Related