Comparing 2 Lists - How To Setup
Jun 14, 2007
I would really appreciate if someone could help me with the following.
I have one list (3 million records)
Table = DNC
Field 1 - Area code
Field 2 - Phone number
I have another list that has say 1000 records
Table = Phone list
field 1 - area code
field 2 - phone number
What type of relationship do I need to setup in order for me to see which records in the Phone list table are not in the DNC table?
thanks for any help
Josh
View Replies
ADVERTISEMENT
Nov 23, 2013
I have run into some problems by comparing two lists. My project is to compare two lists and on the below listed criteria's and with the wanted results.
Criteria's:
1. Part
2. Colour
3. pcs.
Wanted results:
1. Equal match (same part, Colour and pcs (pcs difference above and equal 0)
2. Equal match (same part, colour but missing pcs. (pcs difference below 0)
3. Missing parts (difference in org. list - 1 and 2 query)
But already in the first part I run into problems. I do a query as below. (picture attached)
SQL code:
SELECT Parts_All.[Lego part], Parts_All.farve, Parts_All.Antal, [Set 7897-1].[Lego part], [Set 7897-1].farve, [Set 7897-1].Antal, [Parts_All]![Antal]-[Set 7897-1]![Antal] AS Part_Diff
FROM Parts_All RIGHT JOIN [Set 7897-1] ON Parts_All.[Lego part] = [Set 7897-1].[Lego part]
GROUP BY Parts_All.[Lego part], Parts_All.farve, Parts_All.Antal, [Set 7897-1].[Lego part], [Set 7897-1].farve, [Set 7897-1].Antal
HAVING (((Parts_All.[Lego part]) Like [All_parts]![Lego part]) AND ((Parts_All.farve) Like [Set 7897-1]![farve]) AND (([Parts_All]![Antal]-[Set 7897-1]![Antal])>=0));
This query is returning 2 of the same lines with part and colour but with different pcs. Why.?
View 7 Replies
View Related
Jan 23, 2008
Hello,
I have a main form where the user can click on a button that opens another form. In this form, the user can make selections from one listbox and they are copied to another listbox.
How can I save the contents of the listbox into a field that is part of the record on the main form? Can this be done?
I have a listbox on the main form where the selected items will be displayed and each record of the main form will have different values for the listbox.
Any ideas?
Thanks,
Mike
View 1 Replies
View Related
Apr 20, 2006
I am building a query for a university and the table i am working from has a column with every course run by the department.
How do i create a query that allows me to add a drop-down menu to the course instead of having to write out the course name everytime i want to query the number of pupils on a particular course.
View 1 Replies
View Related
Sep 2, 2006
Hello all, I wonder if anyone can help me.
I have two queries which are both similar. They consist of a list of names with a ‘count’ function to see how many times that the name occurs. Some of the names in the two lists are identical and some are different.
I am looking to create a query (or smoothing) that takes the two sets of numbers, matches them by the name (NameID). If the name does not exist in one of the lists then put a 0 (see example below). Once I have this I then want to create a stacked bar graph to show the results.
Example:
Smith, Iain23 7
Bowman, Dan12 2
Rixon, Simon06
Cooper, Lee10
Jones, Andy21
I am very new to MS Access so please make any explanation descriptive.
Any help with is will be very grateful.
Harold Clements
View 3 Replies
View Related
Mar 5, 2007
I have two list's of names. I'm able to run a query to find the matches but the ones that don't match i also need to id?
For ex. List a has 200 names List b has 130 names, how can i find the 70 names that don't match?
View 4 Replies
View Related
Jul 21, 2005
Ok! So I've binned the pivot table and gone for a continuos form option that allows the double click event that I'm after. A new problem has appeared.
On my main form I have 3 combo boxes (programme, product & company). When I click on any of the combo boxes i get a list of everything in the column it relates to. That works fine but it looks very cluttered. I'd like to have everything listed once only so that when i click on a programme it will filter the subform to show only companies working that programme.
Hopefully this little thingy I've put below will explain it a bit clearer
At the moment the programme combo box is displaying....
AAA
AAA
CCC
BBB
CCC
AAA
BBB
but i'd like it to display like this....
AAA
BBB
CCC
Every programme listed once only.
Any idea's folk's??? :confused:
View 6 Replies
View Related
Aug 5, 2006
I havea combo box on a form which looks up values from another table. The table containing the values which are looked up has another field where the data type is Yes/No. I want to limit the options in the combo box to just those which are set to Yes in the source table.
I know I need to use WHERE in the code for the combo box, but cannot find the correct syntax.
Please help!
Thanks,
Gary
View 3 Replies
View Related
Apr 4, 2006
Hi, help please!
I have an access database with one column containing e-mail addresses.
How can I use them to easily send out multiple emails from this database?
If I copy and paste, the whole columns/cells are copied, but to copy them individually is clearly very time consuming.
All I need to do is somehow get them to a format that I can paste them into my e-mail address box.
Am I being thick or is this option not available?
Richdv
View 1 Replies
View Related
Nov 15, 2006
I am trying to create call lists which will compile one-to-many relationships.
For example, suppose I have 1000 employee records with the following fields:
Manager's Name
Manager's Phone Number
Direct Report's Name
Direct Report's Phone Number
Each Direct Report has only one designated manager, but managers may have more than one direct report. Further, for roughly half of the records, the manager and the direct report are the same person (independent workers).
When I run the report, I want it to print the Managers' information, and have the Direct Report information below. I have added an IIf query stating that if the Manager's Name is the Same as the Direct Report's name, leave all of those cells blank. When I create a report, however, the labels for Direct Report will still show but the data does not. It looks somewhat shoddy.
View 2 Replies
View Related
Jul 19, 2007
I want to be able to create a email list from a query. I.E Say you need all the company's that meet certain criteria and then you have a list of company's complete with all the emails under 'email'. Is there a quick and easy way to export all these emails into the TO: field of outlook?
View 2 Replies
View Related
Nov 9, 2004
Apologies - i'm a "rookie" at Access - but i have searched for a while now on the site for the answer to my question... I'm sure there is a simple solution somwhere here but i cant find it!
I am putting togther an venue database - with all the usual fields: Name, Address, Capacity etc... of which i have set up YES/NO, Free Text and Number fields. No problem!
I have also set up various lookup lists which work fine, apart form the venue "Suitability" lookup list.
The choices are:
Corporate
Private
Drinks Reception
Dry Hire
Music Event
Conference
Now, some venues may be suitable for SEVERAL of these uses - so how do i select multiple uses from this list as opposed to just one?
Many thanks in advance for your help - im learning fast! :rolleyes:
View 2 Replies
View Related
Feb 6, 2006
Hello all,
I’m building a database for my studies and have hit a major snag with the final form build/design. The db is for a mug ordering system. The mugs are defined by…
The supplier – The Category – The Type – The Style
(whereby The Style is the final name for a mug).
The relationships have been designed as per the first attachment.
(see first screengrab)
Before I go on, do these relationships look realistic?
What I want for the final form to provide is 3 option boxes on the left, which would have to be setup as cascading fields. So when a Category name was chosen, it would then list the relevant Types in the next option box, and when the Type was chosen, the relevant Styles would be listed in the Style box. When the customer had chosen the final mug style, I would then assign a macro to copy the details to an adjacent form which would include customer details and final purchase cost.
(See attached second screengrab)
Can anyone please shed some light on this, particularly the cascading field’s option, as I am at my wits end trying to solve this?
I have looked at the help file “Cascadinglistsdemo” to no avail.
Cheers
SR
View 1 Replies
View Related
Mar 15, 2006
Hi,
My users wish to have a feature in my db that allows them to compare the records within it and view a report on it.
I have implemented such a form but the functionality is not great. Basically it's a form with two subforms - the top where you choose the projects and the bottom where you view a list of the projects you've chosen. You can search the top subform using combos and select all the records currently in view (a button runs an update query based on the combo box values)
To add them to the list the user is setting a yes/no field to true. So the top subform is where yes/no field is false (and bottom subform is where yes/no is true).
They can then report on this as the report has a criteria of where the yes/no is true.
This works brilliantly, so long as there is only one user!! Otherwise they would interfere with eachother's list-building.
Does anyone have any ideas for an alternative method so that multiple users can use it? I was thinkin of making tables or something to store IDs in but don't know much about this or where to begin!!
Hope that makes sense!
Thanks in advance,
Bobadopolis
View 1 Replies
View Related
May 3, 2005
I have a table (call it Table1) with a primary key that is a number and one other text field which is what the user would recognize the field by.
Another table (table 2) looks up to table 1. And yet another (table 3) looks up to table 2. I want the text name to show in the lookup list of Table 3 - NOT the number.
Right now, when I look at the sql results in the lookup field for table 3, the text appears rather than the number. But when I open table 3 and try to use the lookup list, the number appears.
Any ideas how to fix that? Do you need more details and sql?
View 3 Replies
View Related
Jul 11, 2013
I am looking to add 3 or more different list values into the same column on the same table.All lists are stored in seperate tables within the same database.
View 6 Replies
View Related
Nov 22, 2005
Hi, all... welcome back to the new Access World Forums... glad we're still operational, even if some data was lost. It's the minds, anyway, not the data...
I have a query that pulls from 3 transaction tables.
tblTransactions - lists purchaser, amt tendered, other details
tblPurchased_Items - lists products and services paid for
tblTherapy_Treatments - lists treatments paid for
(Note: these are not separate lists of available items. They're two subforms' tables, one that deals with insurance and one that doesn't.)
I can pull this data fine, but in a case where someone had 1 treatment, but purchased 2 products, the query lists the treatment twice. I do have unique IDs for each table's items, but am not sure how to "list" each transaction once, but each thing paid for once, also.
For example:
Joe Blow comes in and has physical therapy and buys 2 books.
My query will show (bogus fields for illustration purposes):
TransactionID Name TherapyID TherapyCost ProductID ProductCost
33 Joe Blow 10 180.00 15 12.00
33 Joe Blow 10 180.00 17 19.00
So, if I total TherapyCost it really gives me twice the correct amount.
Can someone point me to a solution?
Pardon my rank ignorance, yet again.
View 2 Replies
View Related
Apr 14, 2006
Hi,
I need to output data in a specific format for a certain client..
for example where
Select * from Falls - would normally give me:
01/01/05 Joe Smith ICU, Assisted
I need in this intance to get:
01/01/05 Joe Smith 5, 2
where locations, or types etc are converted to a corresponding number.
I can imagine a query like:
Select FallDate, PatientName, somefunction([Location],(MS2,ED,OB,CAFE,ICU),(1,2,3,4,5)), somefunction([Fall_Type],(Accidental,Assisted,Unanticipated),(1,2,3))
Is there some more powerful version of IIF or way to write this in the query without having to create a report with a bunch of functions?
View 5 Replies
View Related
Apr 2, 2008
OK, not sure how to search for this so I am just going to ask. I have two properties that send me excel sheets with the name of the employee, employee number, department, and title. What I would like to do is make this into one list. So just one table that would list these fields and maybe get a new field that marks what property that person works at.
Example
Jon Smith, 12345, admin, office ( this is from one list)
Jane Smith 54321, admin , office (this from the other list)
What I want it to look like (query)
Name, Emp #, title,department,property
Jon Smith, 12345,admin,office, prop1
Jane Smith,54321,admin,office, prop2
How would i go about doing this?
View 2 Replies
View Related
Nov 3, 2006
Have simple continuous form to create a pick list. One combo field looks up products from a list of about 5000 items.
Even though the combo homes in as letters are typed there is still a lot of typing needed to identify the target product. This is because many products have the same 10 to 15 characters to start, then have different endings.
It would be great if in adding a new item the combo remembered what was typed in the previous entry, as the picklist is built alphabetically.
Any ideas
View 1 Replies
View Related
Dec 2, 2014
In my table I have the following fields that are tick boxes
Vee
Cathedral
Round Bilge
Bilge Keel
RIB
Semi-Displacement
Keel
Lifting Keel
Each record can take a couple of these options. For example record 1 could be ticked for a 'vee' and a 'cathedral' whereas record 2 could be ticked for all of them.
I have been trying to use these to make a search form. I want to be able to chose on ie. 'Round Bilge' and it will search for a record that is ticked 'round bilge'. I have done this for both a combo box and a list box and have used the formula for each field [Forms]![Boatsearch]![hulltype]
When I have clicked run query I have an error message saying: 'This expression is typed incorrectly, or it is too complex to be evaluated. For example a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables'.
View 3 Replies
View Related
Dec 18, 2013
I need to get a query back that may have more than the given number of records on either side, but need to not duplicate in either of the two columns. Works better to show what I need, I think...
Query needs to return: PersID.....Name.....Assist.....Device
Base data looks like this (after using query with Person and AssistType and Device tables):
Assistance:
1.....Adams...Dress
1.....Adams...Bath
2.....Jones....Bath
3.....Case.....Dress
3.....Case.....Bath
3.....Case.....Feed
Devices:
1.....Adams...Walker
1.....Adams...Lift Chair
1.....Adams...Wheelchair
3.....Case.....Wheelchair
4.....Doe.......Walker
4.....Doe.......Cane
What I've been asked for is something that looks like this:
1.....Adams.....Dress....Walker
1.....Adams.....Bath.....Lift Chair
1.....Adams.....-----....Wheelchair.......... ===> Null or blank in the empty column is OK; I can work with those
2.....Jones.....Bath......-----.............................................
3.....Case.....Dress......Wheelchair
3.....Case.....Bath......-----
3.....Case.....Feed......-----
4.....Doe......-----.....Walker
4.....Doe......-----.....Cane
Regardless of join type, I get variants on:
1.....Adams.....Dress.....Walker
1.....Adams.....Dress.....Lift Chair
1.....Adams.....Dress.....Wheelchair..........
1.....Adams.....Bath.....Walker
1.....Adams.....Bath.....Lift Chair
1.....Adams.....Bath.....Wheelchair..........
I can print it with two subreports (one for each column), but that is running slower than I would like. It's workable if we absolutely have to do it that way, but I'd rather see if I can get it out in one query and use the grouping functions in the report (there are some other fields involved, but they don't really affect what I am doing here)
Is there any way to do a query (even a couple of queries if it takes that to set it up) that will give me what I am looking for?
View 7 Replies
View Related
Sep 12, 2014
I would like the user to enter information directly into an Access table (it's ok). I want one column to have month ended. In this column, there would be a drop down/lookup menu with each month of the year. The user selects the month we are being billed for.
Then another column called week ending date, would display all week ending dates (ends on a Sunday) but only for the month that was selected.
If the user selected another month on another row, the available weekending dates to select from would show only what pertains to the month selected. Is it possible to do this?
View 2 Replies
View Related
Dec 1, 2005
Hi, I'm not sure how to write something and would like assistance. The form is taken from a table and then there are two diffenterent forms displayed as datasheets within, on tab controls. I have a combo box taken from a query on each form and would like the combo box to display a certain list if the control on the form (the bit taken from the table) has a P in it, a different list if the control has an R in it or to display the whole list if the control has a B in it. Trouble is, I'm quite new to this and I'm not sure on how to write any code for if statements or whatever is needed. If you are able to assist I would appreciate it. Thanks
View 1 Replies
View Related
Jan 21, 2014
I have been asked to make a report from my database listing property sites and listed things found with them. Ex. a property might have 3 houses and 2 cars on it. Whenever I make a report the best I can make are groups that look like:
Property 1
House A
Car a
Car b
Car c
House B
Car a
Car b
Car c
I need something that could group car and houses directly to property 1 rather than cars grouped to houses grouped to property.
Ex.
Property 1
House A
House B
Car a
Car b
Car c
View 4 Replies
View Related
Jul 29, 2015
I have been working with Access for some time managing some databases for my company. But now I have some problems making the database easier to use by automatically creating a Query based on different criterias.The code looks like this:
Sub Multi_value()
'Definitions
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = Application.CurrentDb
[code]....
The problem is that the "Business Field.Value" is inserted into the Query-criteria with brackets which for some reason are not supposed to be there when I use the .Value-parameter instead of the "Business Field"-parameter.
View 8 Replies
View Related
