How To Present Informtaion From Two Or More Differnt Tables??
May 24, 2006
hi all, I am really not sure if I am posting in the correct place...plz help me move to a correct place if my questions is not suppose to be here...:p
I am having a problem with showing my data through query...The original data is in such a format,that every running number(eg. 0001) has a list of items(eg. A,B,C,D,etc<--they are differnet fields in a table)...so the next number 0002 has another list of items under A,B,C,D,etc)...so now I am really stuck and dunno how to present these data either through query or form...
I have entered the data in such a format, the information relavent to 0001 in a table...and the informtaion relavent to A,B,C,D,etc. in another table...but how do u present those information of A,B,C,D,etc. of 0001 in a single query or form,and those information of A,B,C,D,etc. of 0002 in another query or form??
Please help or at least guide me through it...or else I am so stuck that I can't really move on with my project...thanks so much guys!!^^
Ok, let's say I have 3 tables with the following data
T1 T2 T3 1 , 1 , 1 2 , 2 , 3 3 , 4 , 4 , , 5
I'd like to be able to create a query that has the only record as its result as 5 as it is the only data item the does not exist in Tables 1 and 2. I don't just want to search for 5, I can do that :) I would like to be able to write a query which finds all the records that are present in one table, but are not present in x number (in this case 2) of other tables.
I don't seem to be able to find any simple way of doin this and there doesn't seem to be a definitive answer on any forums. Anyone got any ideas?? :)
Using Access 2000. I have about 5 databases but only one needs to be secured so I created a system.mdw for it. After doing that the secured db works but all other db's ask for authentication also but since there are no users or groups in the other db's, the authentication fails and I can't use the db's. If I put the original system.mdw back the other db's work but the one that needs to be secured it wide open.
Each calendar quarter we get a table which has records of client wage earnings. Each record has simply the ssn and a currency field for each of 13 calendar quarters, named for the appropriate quarter. (i.e., 1/2005, 2/2005... 1/2006, 2/2006... ) - Each table has 12 overlapping quarters' worth of data. (Constantly adding a quarter and dropping the earliest quarter.) Unfortunately, wage data sometimes changes for various reasons, so that one table's 1/2006 wage for Joe Smith might be different in the next table that we get.
An administrator needs to compare, at one visual shot/report, all the tables' values for a given quarter for a given ssn. i.e., For Joe Smith's ssn, all 12 tables' worth of wages for the 1/2006.
I'm setting up a small EPoS system for a shop and was wondering if you could help. I want a stock number to be entered (i.e. a barcode) and then the item name and price to automatically be entered.
I have the fields 'StockID, Description, RetailPrice' in tblTransaction and basically want the StockID to be enetered and the description and retailprice to be automatically be read from tblStock and placed in tblTransaction.
Any ideas? Hope this was clear enough...its really hard to describe!
I created a form for a database I am building. While in Form View some of the fileds have data in them and some do not (by design). How do I automatically grey out the areas that have no information in them? I know there is a way to do this but I don't know how. Thanks.
Only just started happening, with no explaination! Only one subform out of 5 shows as just background space until a choice is made from a combo box and then the thing appears with the record. WHY?!?!?! I HATE ACCESS... It's rubbish!! Apologies for my outburst...
- tbl_Positions {containing information on a specific job role} - tbl_Office {containing information on an office} - tbl_StaffDetails {containing information of all staff in the company, including a relationship with tbl_Office to say which office they are working out of}
I want to do a simple query saying what type of staff are working out of each office:
e.g.
If in Office 1 I might have a managing director, CEO and 2xOperations managers etc.
If in Office 2 I might have 5xAdmin, 3xHR, 6xIT Support etc.
What sort of query can I run to return just a list of job titles for those present in the office I choose.
I use the output of a query (qryTally) to set as my values to a table (tblOrderCountDaily) which sets all the count of a product ordered during a cmdbutton was clicked. If cutoff wasnt clicked for that day, it would create a new field setting the field name as the date. Now, if i click again the the cutoff button, it would check again if the field exists, if yes, i would add the value to the previous value.
Code: Private Sub CutOff_Click() Dim db As DAO.Database Dim tbl As DAO.Recordset Dim strSQL As String Dim CheckOut As String
The record source of a form that I have is based on a user selection in a combo box in the header of the form. When the form opens there may or may not be any records to display. Currently I put up a message box when there are no records displayed but this only happens when the form is newly opened.
I was wondering if it is possible to have a label displayed in the detail section instead whenever there are no records to display, such as something along the lines of "There are no records to display with the selected option, please choose an alternative.".
I realise that I may be asking the impossible but I'm a member of the "If you don't ask you'll never know" club.
I have Access as front end and SQL Server as back end. The Access application is placed on a common network drive for theusers to access it. The compact on close option is enabled when the access application is closed.
I have a local copy of the access app. When I close the app, it takes ony a few seconds. But when on a network drive, it is taking 2 mins.I just wanted to know if the time taken to close the application on the network drive can be brought down without disabling the compact on close option.
I have two text boxes and I'm wondering how I can filter my records of my table (through my query), between two numbers on my form. So for example lets call the two text boxes Num1 and Num2, the logical process is:
Code: IF Num1 or Num2 IS empty THEN filter records with "*" ELSE IF Num1 and Num2 contain a number THEN filter records between those two numbers
SQL, Me.Filter, BETWEEN in criteria. How I can accomplish this.
I have a text field like, 11242010, and I need to be able to convert it into 3 int fields, day, month, year. I am trying to do this in a query and have create the following three;
DateD: IIf([DATE] Is Not Null,(CInt(Left(Right([DATE],6),2)))) DateM: IIf([DATE] Is Not Null,(CInt(Left([DATE],2)))) DateY: IIf([DATE] Is Not Null,(CInt(Right([DATE],4)))) Time: IIf([TIME] Is Not Null,[TIME])
When I have a value of Null, i keep getting #Error, I think when it's null.
Let's see if I can explain this situation effectively...
I've been handed a database that is set up to track tutoring sessions of students. The original creator designed the database for 1 semester. I am trying to make it functional for x number of semesters. So here's my dilemma: I have several reports that are based on queries. I would like for the user to be able to run the report and it prompt for them to enter a date range, which would then output desired results for that range (I thought this would be more efficient than setting up the db to run reports by semester, so that the user would be able to narrow their results).
So in corresponding queries, I am adding the following criteria to the "Date" field: Between [Enter Beginning Date] And [Enter Ending Date]. The query produces the right results, just not exactly in the way I would like to see them. This is because the query tracks the number of hours per mentor (the person giving the tutoring) and does a sum.
So in my results, it is summing the hours for every change in date occurrence. For example, the results should look like this (on the report):
Student 1 Mentor A xtotal hours
Student 2 Mentor A xtotal hours Mentor B xtotal hours
Instead what I'm getting is a sum of hours for every different date, as follows:
Student 1 Mentor A xtotal hours Mentor A xtotal hours Mentor A xtotal hours
Student 2 Mentor A xtotal hours Mentor A xtotal hours Mentor B xtotal hours Mentor B xtotal hours Mentor B xtotal hours Mentor B xtotal hours
And so on...
Maybe I'm approaching this is the wrong fashion? I haven't had much luck searching google or access help... maybe I'm searching with the wrong strings. :)
How do I auto populate a sep column created which is blank at present, with the table name of the table I append/join to table with a query - to uniquely identify all these rows..from each table..?
I am designing a project in which there is a form and on that form there is a textbox and Browse button. With browse button you can select a file from the dialog box and that file path will appear in the textbox. This part has been done as below:
Code: Private Sub CommandButton1_Click() ChooseFile End Sub Sub ChooseFile()
Dim fd As FileDialog Set fd = Application.FileDialog(msoFileDialogFilePicker)
[Code] ....
Now the next Part i.e Part2 in which we have to see the references present in Column D of the "Summary" worksheet of the workbook whose path is present in textbox1 and check if they match with any of the references in field "Ref" of the Access table named tblLiterature.
If value in column D matches with Ref field of the Access table then change the status field of the Access table to the corresponding columns A,B,C named as "Withdrawn","Obsolete","Updated". that means if the column A of the corresponding Reference is Y then change the status field to "Withdrawn". If column B is Y then change the status to "Obsolete" and if column C is Y then change the status to "Updated".
Please see attached the workbook as this kind of workbook will be searched against Access table .
does access have the ability to create a query where all records are present, all fields are present, yet some data within some fields aren't displayed?
I have a database used to manage teaching assignments (which kid is assigned to which teacher so to speak). I have this relationship defined through three tables, a teacher table, a student table, both with unique ID's. The third table is used to define the assignment. Also, the kid table has an extra GroupID. The group ID is what is used to define. So in the definition table, Teacher 1 is assigned to Group 1, and so on (though their may be 20 kids in group 1). When a new teacher is added to the teacher table, I need it to add it to the corresponding field in the definition table. The groupID is in the table as an Autonumber so that will populate automatically.
I need to appendTeacherID to tblassignment (TeacherassignmentID) and have only one occurrence of the TeacherID. So, if I have teachers 1-8 listed, each assigned to a group# in the tblassignment, and I add Teacher 9, I need it to add Teacher 9's unique ID to the TeacherassignmentID field without adding 1-8 again. I can't figure out how to "check" for ID's 1-9 and add only those I've added to the teacher list that aren't already assigned to a group.
I've tried a few different SQL queries append/select queries but nothing seems to do what I need it to do....
I am trying to create an expression to pull data from 2011 to present date. I need the data for 2011 to only reflect 1/1/11 through 10/7/11 (today's date in 2011). I need the same for 2012 and 2013.
I don't want to have to enter dates each time I run this, therefore, a formula would be preferred rather than hard numbers.
Now i want to write VBA code to check sheet name "Summary" in that workbook and check if any of column D value matches with Access table tblmain field "Reference" values. If yes then change the status field of tblmain to "Yes".
I have a subform which lists a set of records. In the footer section of the subform, I have a number of controls which display calculations based on the records present.
The number of records in the subform will change (reduce). Basically, this is a review / approval function, the idea being that each record will be checked and approved, which will then remove it from the list.
So eventually, all of the records in the subform should disappear (the subform is requeried each time the user approves a record)
I want to be able to hide the controls in the footer section once the subform has been cleared of all records.
But not sure
a) how to determine when the subform's recordset reaches zero and
b) what event to use to fire the code to hide the footer controls.
This is my basic attempt :
Code: Private Sub Form_Current()Dim ctl As Control For Each ctl In Me.FormFooter.Controls ctl.Visible = Not (Me.Recordset.EOF) Next ctl End Sub
But it doesn't work (it only seems to fire when the subform is loaded, not when it is requeried after each approval?)
I've tried Form_Query, Form_DataChange and Form_DataSetChange but no joy with those either.
I am trying to create an expression to pull data from 2011 to present date. I need the data for 2011 to only reflect 1/1/11 through 10/7/11 (today's date in 2011). I need the same for 2012 and 2013.
I don't want to have to enter dates each time I run this, therefore, a formula would be preferred rather than hard numbers.
In sheet 1, In column A all the Batch numbers are present. So I want to check if each of these is present in Access table named "tblmain". If its present then display its corresponding Policy number in Column B of attached workbook.
