realtion between category table and products table should be one - many. when i make the relation, it is always one - one.
this is of course making impossible to have 2 prdoucts with same category in the products table.
attached is my current table relationships, please guid me for a fix if applicable
thanks
Hey guys-
I have a db where I'm not sure what kind of relationship I need to establish between the tables. I have a db that manages real estate properties and realtor information. Each record can have 3 separate Realtors represented (or, as little as 1 realtor). Each Realtor has their own office, or, might have the same office as the other realtors. So, one record can have anywhere from 1 to 3 Realtors associated with it.
I have a Property Table (holds all the info on the property as well as the Realtor's unique alphnumerica ID Code).
I have a Realtor Table (holds all the contact info for the Realtor, as well as their unique alphanumeric Office Code)
And I have a Realtor Office Table (holds the contact info of the Realtor's Office).
In the Properties table- I have 3 fields for the 3 separate Realtor's ID Codes (like an employee number). Let's call it Agent1Code, Agent2Code, and Agent3Code. There won't always be an Agent2 and Agent 3 in the data- but sometimes there is.
How do I set this up and establish the proper relationships?
I have created two table in my database listed below
tblEmployee
EmployeeID{Primary Key, Auto Number}
FirstName
LastName
PositionTitle
tblWorkHistory
EmployeeID {Foreign Primary Key}
Organization {Primary Key}
Position
StartDate
EndDate
I am creating a one to many relationship with the tblEmployee EmployeeID and tblWorkHistory EmployeeID
because one Employee can have many past jobs
where I run in a cloud is that in access under the edit relationship dialog window it has an button called JOIN TYPE when I click on that button it open the Join Properties dialog window and presents me with three options
1. Only include rows where the joined fields from both tables are equal
2. Include all recoreds from the 'tblEmployee' and only those records from 'tblWorkHistory' where the joined fields are equal
3. Include all records from 'tblWorkHistory' and only those records from 'tblEmployee' where the joined fields are equal
Can someone explain what access is trying to ask me as it pertains to my example. I am new to access to a basic answer would help me with future relationship issues and a better understand of the concept
Thanks
I have inherited a table where the data type is binary in some cases and I want to change these to Numerical. When I do this in design, I get a message which says I can't do that. I have something like 200,000 records.
If I try to export as Text file I get Invalid Argument message coming up. Can anyone help?
i have a temporary table which is created by importing an excel file
some extra fields are then added to the table using sql vb code for example
strSQL = "ALTER TABLE [TBL_TmpSubmission] ADD COLUMN [EnergyUnit] TEXT;"
CurrentProject.Connection.Execute strSQL
these fields are later populated conditionally using an update exectuted again from vb code
If Me.cboProgram.Column(0) < 3 Then
strSQL = "UPDATE TBL_TmpSubmission SET EnergyUnit = 'GWh'"
Else
strSQL = "UPDATE TBL_TmpSubmission SET EnergyUnit = 'CarbonTonne'"
End If
CurrentProject.Connection.Execute strSQL
the problem is that this field now has a memo data type, this is a problem because i need to write a query which uses this field in a table join and access won't let you join on the memo type
anyone know why this might happen? for the time being i'm going to try and fudge this by using a further ddl statement to change the data type back to text but i'd rather know whats going on here
My database consists of one large table of approx 3.9m rows and about 12 different columns. One of my columns is of a data type "date/time" but i need the format of this column to be "text"
The problem is that when I right click the table/go to design view/ and try to change the data type, I get the error "Microsoft Access can't change the data type. There isn't enough disk space or memory."
Which means i can't take the easy way out. Is there a way I can just create a new identical table and change the data type of my one column? I was thinking there might be something in SQL I could do, or some how in creating an identical table where only the field type has changed. I should note, I looked up this using the access in file, and it appears the ALTER TABLE SQL will not work on my machine.
the dataset was created from a txt file which as been lost to the ether, and I can't import the database into excel using a macro I didn't write unless the format of my column is text.
I created a table in MS Access using a Lookup & Relationship data-type. This means that my record cells call upon a particular table for values. It creates a LIST of values for that cell.When I query a particular value in this table I get every possible combination of the value. My query and code are below:
OR,
SELECT MainT.Content.Value, MainT.Source, MainT.[Entities Impacted].Value, MainT.Update,
MainT.[Divisions Impacted].Value, MainT.Announced, MainT.Effective, MainT.Stakeholders.Value,
MainT.[Ref#ID], MainT.[Issuing Agencies].Value
FROM MainT
WHERE (((MainT.Content.Value) Like "*" & [Forms]![SearchF]![ContentCB] & "*" Or (MainT.Content.Value) Is Null)
[Code] .....
This means that if I query STATES: "California", I'll get back every possible combination that exists with the name California in that particular field.The issue with this is that I'll get MULTIPLE primary key values rather than just 1. So, if one record, under STATES has California in it, but the other fields in that record have the Lookup & Relationship data-type, then every possible combination of that record will query instead of the multiple field-values form that you'll see in the table I am querying.
notice that my primary key (REF#ID) is repeated numerous times! This obviously causes problems with generating records and forms concerning information for 1 particular primary key. Is there a way around this problem other than specifying search criteria down as much as possible?! Perhaps there is some SQL code.
I am trying to change a field that currently has a data type of number and I want to change it to a date/time data type. When I try to do this, I get a message that says there isn't enough disk space or memory. I am working with a database that has about 100,000 records in it.
View 1 Replies View RelatedI am trying to change a text field (that has been used to store Dates) into a date/time field so that I can sort it correctly
However, when I try and change the data type, It gets to about 90% on the progress bar then tells me that my system does not have enough memory available, and that an error has occured, data type not changed etc.
My system is fairly powerful, and I don't think that it is strictly the problem.
Any ideas??
I am trying to change the data type of a field in a table from calculated to something else. It gives me the error "this data type cannot be changed once the field has been saved"
Is there any work around to this?
I have a field in a table that is comprised of mostly numerical data but some records are text.
I want to convert this field to numerical only and make a new field to put the textual data in.
However converting the field will delete the textual data. What is the easiest way to convert the field but save the textual data AND append the textual data to the SAME record that they were in originally in the new field?
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.
I have a table with a field with names set to text data type and i want to change it to number data type but when i do it in design view the data get lost. I want to know if there is a way to convert the data in the field as number type and keep the data in the field.
View 7 Replies View RelatedHi All,
I am new to access and this forum, so hello all.
I am trying to build a database to keep records of my bird sightings. As you might have surmised; I am a birder.
I have come to a holt on creating a complex relationship between the Bird Entry From and the table that contains a list of British birds.
In Bird Entry From two of the fields are for entry of the bird name, one field being the Common Name and the other being the Latin Name. They are both drop down lists that contain all the birds from the table, this being done by import data. The table that holds the bird list has two fields One is called Common Name and the other is called Latin Name, this being the same as the filed names in question in the Bird Entry From
This is where I am coming unstuck. What I would like to happen is that when I enter a bird in the Common Name field (either by typing until the bird is auto entered from the drop down or using the drop down to find the bird) it automatically enters the Latin name in the Latin Name field and visa-versa, but I have become very frustrated with trying to do this.
Am I wasting my time? Or is it possible? And if so would anyone be kind enough to tell me how?
Many thanks,
Simon.
Hi i need help with one of my relationships
I need help with linking Member 1: N Reservations
the entites are
Video (Vcode, VTitle, Date Made, Director, Genre)
Copy (CopyNum, Vcode, Due Date)
Loan (LoanMemNum, CopyNum, Ldate, In, Out)
Member (MemNum, MName, MAddress)
Reservation (VCode, resMemNum, Resdate, CollectDate. Collected Not Collected)
The keys are underlined
Cheers
Hi there!
I've three tables in my database, tblInfo, tblSavedInfo & tblCountry.
for tblInfo I have this fields - Ref: Cost: RefCountry:
for tblSavedInfo I have this fields - Ref: Cost: Country:
for tblcountry I have this fields - RefCountry: Country:
My problem is when I save from tblinfo to tblSavedInfo I want it from RefCountry on tblInfo check wich country is on tblCountry and save on tblSavedRef the country instead the refCountry.
As tblInfo has about 200.000 rows I need something to make it quick. Is there anyone that can help me please.
Thanks
Any advice? I've tried some things and no success with linkage
Rooms Table
RoomID
Room No
Type
Desc
Project Table
Project ID
Project
Desc
Faculty Assignment
Project Grant No
Sponsor
Project End Date
Student Table
Student ID
Students
Many rooms with many projects, many of those projects in many of those room, with many students working on those projects. Many rooms, many projects, many students. Argh...
I've already tried two junctions between rooms and projects // and projects and rooms
What is making this so difficult?
As you all know Im not a expert on databases but work in a volunteer basis in a small community area where we have no money for people to do anything for us.
Im proud because I did the other database and its up and working but this one has got me beat.
Im setting up one for the volunteers, which has computer knowledge, any basic training they need, when they are available and what tasks have been set for them.
I need to have a one form which has just their personal details but to be able to put in data on the other forms and it will link back to the volunteers name.
Ive attached what Ive done and Im sure your all going to laugh - but any help would be appreciated
Barnesy
Trying to set up a fairly simple DB. Here is the layout:
tblRoom(roomID, roomNumber, rackOne, rackTwo, rackThree)
tblStudent(studentID, name)
Each student will be assigned to one, and only one, rack. There will be 3 students to one room. One student can only have one room but a room can have many students... I store studentID in the rackOne, Two and Three fields. My problem is when I try and querry for the information so as to display actual names rather than ID's. I'm not sure If my querry is the problem or if I've set the tables up incorrectly. Any insight is much appreciated.
Hello,
I'm having some problems setting this up right.
I have 3 databases.
Volunteers
FIRST
LAST NAME both primary
Movie Data
MOVIE
DATEboth primary
Attendance
MOVIE
DATE
USHER 1 First
Usher 1 LAST
Hello,
I'm having some problems setting this up right.
I have 3 databases.
Volunteers
FIRST
LAST NAME both primary
Movie Data
MOVIE
DATEboth primary
Attendance
MOVIE
DATE
USHER 1 First
Usher 1 LAST
USHER 2 FIRST
USHER 2 LAST
AUTOKEY PRIMARY
I have a one to many relationship between the movie and date between the attendance and movie data table. I have tried linking the name to the usher fields but I"m not getting the result I want. I want to be able to open up the volunteer table and have it show me the movies they have worked on. The movie data table will list who worked it, but the volunteer tables are not.
Any ideas?
Thanks,
Cody
I have to tbls which have indeterminate relationships and I'm told that ther is no unique index found why is this?
i am using emplyID on both tblCommission and tblSales both are number type.
I might not need the commission table as I'm using this is calculate commission as said im my previous post
at one point in my database I have two tables and for everyone record in one table I may have either 1 or 0 records in the other. How do I express this in access design? Since it's an optional 1 to 1 relationship I guess?
View 1 Replies View RelatedI was wondering if anyone could help me with a query. Basically I am doing (or trying to do) a small database to track childrens progress in my mums primary school. I'm doing this for free as the budget is rubbish for this type of thing and i dont have enough knowledge to ask for payment anyway! I want to get it right from the start and I think if I get the relationships right initially then I can complete the rest of it on my own. I did this at university (normalisation etc) but ive not used it since i left in 2002 and so have practically forgotten everything i ever did.
Basically, the child comes into the school and is predicted a level in maths, reading and writing. Then in the october, feb and july of each of the 2 years they are there, they are given actual assessment levels. They are also given a prediction level at the beginning of year 2.
So far I have one table with student no, surname, first name, gender, ethnicity, year group and SEN(special educational needs) - with the primary key being student no.
This is where I get stuck - do I go for a maths, reading and writing tables and split it that way - or on an assessment basis, so prediction yr1, october yr1, feb yr 1 etc etc. Or is this completely wrong? Am sooooo confused, so any help would be greatly appreciated.
The outcome I want is to be able to query a childs progress, so for example: show children who achieved a 1a in yr1 maths, and then out of those children, who achieved a 2a in y2 maths (as this is the required shift in progress set by government). I really hope that makes sense.
Thanks in advance
Nicola
I'm rebuilding my database from scratch now that I've learned a bit from the forums about developing a proper database and I want to investigate whether I have the relationships set up correctly, as I seem to be using lot of one-to-one relationship, which most articles say is very rare, and whether I am formulating many to many relationship correctly. Here is a screenshot of my relationship.
If I've understood this correctly, one-on-one is desired if you have a subset of data that is applicable to the main table only occasionally and is dependent on the main table's data. Have I used that correctly?
As for many to many relationship, articles say this is formed by having two one to many relationship linked together in a table. In my screenshot, you can see that tblAdvocacyVisit is the linking table. However, I've set GuestID as a one-one relationship whereas ClientID is one to many relationship, reasoning that since I do need to relate the Guestl Logbook data to Advocacy table for some queries I will be performing later, this will save me some work. I'm afraid that this may compromise RI or something like that. I can simply add a PK autonumber to tblAdvocacy to revert the GuestID as a one-many relationship, but can't see how that would not create more work in queries or so...
Oh, are the lookup tables supposed to be related at all?
TIA.
Edit- Updated the relationship screenshot.
I’m building a database of protein mutants we use in the lab. A lot of analysis is done on these proteins by various people and it’s becoming a bit of a nightmare keeping track of all the information per protein. What we need is a database. I’m getting a reasonable grip on it all I think with the excellent examples I have found on the forum but there are obviously areas that I have great difficulty with. The main problem for now is that many-to-many relationships are involved and I just cannot get them to work properly.
On each protein up to 8 different types of test are performed. To keep things easy I’ll call them A1 (for Analysis1), A2, A3 etc. These are done by different people from a group of 7. I’ve called them O1 (Operator1), O2, O3 etc. Each of the operators can do multiple analysis and the operators overlap. So e.g. O1 might be particularly good at A1, A4 and A7, O2 might be good at A2, A4 and A5, and O3 predominantly does A1, A4 and A6. What it comes down to is that each operator can in principle do each of the analysis so I have to give that option in the database.
What does work (but is not the solution as it introduces 8x duplication of data) is if I have a separate table with operators for each analysis. The same tblOperators is linked 8 times. There must be a way to link each of the analyis to only 1 tblOperators, probably with the use of a linking but I just cannot work it out from the examples I've found here.
Can someone help me on my way please?
Thanks,
Walter