Hello,
Trying to put together a query that has all my data. However when I get to the this table the query no longer works, has just the titles. When I add tblCallCredit the query stops working. If I remove that table I get the data that the query is supposed to fetch.
I am not sure if its a relationship issue or something else which I dont know about.
Any clues?
Attached Image.
Table: User
Column(s): UserId
Table: Classes
Column(s): UserId, ClassId
User -> Classes is a one to many relationship.
In Access is it possible to create a query that outputs 1 record per UserId so that the output would look like:
UserId ClassId
102 2, 45, 6, 8
103 1, 44, 33
111 34, 2, 32
Since the ClassId can return multiple records the output would be comma deliminated and only return 1 record per UserId. Is this possible within an Access query? Also, if so, how would you construct the query that had another additional table with a one to many relationship?
Any assistance is appreciated!
Best, Fezzik
Sorry for posting yet another thread on many-to-many based queries, but mine aren't working and I need some pointers. I have downloaded and studied Pat's example of the many-to-many DB, and mine now looks fairly similar. Relationships have been properly defined, it's just that my forms dont seem to be behaving how I'd like them to.
Tables:
Papers
------
PaperID (key)
Title
Year
etc......
Authors
-------
AuthorID (key)
AuthorName
etc........
Join (for M-2-M)
---------
AuthorNum
PaperNum
These tables have been linked correctly and show the correct relationships (Papers and Authors are linked with one to many to the Join table)
I have a main form: Papers, data source 'qPapers'. In this I have a subform: Authors, data source 'qAuthors'. These queries are set up in the same way as Pat's, i.e., Authors uses the join fields 'AuthorNum' and 'PaperNum'.
However, when I open Papers form the Authors Subform displays all records and not just those linked to this particular paper. So in this way it doesn't function in the way that Pat's does.
Please help. I've studied Pat's closely and can't work out what I'm doing wrong. Many thanks
I have an existing Members Database Table fully populated with over 1,000 companies detailed. Within the database each company has a nominated person, normally the M.D. who has access rights to the member’s area of a website. We would now like to give the companies’ employee’s access as well. I would like to create a Parent / Child relationship between the two. I have a yes/no check box with the main table which signifies whether or not the company is still in membership. If the company was to cease membership by un-checking the box, access is denied to the employer and the employees. The employees table would only need to consist of First Name, Last Name, email address and password, and I presume a ID field to link the relationship.
Have I got that right or is there a better way?
I must admit, I'm confused as to the differences between Queries and Relationships, is that not the same thing?
Regards
Alan
I have a pretty standard relationship set up, with the following tables:
Customers: A row/Cust ID for each customer
WorkOrders: each customer can have multiple work orders (linked to Customers by CustID, individual ID is WOID)
Jobs: each work order can have multiple job records attached (linked to WorkOrders by WOID, individual ID is JobID).
As far as I can tell, they're fine and all other forms etc work, updating no problem, referrential identity is enforced etc...
Problems: When I go to make a "job allocation" query for subsequent form, I select: Customers, Work Orders and Jobs table (I then filter by location, but
this problem remains unfiltered also). They link up fine. The query isn't nonsensical either - it lets me add new data. BUT instead of showing all the customers and work orders for the location due for the location, it will ONLY display the records that have details in the Jobs table (the lowest in the relationship chain). Basically, If the jobs sections are empty or "unattempted", then NO details AT ALL will show up in the query. Which is a problem, because I want to see ALL the "unattempted" jobs in the area to allocate them, including customer data and work order numbers. I also need to have the jobs table present, so I can allocate a job date, a contractor etc.
I'm not sure what to do. As far as I can see, the set up I have IS very standard tables/relationships-wise. I have to work it out, otherwise I'm going to have to revert to some kind of 'super-table' (like in our old system) - which I am at loathe to do - I don't want to merge Work Orders and Jobs because that means a lot more typing for me.
Would cascade update in the relationships do anything? (it is checked).
I've been away from Access for awhile and I'm just drawing a blank on this one. Any help greatly appreciated. Thank you.
This is a general question.
If I have two tables say A and B with a one to many relationship and if there is no linked record in table B for a record in table A, if you run the query, that particular record does not show up at all.
Is there any way that this record can be shown in the query with no corresponding records in table B in the query (since there is no corresponding record)?
Thanks.
I have two tables which I pull records from. One table has a list of sections. The other table is a list of chapters.
One chapter has many sections.
I have the sections table populated, and want to show all of the sections, even if they are not currently assigned to a chapter.
Currently only the sections which have a chapter show up in my query.
I know this is really simple to do ..... I'm just drawing a blank on it!
Any help would be appreciated.
Gordon
Alright I've been picking my brain for the past hour trying to figure this one out...
I have 3 tables...
1) Retailers-RetailerID, RetailerName, address etc. (500 entries)
2) Distributors- DistributorID, DistName (10 entries)
3) Uses- RetailerID, DistributorID
Sample data is as follows for 'Uses' table:
RetailerID DistributorID
1 1
1 3
2 1
2 6
3 4
3 5
4 8
I am trying to create a search form based on a query which will return retailer information for those retailer who use multiple distributors.
For Example...There will be 2 text boxes and a user can enter a distributorID in these text boxes. If only 1 distributorID is entered, it will display records for that one.
I have tried the following but I have been unsuccessful:
SELECT Uses.RetailerID, Retailers.Address, Count(Uses.RetailerID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE Uses.DistributorID = 1
GROUP BY Uses.RetailerID, Retailers.Address
HAVING Count(Uses.RetailerID) > 1
Is this possible to accomplish with the way I designed this database? If so, can I get a little advice? I know this is a long question that can't be answered in full but I would just need to basics in order to be on my way.
Thanks in advance!
I have the following tables:
tblInvoice
tblInvoicePrePayments
They are related with a one-to-many relationship. The related field is "InvoiceId"
I want to create a query that shows selected fields from tblInvoice and the sum of field "PrepaymentValue" from table tblInvoicePrePayments.
I want to be able to edit the fields from tblInvoice in the query.
If I create a totals query (qryInvoicePrePaymentsSum) on tblInvoicePrePayments, using the fields InvoiceId and PrepaymentValue, and then create another query with tblInvoice & qryInvoicePrePaymentsSum, the fields in tblInvoice cannot be updated!
I have tables Account (PK acctnum) and Orders (linked via acctnum to Account). There are multiple orders per account that need to be billed individually. When creating an invoice I need to add the Account to the Invoice table and the Orders to the InvoiceDetail table. I use 2 queries to accomplish this. The first one though adds multiple records to the invoice table (because of the one to many relationship). I need to keep that so I don't bill anyone with no orders.
INSERT INTO Invoices ( InvoiceDate, AcctNum )
SELECT DISTINCT Date() AS InvoiceDate, Account.AcctNum
FROM Account INNER JOIN [Order] ON Account.AcctNum = Order.AcctNum
WHERE (((Order.OrderNum)=[Forms]![Account]![Order].[Form]![OrderNum]) AND ((Account.BillingCycle)="on discharge") AND ((Order.EndDate) Is Not Null) AND ((Order.Closed)=Yes) AND ((Order.PatientName)=[Forms]![Account]![Order].[Form]![PatientName]));
Is there any way to get it to only add one record? I've tried limiting it with several parameters to no avail.
I have created a linked Excel table in Access 2010 called 'tblExcelLinked' and I have a form called 'ASB Log Form' for the purposes of presenting the data in a more readable manner that is easier to view, plus link other fields of data that are not directly related to the 'tblExcelLinked'.
Because there is no unique ID in the 'tblExcelLinked' to create a relationship, I have created a table called 'tblASB', which allows me to add other table data linked from same d/b.
I now want to update the 'tblASB' with data from the 'tblExcelLinked', but only append new records from 'tblExcelLinked', but my inadequate append query is duplicating the records each time I run it, rather than just adding the new ones.
Once sorted my next challenge is a macro so that this runs automatically rather than being manually triggered.
I have built a contacts manager based on an Outlook export (I know I can link the two programs, but I wanted to customize the contacts a bit further). I built the database in a way that phone numbers, addresses, etc. allow for multiple entries for each person under the same column using a foreign key. The only difference is a field called "Type" which designates the entry for Home, Work, etc. The issue I ran into is when I queried the data I get multiple entries for the same record. As you are all likely aware, Access repeats the data in a query for each different case. For example if Bob Jones has a personal and work phone number, Access will show the results in a query like this:
1. Bob Jones 555-555-5555 Personal
1. Bob Jones 555-222-2222 Work
2. Jerry Smith 555-111-1111 Personal
If I kept the data in Access it wouldn't necessarily be an issue. However, I want to be able to export the data in an excel file. How do I get Excel to return the data so it looks like this:
1. Bob Jones 555-555-5555 Personal 555-222-2222 Work
2. Jerry Smith 555-111-1111 Personal
I've got a problem with a make-table query that creates a new table which is then populated with new values that relate back to the original table the trouble is the make-table won't run with that relationship in place.
Is there a way to delete the relationship then reinstate it after the query? or any other way round that someone can think of.
Thank You
Hi 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