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.?
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.
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.
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.
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?
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....
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.
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.
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?
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:
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.
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!!
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?
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.
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.
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
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.
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'.
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):
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
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?
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?
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
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.
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.
