General :: Managing A Many-to-one Relationship From One End
May 10, 2013
use of a query, or a report or it could involve changing the table structure.Say I have a many to one relationship, for simplicity we'll call the table on the 'many' side tblThing and the table on the 'one' side tblGroupofThings.When managing the records in these tables it is important that each Group of Things contains at least one Thing, but it isn't important that each Thing is part of a Group of Things.
Things and Groups of Things are constantly being added to the table. As I see it, the only way to manage this is from the Things table, or at least a form based on the Things table, as I am editing a foreign key for tblGroupofThings inside tblThings. However it would be easier for me to manage it from the Groups of Things end, so that my workflow goes as follows:
-I add a new Group of Things to tblGroupofThings,
-I then 'put inside' that Group of Things, the Things that belong inside it (i.e. make the foreign key field in those Things point to the Group of Things.
Managing from the Things end means I have to start of with the new Groups of Things which are 'empty' at the moment, decide which things need to go in them then swtich to Things and remember which Things need to go into which Groups of Things.all the talk about putting records inside other records when I'm really talking about foreign keys. I know that's not know it really works but to the user that's how it should appear to work.
I work for a firm who have a few thousand clients and we are wanting to go through a process of checking with them that the data we send them is going to the email address they would like it to go to. However in some cases we don't currently hold an address so we are contacting them by phone to get the addresses over the next few weeks.
The way I've chosen to do this is to use the excellent link between Access and Outlook to send an email to the address we have on file (or have gotten) with a very light form they can optionally fill in to update the email address if desired.All seems well in testing this with 2 exceptions:
1) I have 2 email boxes in outlook, my "personal" one and a group mailbox. I'd like the emails access sends to come from the group mailbox but cannot see an option to control this.
2) We're likely to send the emails out in tranches - is there a way to have a single table the drives the email process but some how have it know that we have sent the emails out for certain clients such that when I send a second tranche, it doesn't re-send to clients that have already been contacted?
I'm writing a task tracking database to be used during staff meetings. Each task should be assigned a priority, and no two tasks should ever have the same priority. Easy enough. Here's where I could use some suggestions. If I have a new task, what would be easiest way to manage that insertion (assuming it has a higher priority than the lowest item), without having to renumber all the other items in the table?
Having had little luck in getting any concrete information about effective ways to manage ODBC data, and after days of long trials and errors, I thought at least I could write up a summary of what I've found to work well for ODBC sources and hopefully others may be able to contribute to this.Disclaimer: This is a far cry from being an authoritative and is woefully very subjective, being written by me and myself, using a MySQL server and Access 2003. I do hope that others who are able, can contribute more information to make this somehow more useful for those who would like to use Access as a front-end client.There are three principal issues that must be considered when you are using a ODBC data sources:1. Numbers of connections and different flavor of connections.2. Size of recordsets and network traffic.3. Binding forms to ODBC data sources.As a starting point, one should read the whitepaper on Jet and ODBC Connectivity. (http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp ) This was for Jet 3.0, but should be valid for 4.0 as well. ODBC Driver and ConnectionsThe next thing is to understand what your ODBC driver is capable of. In a connection string, there is a parameter for "Options", which is usually a long integer. You should look at the ODBC driver's manual to ensure you have all options you need turned on. A good example would be to ensure that a certain data type is correctly mapped to Access's data type. In my case, I ensured that Big Integer (64-bit) were turned off because Access does not support this (and thankfully, I don't need it anyway). By far most important thing you want to look for is to ensure that the driver supports two things- Multiple statements and Active statements. Jet does try to pool all queries to the server into one connection whenever possible, but if the driver cannot support multiple statements over single connection, Jet will open another connection to submit a statement. Therefore, if you have a form that has a combobox, Jet will send two queries, one for form's recordsource and another for combobox's rowsource. In case of lack of support for multiple statement, Jet will need two connections. Even if your driver supports multiple statements, Jet may find it necessary to open a second connection if the driver cannot have more than one active statement. Active statement is when you fetch a big recordset and need to wait a bit for the full set to come over the wire. So back to that form with a combobox, if Jet find that the form's recordsource will take a while to be fetched, it will go ahead and open second connection to populate the combobox in order to allow for 'instantaneous' loading of a form from a user's POV.Wherever possible, keep numbers of connection to a minimum. As mentioned before, Jet will try to pool all statements into one connection wherever possible, but Jet cannot help you out if you issue a query using a new ADO connection, DAO ODBCDirect workspace, or create a recordset within VBA. If you want to avoid the additional overhead of another connection, you should use a stored query instead, and make sure it has a Connection string in its query property window set, so Jet knows that it uses ODBC data and pools it with its open connection to the ODBC server. The stored query can then be called within VBA without incurring another connection.DSN and DSN-less/ODBC and OLE DB connectionsThere are two ways to create connections, by using Data Source Name (DSN) or direct connection. To find right connection string, look at Carl Prothman's excellent website listing all possible connection strings (http://www.carlprothman.net/Default.aspx?tabid=81). Some people say connecting to DSN is better than without. Others say it's faster without DSN. One thing for sure is if you use DSN, you will need to distribute the DSN file to your users along with the finished database. I would recommend experimenting with both connection flavors before deciding which is better suited for your needs.To make a DSN connection DSN-less, look at Doug J Steele's example (http://www.accessmvp.com/DJSteele/DSNLessLinks.html) or perhaps this Add-in by Paul Litwin (http://www.mvps.org/access/modules/mdl0064.htm).Also, make sure you know whether you have different drivers available. I know for an example, some people prefer third-party drivers for Oracle over one supplied by Oracle themselves. Furthermore, some drivers are OLE DB which is supposedly better than ODBC (I say supposedly only because I am skeptical of Microsoft's promises of new and latest technology superseding a older technology, then dropping their claims and going back to older technology). Binding formsTo Jet's credit, it is quite intelligent in retrieving just enough rows to populate the bound form, and will continue to fill up the recordset periodically while a user is working on a row. However, Jet has some quirks where it can do something very stupid, such as asking for multiple full table scans. If you bind a form to ODBC table, Jet will do a full table scan. The simplest solution would be to add a WHERE clause to make the recordset smaller. But by far the better solution would be to set the recordsource's connection string to use ODBC instead of "(current database)". Jet will then query for keys, which it must have locally in order to manage a recordset, then afterward query a few rows at a time. If you can manage to keep keys smaller, all the better.Furthermore, if you want to use subform, do not use Master/Child linking fields. This makes Jet go ape-shit, issuing several queries to show tables and index which is quite unnecessary. Rather, leave the link blank, and set subform's recordsource with a WHERE clause to match the parent form's key, so Jet will only ask for rows that match the key only without asking for indexes and table status every time you move around.An additional benefit of making all queries for forms' recordsource an ODBC query is that you now have more control over how you handle those forms *with* Jet's intelligence. For example, you now can start transaction across multiple forms with subforms and commiting/rollbacking as you see fit, which would have not been possible using Access itself. I have been able to rollback the changes in two parent records and their related child records by issuing a SQL Pass-through query which simply says "Rollback;" and nothing was changed for any one of records, just as expected. If you have a combobox or listbox on a form, this will mean another query in order to fill in the rowsource. Ideally, you want to keep some tables local to front-end clients, especially for tables that will never change (e.g. a list of states or provinces for example). For tables that may be updated peridocially but is otherwise select-only, you need to decide whether you want to make it a dynaset or a snapshot. For a small set, snapshot is faster, but for a larger set, dynaset is faster. You will need to experiment with the rowsoource to ensure that the network traffic and time to load the rowsource is satisfactory. One problem is that Access won't accept an variable (at least I have been unable to do so) for a stored query's connection string, so if you need to change a connection string (e.g. you want to use different set of options, perhaps?), you would need to do this by hand, or at least write a function to loop through queries and updating the connection strings. Haven't tried that yet, but would imagine this is very possible.Keep an eye on SQL log when developingYou definitely will want to have the server write a log of what queries it has received from Jet to give you feedback in ensuring that Jet doesn't do anything stupid. This has told me far much more about Jet than working within Access environment. Unresolved issuesThere is only one thing I haven't yet worked on- Sharing a recordset for different controls. Suppose we have a form with a combobox and a subform, both which use same table as a rowsource and recordsource, respectively. In this case, Jet will issue two separate query to the server, even if they may use same recordset. If anyone has been able to show how to get such controls to share recordset, that would be cool.Also, I'm a bit worried about scattering connection strings all over the place, especially that it will contain a password. As I see no point in asking users to authenicate themselves every time they use a query with a ODBC connection string, given that variables can't be used in query's connection string. Would MDE make this less of a problem (I do not know if password still can be plucked out from a hex editor?). A possible solution is to synchronize Access's security with the server's security, because Jet will try to use Access's user & password for initial connection, and if a call to retrieve password was made along with a module at startup to 'fix connections' along with updating the connection string with the entered password, this may help somehow with keeping the password secret? Does anyone have more information on that?External LinkA useful FAQ (http://archives.postgresql.org/pgsql-interfaces/2000-07/msg00193.php) for linking Access with Postgresql which may be useful in giving you some ideas of how you can work with Access. If anyone wants to contribute to this, please do feel free to do so. If anyone finds anything that is dead-on wrong, please give me a good can of whoop-ass- I hate to lead blind into ditch, so to speak. :)
I have an estimating database, the "main" database is on my desktop, then it is also on a Notebook and Laptop and goes out with the bidders. At the end of the day, I want to sync all 3. I may enter info throughout the day on my desktop (which needs to sync up to the other 2) and vise versa. Using SharePoint is not an option as the Notebook and Laptop will not always have internet access.
I have a database for managing time related contracts.
I need to be able to run parameter queries but if I query the data by start date then and contracts which are current but started before the first date entered in the query (this is the same for end dates) are not shown.
I am attempting to create a database for managing nonconforming product internally.
Different stages will need to be assigned to an individual within the organisation, is it possible to have a lookup value in my table which draws its information from active directory?
All the users are obviously in here and managed already, I dont want to have to create and manage a separate list of users just for my database.
I am currently studying Robert Jennings book entitled Using Microsoft Access 2007 Special Edition. In reading about specifying of the sort order and top value limits established by inner joins (pg 455), the author makes this statement.Access displays query result sets in the order of the index on the primary key field of the table that represents the one side of the top most one-to-many relationship of query tables.An example of a query with Orders, Order Details, and Products tables displays rows in productID (the primary key field in the Products table) sequence. He says that it does so because the Products table has a one-to-many relationship with the Order Details table and indirectly with the Orders table. I do not understand why the Products one-to-many relationship with the Order Details table is ranked as the top most relationship in that the Orders table also has a one-to-many relationship (OrderID is the primary key field) with the Order Details table and an indirect relationship with the Products table. The Order Details table has two primary keys (ProductID and OrderID). My attempts to change the sequence in which the table relationships were formed as well as the location of the tables in the database had no effect on the result.
I have a table that holds UK Postcodes and a customer table that holds customers.
I am trying to create a relationship between the 2 so when I enter a postcode in the customer table this is then related to the postcode table.
The main problem I have is that there is a lot of duplicate postcodes in postcode table so the primary key is simply a number as you will see in the picture.
Enforce referential Integrity and Cascade Update Related Fields
Thus, it is possible to change the name of the state or city, but can not be deleted until the state is associated with some of the city.
I also made a form for the "state" in which is the list that contains a list of all states. The name of the list is "lstState". So that I can delete the "State" I make a button in form and I use the following code:
Code: Private Sub DeleteState_Click () If IsNull (Me.lstState) Then MsgBox "Select the state you want to delete", vbCritical else DoCmd.SetWarnings False
[Code] ....
Everything works fine when the state is not assigned to any one city. But the problem arises when trying to delete a state which is assigned to the city, that is when I select this state and click on the delete button then the state is not deleted - this is ok, but without any message told why the state is not deleted and that's the problem.
My question is how to make the code that the user receives a message that such State can not be deleted because there are cities that are associated with it.
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?
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)
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.
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
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
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.
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.
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?
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.
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?
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.
I am having trouble associating the relationships within a database to accomplish what I need. I don't know if I am over thinking or I just plain old don't get it. So, I ask if someone can draft this database up rather easily and post it for me or send me in the right direction to learn quickly.
This is what I am looking for: A table with people’s first name, last name, title, job position. That is it. I can do that and have done that. No problems.
Another table that has amplifying data on that person such as: date of birth, SSN, date employed, comments, etc.
What I envision is, creating a form to add the names, title, and job position of the people to the table.
Then I want to create another form that puts the other data associated with that person. This other form should have the first and last name from the first record to "view" only so the correct data can be added to the correct person.
That is it; I hope someone can help me out. I have been working on this for sometime and just can't seem to get it.
