General :: Displaying Data In Many To Many Relationships
Aug 26, 2014
My problem is displaying data in many-to-many relationships.
There are three main tables: tblShops, tblOwners and tblMarketingEvents.
These are linked through two junction tables creating two many-to-many relationships:
1) Each Shop may have more than one Owner and each Owner may have more than one Shop.
2) Each Marketing Event may reach more than one Owner and each Owner may be reached by more than one Event.
Now I want to display for each Marketing Event:
1) the data of that event, (tblMarketingEvents)
2) plus the owners reached in that event, (tblOwners)
3) plus all the shops owned by the owners reached in that event. (tblShops)
Preferably, I want to display more than one record at a time without repeating any information.
Is there a way to display the data like this?
I tried a query: it gives the right data but does not display it as I need. It repeats the data from tblMarketingEvents and tblOwners for each shop.
I tried a form with subform: it can only display one record at a time and is hard to work with (printing etc)
I tried a report with grouping levels based on the query: Again, right information, wrong display. I need to group according to the records of the MarketingEvents table and not just according to a single field.
I Have this graph that needs to be run from a combo box selection. it doesnt seem to want to display any information at all. i have attached my database.
I want to display that query data in a Pie Chart, one chart for each theme with a section of pie for each Type (with the value of the count determining the size). Unfortunately my mind has gone blank. Everything I try does not appear correctly, some queries also require an repeated entry of the parameters but even if I do that it doesn't display correctly. I think I need to write another query to the format:
I have tried to figure out what the difference in functionality would be between 1 and 2. In the latter I have just created a series of relations between the last two tables (but that structure could be used throughout the whole DB instead of hanging it together with one relation between tables as is the case for the rest of the DB in this picture).
So there can be three different situations with this table structure: - linking all tables together via multiple relations - linking all tables together via a single relation - a combination of both, such as in the images.
Why would someone choose one versus the other? "Which is better in what situation?"
I've been fiddling about for a while now, but in a structure such as this it doesn't seem to make a difference. Or maybe there are differences in use/design, but on a level that I have not explored yet.
Could anyone explain this to me a bit? I have multiple books, all of which handle basic relations but I have not yet seen an explanation for this question. I think understanding the whole relationship topic is critical to be able to see other pieces of the puzzle fall into place later on. And as of yet, this is really fuzzy to me.
I have a table which has a yes/no field, in the table everything displays as either Yes or No, unless you click on it to edit it, whereby No become 0 and Yes becomes -1.This isn't a big problem as users can only access forms. On the input form this is a check box, checked for Yes and unchecked for No. This used to be a drop down selection box of Yes and No and the same problem was occuring, so I know the problem doesn't relate to the check box.
I have a second reporting form which has multiple combo boxes to filter down results in a subform. In the subform the field correctly displays as Yes and No, however, the filtering combobox for this field has the options 0 and -1.
The combobox is set up as Yes/No, the table is Yes/No and the results are Yes/No. There is no other formatting or input options that can be changed to tell the database this is a Yes/No field.How can I get this combobox to display as Yes/No? It's causing a lot of confusion with users as they are having to select 0 or -1.As a note, I've already tried the following:
-Deleting the combobox and starting again. -Deleting the table and combo box and starting again. -Deleting all tables, queries and forms which use this field and starting again.
None have worked, this seems to be something Access is doing rather than an error in what I've done.The filter form uses this code, which may be the cause:
Code: Option Compare Database Option Explicit Private m_colCombos As Collection Private m_strFilter As String
I a a new user to Access having only every ceate a couple of small databases using the wizards. I have just started a database where I have created my tables and I am now creating a form to display information. The problem that I am having is that when in Design mode I can see all the information and fields that I have used to create the form, however when i save the form then opening in display mode I do not see any of the fields, revert back to design mode and hey are all there.
I have created a table with name "Main". It consists of fields ID, Author, Title, Abstract, Page and Published.
I have created a form named "MFORM" with text boxes Author, Title, Abstract, Page and Published,and then I created a search and clear parameters buttons below the text box.
Then I dragged my Search Query into the bottom of my form MFORM and named it as "All Results".
When I click on search then the results are displayed in the new tab instead of bottom of my main form in Search query.
If I enter author name as Mike and then click on search button then results should display all the details related to Mike on the bottom of my form.
All Results [At the Bottom of my page]
Should display all the details related to Mike.
I had attached my database. I just need the search results at the bottom of my main form in stead of displaying in new tab.
I'm trying to create multiple relationships between the same two tables, but I run into problems every time I try. I'm using Access 2007.
Specifics: I have a table with information on meetings and there are two hosts. So I have fields Host 1 and Host 2. I have a second table that lists possible hosts and their personal information. I have a relationship between table 1 "Host 1" and the Host in table 2; I cannot create another working relationship between table 1 "Host 2" and the Host in table 2.
how I can get that to work? With just the one relationship, I can go to table 2 of the hosts, click on their name, and see all their meetings.But if I add another relationship, it takes out all of the information. I've been working on this for over an hour,
I'm wanting to create a way of searching through and displaying a large number of pdfs. These will be of different lengths and most will have images embedded in them. Each pdf will be categorised using a variety of fields to enable fairly sophisticated searches. I then want to link this database to a Joomla CMS website.
how to export Access' many-to-many relationships in excel.
My database is a Project portfolio management tool. One project has many different fields, some of which can only be single values (one-to-one, easy to export to excel) and many others are multiple values fields, built as many-to-many relations (through junction tables).
One usage that was not specified at database creation time was the ability to export the portfolio to excel, so non-access-savvy users can browse, filter, sort and play with the portfolio however they want.
If I build a report, it will contain as many sub-reports as there are junction tables, rendering it un-exportable to excel. A Form would have to be continuous, barring the use of sub-forms for the m2m relations.
Building a query will generate many lines per project (as many as the most populous multiple field), making the excel sheet nearly unusable (in my users opinion, and here, the client's the king).
The best route I have been exploring so far involves "transforming" the multiple fields, so a the different "rows" become additional "columns".
You know the scenario where people keep working in an old version of a database, while you work on an update... leaving you with a new database, but old data?
This concerns a database, containing about 20-25 tables all linked together. I was asked to make an update. In this update 3 new tables were included and here and there a few of the old tables got news fields, nothing crucial and a lot of forms were changed.
The current situation: Database 1: contains new data, misses a few tables, is missing some fields, forms and queries Database 1.1: contains old data, but some new extra tables, added fields, forms and queries
I would like to know if it is possible to import all data from 'database 1' into 'database 1.1', so that all linked information between the data is preserved, all database relations are maintained.
If I import tables from Database 1 into database 1.1, tables just get added, not replaced. If I delete or rename tables, I loose my relationships.
I would like to know what is a common and effective way to get all the new data of an old version of a database (where people keep working in while you work on an update), into the new version of the database, that currently contains old data.
I have two tables to track our engineer visits, one tracks the visit as a whole and the other tracks the individual instruments the engineer worked on during that visit. This way I can track visits to customer sites separately to the visits made to an individual instrument.
Right now, I create a visit and then add Visit Lines (containing the details of the instrument visited). These instruments, or 'Visit Lines', are being displayed in the Visits Table via a related items box. Visit lines are associated with Instruments in the Instrument Table via a serial number lookup. All this works great...
However, I want to display both the serial number and the instrument description in the related items control in the Visits Table. Since the Visit Lines table only has the instrument ID lookup and not the instrument description I can't display it in the control.
I need to either:
a) Create an instrument description in the Visit Lines table as a lookup and have this automatically pull in the description based on the serial number the user selects.... which I can't figure out how to do - it's just an autocomplete.
b) Create an instrument description in Visit Lines and have a macro grab the corresponding description from the Instruments table based on the serial number input - but just for this record...
Basically, its a database for an entertainments agency, I've got a form for 'Artists' which shows the performers on the agencies books. I've tabbed the form so one screen shows the artist details and I want the second screen to show the bookings that the specific artist has.
I've linked to the 'bookings' table on the subform successfully, but I can't figure out how to filter it so it just shows the bookings that the specific artist showed has.
i.e.: I want it so that The Deltatones booking tab ONLY shows the deltatones bookings.
I have created a 6 search boxes with names Author, Published, Language, Title, Keywords and University. If I enter "A" in Author search field then it should return all the 6 fields which contains "A" in Author field. It worked well.
My Requirement :
The column published is the year. In search box of Published if I search for 2000 then it's returning all the 6 fields which contains "2000" in Published field.
But most of the people doesn't know the exact year in which the book is published so they need some favour like, If they search for 2000-2010 in Published field then it should return all the fields which are between 2000-2010 in the published field.
For this do I need to create 2 extra text boxes and name them as Starting year and Ending Year? or else we can achieve this in single text box as xxxx-xxxx?
Can't figure this one out. I am trying to get my query to show me which company's have not sent a supplier any money during a certain period.
However if I have the following data
abc company 1/10/02 £5 1/12/02 £10
an i search for any company who has not sent any money between 2/10/2 and 1/11/02 abc company does not show up because there is no data between these dates. If I had a record 2/11/02 £0 it shows up. So what criteria or query can I use to show this up?
I recently added a couple cascading combo boxes to one of my forms and now the data from the underlying table that has already been entered does not show up unless I go in and reselect the options.
I have a relationship between Market data table and Trade table which is One to Many. Market on the One and Trade on the Many. However I just found out that I am unable to enter information into the Trade Form without entering information into the Market Form first. I would like to enter info. into Trade Form/tables independent of whether I enter info. into the Market Form/table, but I still need to have the relationship of One Market data to Many Trade date.
I have 2 excel sheets that I have imported as external data. I created a relationship between the 2 tables on part# field that is common to both. The relationship is to take all info from A table and only those from B table that match. The part# field is set to text in both tables. When I run a report that merges the 2 together a significant # of parts in my field change to a negative number. For instance the part# in both tables are 12345678 it is changed to a random -3452345 number..
Using ms access 2010, I created a table called all items contains the ID, item name, item code, item price.
Second table I created called orders, contains item1,Q1=quantity,Tot1=calculated field.
Then a form to fill the orders table with a combo box for Q1 (gets the value from a table called numbers), second combo box for item1 (gets the value item code and item price from the table all items), and a text box to calculate the total of the Q1-item1(item price) All working perfect except the combo box for item1 ends up displaying a different item code that's only if the items were at the same price, for example, I choose latte from the combo box gives me correct item price but the code is cappuccino, only happening with items with the same price.
If I set the property of the combo box Bound Column to 1, I get correct item code but calculation error, Bound Column to 2, I get correct calculations but wrong item code. how to get the combo box to display the correct item code?
I have done this before and can't remember how I did it and I can't fogure out how to do it. I have a form with textboxes that are for displaying info to the users. I want to lock them so that users can not click on them or high light the fields. So basically the user can only click on fields I want them to.
I am maintaining an Access 2003 application that is running on Windows 7 64Bit Enterprise OS setup.
I have a form in the application that displays a number of records with each record having a unique id and a field called ClockHrs which is stored as a Number (Long Integer) type.
I have a textbox that displays the sum of the ClockHrs field.
The textbox contains no code behind.
The Control source property of the textbox is set to '= SUM([ClockHrs])'
ClockHrs 10 10 30 15 10 15 25 === 115
The textbox displays the result of the sum 115 in scientific notation as 1.15 +02 .
Not sure the title is the best way to describe my problem. lolWell i have only been working on Access for a few months. The database i am working on keeps track when employee have been trained in a certain SOP document. I have a table for employees names and info, a table for SOP names and info, and a look up table that includes both employee and SOP with the date that they have been trained. Is it possible to create a report that would display what they did not train yet? Forgive me if its a stupid question and any help would be great. Thanks for your time.Aloha
I have a table of customers' data and another with the ISO country codes. They are joined. The country codes appear in the customers' table. The main table looks like this:
http://lemmikki.uw.hu/countries.JPG
On the form I would like to display the country's name instead of the ISO code.
