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 Replies
ADVERTISEMENT
Mar 15, 2006
I am trying to build a query which will find the costs from a set of tables where the client has costs.
The client has a cost on booking a fee, he has a hotel room cost and a room facility cost.
My query brings up what I want but each result is repeated 3 times.
I don't know why, can someone correct me please.
SELECT DISTINCT Addresses.ID, Holiday_Bookings.Initial_Cost, Room_Facilities.Cost, Rooms.BasicCostPerNight
FROM ((Room_Facilities INNER JOIN ((Hotels INNER JOIN Rooms ON Hotels.HotelID = Rooms.HotelID) INNER JOIN Stops ON Hotels.HotelID = Stops.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID) INNER JOIN (Routes INNER JOIN WalkTypes ON Routes.RouteID = WalkTypes.RouteID) ON Stops.StopID = Routes.Arrive_In) INNER JOIN (Holiday_Bookings INNER JOIN (Groups INNER JOIN (Clients INNER JOIN Addresses ON Clients.ID = Addresses.ID) ON Groups.ID = Clients.GroupID) ON Holiday_Bookings.ID = Clients.ClientID) ON WalkTypes.Walk_TypeID = Groups.GroupID;
View 3 Replies
View Related
Mar 5, 2014
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
View 1 Replies
View Related
Oct 11, 2014
I have created a query that is designed to return a count of how many records there are in various tables. There are 10 expressions in all, so when it is run I am expecting to see one row of data with each field populated with the number of records.
It does do this, but the data is repeated over line after line (see the attached picture)
View 4 Replies
View Related
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
Nov 7, 2007
I am really new to using access and I have a massive table 16000 or more entries. What I need to do is take this example data :
VPIAddress
001:99_97_AO
001:99_96_AO
001:99_0_AI
001:986_51_CI
001:986_50_CI
001:985_51_CI
001:985_50_CI
001:984_50_CI
001:983_51_CI
001:983_50_CI
(This is just a tiny fragment of the total data)
The 001: to the first '_' is the important bit this is defining a "Router" what I need to do is total up how many '001:*_' there are, not including duplicate numbers.
Firstly if I understood this (http://www.access-programmers.co.uk/forums/showthread.php?t=92001) post correctly I need to have all the VPIAddress the same. If this is correct is there a way to get access to delete the data after the first '_' ? (I would of posted in this tread but i didnt think that would be right, if im wrong mods move the post :) )
Secondly will the info in that post help me to reduce the data into this :
VPIAddress
001:99_
001:986_
001:985_
001:984_
001:983_
So that I can then see how many Physical "routers" there are
If any one can help me and tell me that my idea will work then tell me how to do it id be extremely happy!
Even if any one says that wont work and gives me the correct way to do it and does a virtual "Clip around the Head" and call me a Daft Bugga I would still be happy because I wouldn't have to trawl through the entire 16000+ entries deleting and modifying.
View 4 Replies
View Related
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
Aug 18, 2013
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I'm using access 2003.
View 4 Replies
View Related
Nov 8, 2005
i have a lists of emails+domain names eg camae@yahoo.com. im trying to sort them in reverse order but "reverse" seems not working in access. anyone knows how to do them or i was thinking of sorting the list begin with the domain names (which is after the '@') but i dont know how. ive tried using "like "*@[a-d]" but the result displayed is not sorted in alphabetic order. sorry im a beginner. thanks in advance
View 8 Replies
View Related
Oct 28, 2004
My printer does not have a "print mirror image" setting. Is there a way to create a report that would show on the screen as a mirror image?
The application here is that the informartion would be printed on iron-on material and then transferred to a t-shirt as a souvenir. Currently, I do a screen capture, crop and flip the image in Adobe and then print it, but I would like to set it up so that anyone could use the database without my intervention nor using a second application. Reducing steps users need to take is a must.
Thanks.
-Joat.
View 5 Replies
View Related
Oct 12, 2013
I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies
View Related
Aug 14, 2014
I have a drop down box where I select a Retailer. When this choice is made I want the last invoice entry to appear in a text box. I have attempted this by creating a query which contains the top 1 invoice in descending order. My dropdown box Event updates a hidden List box using List677. Requery and List677 has a SELECT statement in Row Source
So far so good this bit works
I want to use this value as a default in a Textbox. I thought I could use the same trick to populate the Textbox as I did the Listbox so i tried Text232. Requery after the List677. Requery in the Event above. But no it does not update. The only time the textbox updates is if the Listbox is in Focus and as I said this List box is Hidden. How can I get the textbox to change in real time... I would use the Listbox but i need to be able to change the value of the contents to run reports, I just want a default value.
textBox Default Value: =[List677]
textBox Control Source: =[List677]
List677 Row Source: SELECT LastInvoice.Invoice FROM LastInvoice;
View 1 Replies
View Related
Jan 16, 2006
I have to give a three paramters queries. But i have to handle null responses also in such a way that in one parameter or two parameters i don't want to enter value and the result should bring all possible values with the parameters I entered
Like customer, status and order number in different fields
when i don't enter order number the result should bring all values in status and customer
View 1 Replies
View Related
Aug 28, 2012
I'm trying to split out the first name from a GivenNames field which contains first and middle names. Some values in the GivenNames field have just a first name. I've tried the below formula, but still get some #Error responses where only a first name exists in the field.
My formula:
Given Name: IIf(IsError(Left([GivenNames],InStr([GivenNames]," ")-1)),[GivenNames],Left([GivenNames],InStr([GivenNames]," ")-1))
View 2 Replies
View Related
Aug 19, 2013
(I'm not sure if this belongs in the macro or query forum)
I have a macro that opens 15 queries and ends with opening a report.
The final query asks the user to type in beginning date, then an end date. Now when there's a data mismatch (i.e., he spells feburary), the macro halts. No harm no foul, they can just click the command button again, but I'd rather not.
What I'd like to do is place a msgbox (i.e., "Check Your Spelling") and then reroute them back to the query.
View 2 Replies
View Related
Mar 17, 2007
Hiya all,
I know the title is confusing, and so is the problem (to me at least). Basically, I have a database with an invoice form. Each record is an invoice, and on this form there is a subform of additional charges. The tables are linked by InvoiceID. It worked smashingly when I was playing around with it, until I finally tried an invoice with no additional charges. The queries for my reports display no records (because there are none). This is an issue, because I need a summed up amount of the charges to complete the invoice. I cannot do this on the report, because additional expressions are used on the charges.
Basically, my problem comes down to this: I have no additional charges, so certain fields in my query don't exist, therefore the entire query record won't display. I was screwing around with NZ() and IsNull() before I realized that a null record and the absence of a record are different.
I've been searching, but can't seem to find the right keywords to produce a fruitful search. If anyone can point me in the right direction or give me some words of advice, I'd greatly appreciate it. And yes, as far as I know, my database is fully normalized.
View 11 Replies
View Related
Mar 19, 2007
Hello all,
I have 3 tables:
1. customer
2. DVD collection
3. Borrow
The aim of this system is to create a home borrowing system.
The customer table has a one to many relationship with the borrow table, while the dvd collection has a one to one with borrow. I can create a new book with no problem, and even view these on a form. Here my proble arises.
I ahve another form to return a DVD. I view the records from the borrow table, and once found what i want there is a button which deletes the record from the borrow table. Sadly, when i press this button, not only does it delete the record in the borrow table, but it will also delete the record from the DVD collection table.
I have tried everything i can think of, and i don't want to change to change the design as i have found this mnethod very easy to generate reports and the like. Any help??
Thanks
EDIT: the button i used was from the wizard that comes up when you create a button.
View 2 Replies
View Related
Mar 27, 2013
I have record like below:
id s q
1 11 11
2 14 15
I want to sum record 1 and record 2 and put them in next record.
View 2 Replies
View Related
Feb 27, 2006
Hi Foks,
I'm sure this has been asked and answered many times but I'm not having much success in searching for it as I can't think what to put in the search engine. My apologies in advance for the longwindedness of this posting.
Anyway, I have a database for tracking projects. As the projects evolve, they are sometimes amalgamated, split, dependant on other projects or implemented under a common contract with one or more other projects. Thus, a project can have none-specific relationships with multiple other projects.
To accomodate this:
I have a juction table, tbl_Related_Projects, with fields Project_ID and Related_Project_ID set up as a joint PK.
The PK of tbl_Prj_Details has 1-to-many relationships to both of these fields to create a "circular" many-to-many relationship between the tbl_Prj_Details.Project_ID field and itself.
A comment field in tbl_Related_Projects describes the nature of the relationship between the projects.
;) I hope I you're still with me and following this!;)
On my fdlg_Prj_Details, I have a subform for tbl_Related_Projects. A user can select related projects from a drop down list (Combo Box). So far so good.
Here (finaly :eek: ) is the question: When a user selects a related project (Project B) on the related project subform for Project A, how do I get the reverse relationship to show for Project B i.e. Project A is to appear in the related projects subform of the Project B record, along with the same comment?
I can't even begin to think how to achieve this but as it is a relationship issue, it must be what relational databases are all about and have been solved many times.
Currently a user must select the appropriate project in the related project subform for each record and enter the comment twice. The possibility and liklihood is that the relationship will not always be noted for both ends i.e. A will be related to B, but B not related to A.
:cool: Small caveat for any helpers here, I can copy, cut, paste and stumble/modify code, but at this early stage in my database carreer (ha!), I cannot write it from scratch - too many traps for beginners.
Having re-read this a number of times before posting, it is occuring to me that this is a more complicated problem than I thought it was. Many thanks in advance for any help and advice.
Regards,
Keith.
View 8 Replies
View Related
May 2, 2013
Any way to build something into a sub-query that says 'if no records are found, return '0' or some other string'?
Otherwise is there a way to make a master query ignore sub-queries if they return no records?
Allow me to explain in more detail: I have a series of sub-queries, most of these take the sum of several fields from a number of different tables, and I have a main query which combines all of these, to be used as the basis of a summary report.
These queries aren't a problem, but I have a few other essential queries which take the modal (most common....) entry for fields which aren't numerical. So I can't use the sum function.
Now, if all the sub-queries are working then so does the main query, however if one of them fails to find a result, then none of them show up in the main query. I don't know why.
The issue is that depending on the date range selected, some of the tables targeted by the sub-queries don't have any records at all, so when they are run they return nothing. The sum queries can handle this since they just return 0, but those searching for modal records just find nothing (not 0's and not null fields, just blank across all rows).
Here's an example of my sql statement for the modal queries.
Code:
SELECT TOP 1 Trends.Trend AS ModeTboxTalk, "1" AS [Key]
FROM Trends INNER JOIN [Toolbox Talks] ON Trends.TrendID = [Toolbox Talks].TrendID
GROUP BY Trends.Trend, [Toolbox Talks].TrendID, [Toolbox Talks].TalkDate
HAVING ((([Toolbox Talks].TalkDate)<=[Forms]![WeeklyReportSelect]![WeekBox] And ([Toolbox Talks].TalkDate)>[Forms]![WeeklyReportSelect]![WeekBox]-7) AND (([Toolbox Talks].SiteID)=[Forms]![WeeklyReportSelect]![SiteBox]))
ORDER BY Count([Toolbox Talks].TrendID) DESC;
- FYI the weekly select form is where users select the week and site they want to report against. So it would be really peachy if I could tell the above to say something like 'no trend this week' if indeed there were no records.
View 6 Replies
View Related
Dec 30, 2013
I have a db using 3 large tables with 1:1 relationships. Before you question my normalization, I stress that these tables are strictly used to temporarily store user input information for a highly automated form with several hundred fields. All of the bound fields enable the form to be reopened and retain the information originally entered. The information is then transferred to a report and converted to a .pdf where it is filed; these 3 table's records are deleted weekly with a delete query. Other than a few combobox row source queries, these 3 tables are not connected to the rest of my db. At this point the form is functioning perfectly.
What I am trying to do is save about 3-5 of the partial (records) on 2 of these tables for indefinitely, no longer needed, or overwritten. Reason being is that partial information (records) on this form can be recalled and duplicated into a new form, saving the user time from having to completely re-enter some fields that will not change week to week.
So far, my best idea is to have 2 duplicate tables that store these records (based on a checkbox on a scheduled query event) that can be recalled and written back into the 2 main tables with a new primary key when needed. Append queries only work with one table at a time, and I need to maintain the integrity of these records 1:1 across the 2 tables with their primary keys intact.
View 5 Replies
View Related
Mar 21, 2014
See attached picture where I am stuck at?
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.
View 2 Replies
View Related
Oct 11, 2007
I have qrys that show current info and also repeated info. A request has been brought to me to now count how many times the repeated data has been repeated.
The data is updated daily, so the repeated record would need to increase in the number of it's occurances by 1 each day.
Any guidance on how to accomplish this is GREATLY appreciated! :)
View 5 Replies
View Related
Jan 16, 2006
when i change a form so that instead of single form its multiple forms it seems to only want display 30 instead of the 62 that exist. is there a quick fix this?
if not is there a way to make it display the next 30?
as now it says "record 30 of 62" when it gets to the bottom of the form... ;/
any help is much appreaciated ta. <3
View 3 Replies
View Related
Jun 3, 2013
All using access 2010. Here's the situation. I built a searchform according to datapigtechnologies video. Used a query with criterias on fields i want to search on the form: ex.
Code:
Like [frm]![frmMyform]![MyField]
It worked fine i thought until i ran the same query outside the form without the criteria field. I filtered the query for the same result and the one used on the search form did not return all data records for that particular result set. Ex. on the searchform; I select the fields I wanted to filter then ran the query. I come up with only 9 records when it should be 18 I get with running the exact same query without the criteria and manually filter the results.
View 13 Replies
View Related
Mar 6, 2006
Hello All,
In my table, I have some records that are duplicated (aside from the primary key).
How can I select only one record from each set of duplicated entries? (There may be more than 2 of each of the duplicated records, but I only need to pull one of them...doesn't matter which one.)
i.e...Like a 'Find Duplicates' query, but only returning the top 1 primary key from each duplicated set.
Thanks,
Sarge.
View 2 Replies
View Related
