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 have a list of people in a list box. In the list box I only have the people names listed. (example below)
1. John 2. Frank 3. Tim 4. Jessica
I want to click on one of the names and have a form pop up on the same screen that lists the persons contact info. If I had a table like listed below, could the info auto populate on a separate form.
[ID] [Name] [Number] [Address] 1. John 456-4567 123 Elm Rd. 2.
I need to make a DBA that will have 2 tables. One Has general Infomation name, address, phone, ect. Another that will log each call made to that customer. The purpose is to log everytime I speak to a customer and be able to determine using a qury who I have not called in a while.
I have been looking around for a while now to learn how to show in a subform similar records from the same table, and a way to link them together.
I work for a mental health organization and we have a call log database that we create a new record every time a person calls us - this allows us to track the outcome of these calls. Over the last 3 years we have about 10% repeat callers. I am trying to find a way, when creating a new entry, to see if this person has called before, and if they have, link their past contact (record) to the new contact (new record).
For example.
Caller: John Smith (555) 555-5555 (this is the new record)
in the subform, a list would populate all the 'john smith' records with an option to link or attach them to the new record.
The database is to track and evaluate call center associate phone calls. I created yes/no fields for multiple common errors (accurate/complete, Security, and client experience. and under each are @ 20 common errors)
(the problem is that I did not create a seperate table for each main category which I think might have made things easier)
Now what I am trying to do is create a report that allows me to show (or select) an error and have it show all the associates that had that error in a given time frame.
I already have a report to show all associates and all the errors that each had in a certain time frame. (so vertically I have associate detail and horizontal I have error detail).
is there a way, without creating 25 separate reports, to show all associates under an error type?
I'm having to recode some old MS Access DBs so they will run in the following environments:
Office 2000 on WinXP Office 2003 on WinXP Office 2010 on WinXP Office 2000 on Win7 Office 2003 on Win7 Office 2010 on Win7
When I wrote my code for Office 2000 on WinXP things were simple because directory paths were the same across all computers and I could hard code pathing when using a shell command to launch other files.
My new approach is to make a function call to the Windows registry to determine the default executable and path for opening a file based upon its extension (see apicFindExecutable in basWindows API module).
I'm able to use code to create a shell call and debug print it to the immediate window. If I put my cursor in the immediate window at the end of the shell call and hit [enter] the external file will open as desired. If I try to open the external file directly through code, I get a file not found error.
To recreate the error take the following steps:
(1) browse to files that are accessible from your computer (2) click the PREPARE DATA AND OPEN MAIL MERGE DOCUMENTS command button
Shell function call is made by the fnOpenFile function located in the basOpenFile module. There has to be a trick here that I'm missing.
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 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?