Query Or Relationship? Some Guidence Please

Mar 23, 2005

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

View Replies


ADVERTISEMENT

Guidence/advice For Tables

Apr 9, 2006

Hi,I am trying to create a database but I am a little stuck, could do with your input.Here is the table structure so far:member_tblid AutoNo Pkmemberid speacial number given to each membertitle fk - linked to title table ?fnamemnamelnameaddid fk - linked to a address table ?datejoinedphonemobileemaildobrole fk - linked to role table address_tblid pkhouse no/namestreetadd1streetadd2areapostcodetown/citypostcodeaddress_r_tblid pkaddid pkrole_tblid pknamerole_r_tblerid pkmid pksdateedatefamily_tblfid pkphonedate joinedemaildefault contact - not sure if this should be here or fmembers_tbl.fmembers_tblmid pk fk - using info from members table ?default contact - not sure if this should be here or family_tbl.My issues are this title - Should this be a separate table. address - should this be a separate table. role - not sure how to set the id field for this, I have read that autonumber for a small number of items in the table is not worth it.Would be greatful for your input.

View 3 Replies View Related

One To Many Relationship Query

Feb 15, 2008

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

View 2 Replies View Related

Relationship/Query Problem

Jul 29, 2005

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

View 3 Replies View Related

Query / Relationship Issue

Nov 14, 2007

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.

View 5 Replies View Related

Relationship Woes With Specific Query

Mar 9, 2006

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.

View 2 Replies View Related

Diplaying Results Of A Query With A One To Many Relationship

Sep 14, 2005

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.

View 2 Replies View Related

Query - Show All Records On The Many Side Of A 1-many Relationship

Aug 29, 2007

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

View 8 Replies View Related

Create Query With Multiple Criteria From A Many To Many Relationship

Sep 23, 2004

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!

View 1 Replies View Related

Queries :: Update Table With Relationship To Totals Query

Oct 21, 2013

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!

View 3 Replies View Related

Add Single Record To A Table Using Append Query With One To Many Relationship

Nov 21, 2014

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.

View 2 Replies View Related

Queries :: Append Query - Unique Records To Create Relationship

Mar 22, 2015

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.

View 3 Replies View Related

Handling Repeated / Mirror Records In Query As A Result Of One To Many Relationship

May 5, 2015

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

View 3 Replies View Related

Make-Table Query With Relationship To New Table

May 31, 2005

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

View 1 Replies View Related

Relationship??

Jan 14, 2006

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.

View 8 Replies View Related

Relationship Help Need

Feb 28, 2007

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

View 1 Replies View Related

Relationship

May 16, 2007

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

View 2 Replies View Related

Many-to-many-to-many Relationship

Jul 25, 2005

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?

View 3 Replies View Related

Relationship Help Please

Aug 14, 2005

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

View 5 Replies View Related

Many To One Relationship (I Think...)

Aug 19, 2005

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.

View 2 Replies View Related

Relationship Help

Aug 30, 2005

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

View 1 Replies View Related

Relationship Help

Aug 30, 2005

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

View 11 Replies View Related

Relationship Help

Sep 11, 2005

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

View 1 Replies View Related

1 Or 0 Relationship

Nov 4, 2005

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 Related

Relationship Help!

Mar 3, 2006

I 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

View 3 Replies View Related

One-to-One And Many-to-Many Relationship

May 10, 2006

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.

View 14 Replies View Related







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