query using 2 tables

I have a query using 2 tables. However the second table is not always required and I notice that if the second table is not used then the info from just the one table is not included in the response. How can I rectify this problem

Posted On: Monday 22nd of October 2012 05:58:23 AM Total Views:  552
View Complete with Replies

Related Messages:

[Reports] Help with query-reports   (281 Views)
, I have made a custom form to open up when I start a query which then enters the information I enter in the form, into the query. The query then produces the results in a report. My problem is, I wanted it to display all the records when I leave a field blank. BUT when I enter a "Location/Contract", it brings up the records I want with that location/contract but it also brings up some records that don't have an entry there. Am I making sense If not, here is some screenshots: I enter "Bridge Cross Rd" And it brings up those without a location/contract... Any Ideas The code for my report is: Code: Option Compare Database Private shadeNextRow As Boolean Const shadedColor = 13356495 ' Const shadedColor = 15726583 ' alternative shade colors ' Const shadedColor = 14078404 ' Const shadedColor = 13356495 ' Const shadedColor = 14281974 Const normalColor = 16777215 Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) On Error GoTo Detail_Format_Error ' Choose a color based on the shadeNextRow value If shadeNextRow = True Then Me.Section(acDetail).BackColor = shadedColor Else Me.Section(acDetail).BackColor = normalColor End If ' Switch the color for the next row shadeNextRow = Not shadeNextRow Detail_Format_Exit: Exit Sub Detail_Format_Error: MsgBox "Error " & Err.Number & ": " & Err.Description Resume Detail_Format_Exit End Sub Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) If PrintCount = 1 Then txtPageSum = txtPageSum + Cost End If End Sub Private Sub PageHeaderSection_Print(Cancel As Integer, _ PrintCount As Integer) txtPageSum = 0 End Sub Private Sub Report_Close() DoCmd.Close acForm, "Master" End Sub Private Sub Report_NoData(Cancel As Integer) MsgBox "There are no records to report", _ vbExclamation, _ "No Records" Cancel = True End Sub Private Sub Report_Open(Cancel As Integer) ' Set public variable to true to indicate that the report ' is in the Open event bInReportOpenEvent = True ' Open Sales By Category Dialog DoCmd.OpenForm "Master", , , , , acDialog ' Cancel Report if User Clicked the Cancel Button If IsLoaded("Master") = False Then Cancel = True ' Set public variable to false to indicate that the ' Open event is completed bInReportOpenEvent = False End Sub And my Form: Code: Private Sub cmdCancel_Click() DoCmd.Close End Sub Private Sub Form_Open(Cancel As Integer) If Not bInReportOpenEvent Then ' If we're not called from the report MsgBox "For use with the Reports only", _ vbOKOnly Cancel = True End If Form_Open_Exit: Exit Sub End Sub Private Sub OK_Click() Me.Visible = False End Sub And for some reason this is what my Query looks like:
Calculate Yes/No fields in a query   (624 Views)
In a query that utilizes a table containing a yes/no field, is it possible to calculate the yes/no field and get a numeric return If so, can you share how this could be accomplished.
Replacing query   (635 Views)
hi. i are replacing queries in my tables to get the database ready to convert to SQLServer. Am having a problem replacing a query that is brings in a table for a drop down and inserting the selection in the record. during creation, the record shows a date, id, task, hours. the datatable needs date, id, task_code, task_name, task_job, hours. On "task" the drop-down allows for selection of several choices that are 3 columns wide. i can get the columns to show but only the first column currently goes in my new table. the table's columns that the selection comes from is "task_code,task_name, task_job". columns are needed as different areas use the same task code but use different jobs or names. I was told to use a insert into statement, however am having problems with it. have as follows: SQLStatement = "SELECT [Task_code], [Job_name], [Task_name] FROM tablea; " rec.Open SQLStatement, , adOpenDynamic job = rec(1) task = rec(2) rec.Close SQLStatement = " Insert into [tableb]([Job_name],[Task_name]) " & _ " values (" & job & ", " & task & " ) " rec2.Open SQLStatement, , adOpenDynamic db.Execute (SQLStatement) any point in the right direction would be greatly appreciated.
ERROR: Operation must use an updateable query.   (621 Views)
asp code: Code: error: Code: Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query. /site/content/register_act.asp, line 13 It's says it has something to do with permissions... I set writing & reading permissions for the db and the directory that contains it through contorl panel->Administrative Tools->Internet Information Services I have no "security tab" when I rightclick->properties the db file or it's directory even though the file system is NTFS in spite all these when I rightclick->properties the directory that contains the db I see a "read only" square marked in the general tab-attributes, when I try to unmark it seems to be unmarked but when I rightclick->properties again it's marked again... what should I do
Relationships query   (515 Views)
hi.. Im struggling to get the relationships correct within my database. (4 tables) It's for a shopping cart application Can someone please help: tblcustomerdetails: custid forename surname addline1 addline2 postcode email telno tbllogin customerid username password tblorder orderid orderdate orderqty orderprice tblproduct productid productname productprice productquantity productimage
access query - continued   (530 Views)
I sent message before I finished explaining my problem! The query to get the totals includes queries with the employee, vehicle, material, and barricade costs all sorted by activity code but for it drops all activities that don't have costs in each column--if there weren't any costs entered for material or barricades for the month, they get dropped. How do I get everything even with some fields are null
Update query help   (548 Views)
I have a table with trailer numbers currently on the yard. I also have a prompt to enter trailers as they are leaving. I would like an update query to then remove the record from the OnYard table. Is this possible
Lookup value from a table to input in a query   (516 Views)
I am building a mortgage/amortization database and I need to look up the "points paid" from another table and also the "adjustment to margin" based on the points paid and the index, "MTA" or "COFI". I have no idea how or where to write this lookup. Any suggestions Thank you! KellyJo
Automatically update value in the subform from Crossstab query   (696 Views)
, I have a main form which has 2 subforms. The main form is the name of the Student. The first subform is the data entry form. It has 2 fields. One for the Trimester - a drop down which has "1, 2, 3" listed. And the second field is the benchmarks (again drop down). Now the second subform is a more elaborate way of viewing (only for viewing purposes) which standards have been chosen for which trimester. Now this form's control source is a Crossstab query, which updates the Trimester field for each of the benchmarks for the particular student. Now my problem is I am trying to update this subform which is for viewing purposes only, automatically, so that the moment a benchmark is chosen on the other subform, the [trimester] field automatcially gets updated on this form. the benchmarks are listed on this form (view form) and a field which is the trimester field. Data entry is not allowed in this form. So the information has to get updated automatically. It does when I close and open the form, but thats not how it should work. I tried everything I thought possible (requery, refresh, a button to click) but couldn't get the form to update. Please help! I would really appreciate if you could help me find a solution.
Requery value of combo to listbox   (595 Views)
, I have a form with a text box, a combo and a listbox. The textbox stores the name of the user. The combobox gets its data from a query. I would like that when I open my form, by default the list box will show all the records of a table named Master and then, I would use the combo to requery the list by selecting another user. records under that criteria, will have to show up in the list box.
Concatenation of query text   (475 Views)
Can anyone please give me an Access 2000 VB module that will concatenate [Portia - 1 query.text1] with [Portia - 1 query.text2] and type the result in textbox [text]
Listbox selections requery subfrm   (520 Views)
I have a form that has names with a subform with information. The contacts are in a list box and the subform (in datasheet view) shows the phone number/email/etc.. of the selected contact person. I am using the form as a quick look up of a persons information. Before changing it to a list box, it was previously a combo box, and everything worked fine. i was able to requery the subform and the cooresponding info for the person would come up. However, its now a list box and i am having problems with the code to make this happen. I have a different button sending the names selected from the list box to a report...and that works fine, but i am obvisouly missing something to make it work with the subfrm requery. Below is the code i got so far. Any help would greatly be appreciated. its driving me nuts! Private Sub QuickLookup_Click() Dim varItem As Variant Dim strWhere As String strWhere = "[memberID] = " For Each varItem In Me.MemberID.ItemsSelected strWhere = strWhere & Me.MemberID.ItemData(varItem) & " OR [memberID] = " Next varItem strWhere = Left(strWhere, Len(strWhere) - 17) DoCmd.Requery "subfrmqryindividual"
Locking a table based on query results   (661 Views)
I have a query that checks an expiration date field and displays the word "Expired" in another field if applicable. This query is used to look up items, then the user would enter whether or not it is approved right in the resulting data grid thereby entering that approval into the table being used for the query. Can I do something with the query so that if the item returned has expired, a user would not be able to enter anything into the approval field Something like, if field1 = "expired" then lock the table I can't use a form, I have to just do it in the basic query or forget it. I would very much appreciate any suggestions!
Help on query   (667 Views)
i have this query Code: SELECT Group, ((Progress.DaysSpanned/(SELECT SUM(Progress.DaysSpanned) FROM Progress))*100)*((Progress.PercentCompleted*Weight)*100) AS WeightedProgress FROM Progress ORDER BY Group; it should reutrn the progress as a percent based on two weights (days spanned on task, and percent completed) the prob i have is if the percent is 100% complete it does not show in the progress, a value of less then 100% (due to the weights) how would i rewirte the query so that if percent completed is 100% it displays that, basically if the percent completed is already 100% it should ignore all other calculations and just return 100%
Form using dynamic query results   (524 Views)
hope this is a reasonable thing for semi-beginner to intermediate person to do. Our MS Access 2000 db shared on a server stores projects, categories of projects, clients, contractors doing the projects. Contractors are offered projects within their chosen categories based on their order on the list (last company who was offered a contract goes to bottom of list, like that). Currently we print out a phone list of the contractors (in desc. date order of the most recent offer accepted or refused), then we phone down the list until someone accepts. Later we enter all those offers in order on frmOffers to store the date & time stamp of that offer. Problem is, several contracts can be on offer on a given day, so when a company accepts/declines Project A, that company is still showing as high on the (paper) list for Project B. So when we have 3 people phoning they don't know they've offered multiple contracts to the same company this morning. Is it possible for a form "Offers" to be based on a query that keeps updating like that For example, you open frmProject to display details of a certain Project, cmd button to open frmOffers (continuous form, showing all offers so far with company, phone #, "accept"/"decline" & date/time stamp). Then, when you tab to the next record, can you have it show which company is next in line (even as the person beside you has updated their frmOffers in the meantime) Would you need to have a separate table storing only the most recent offer for each company If so, how would you get that updated all the time Anything you could offer to point me in the right direction would be very much appreciated!!
Open query or form   (515 Views)
access 2000 have a form with a list box some items in the list box after i click on it i want to open a query and the others i want to open a form help
Append query to different table   (508 Views)
Friends, I would like some help with a code that allows me to run an append query only if a specific field is not already stored into another table. Example. I add a new customer to my database using table1. I type his SSN. The code should check if this SSN is already present in another table (table2). If yes, so the code should skip the append query and just update any modified record included the SSN. If not, the code should run the append query. this should be done in the background while working in a form with its recors source to the table1. Can anyone help me
How to calculate cumulative values of this query fields?   (427 Views)
Hi everybody. I got a access 2000 query that lists : 1)weekno 2)year 3)project (project number ) 4)QweekylyReportHeader (project description ) 5)customer (customer that requested this project) 6)department (department number and name that implements this project) 7)Projectleader ( project leader name and number that is responsible for this project) 8)Task (Task number that is done for this project ) 9)task description (description of task ) 10)employee ( employee number who is working in this project ) 11)name (Employee name and initial and last that works for this project ) 12)hours ( number of hours employee worked in this task ) ==> i want cumulative for this 13)salary (amount of salary given to this employee) ===>i want cumulative for this I want to create another query that lists : A)cumulative value of hours worked on particular project task up that point. b)cumulative value for wages given for that project task up that point. ( query output sample) The above query ONLY lists hours worked and wages gives for particular project task only during each week.But i want hours worked and wages give for particle project task up to that point in week. For example a project task might have implemented last week but not this so i want to take that in calculation as well. I be happy if some expert show me how i can calculate the cumulative value for hours worked and wages given for particular project task. Notes: - There is a possibility that during a particular week no task been implement for particular project. - One employee can work in more then one project - One employee can have more then one salary (amount) for the same project because he might get raise in salary! - Only tasks carried this week will be printed in the weekly report ( pic of database) ( query output population) (query in design view) query that display hourly wages of certain project during each week Code: SELECT querythisweek.weekno, querythisweek.Year, querythisweek.Project, QweeklyReportHeader.Customer, QweeklyReportHeader.Department, QweeklyReportHeader.description, QweeklyReportHeader.ProjectLeader, querythisweek.Task, dbo_Task.description, querythisweek.Employee, [lastname] & ' ' & [initials] & ' ' & [insertion] AS Name, querythisweek.hours, querythisweek.Salary FROM dbo_Task INNER JOIN ((QweeklyReportHeader INNER JOIN querythisweek ON QweeklyReportHeader.projectno = querythisweek.Project) INNER JOIN dbo_Employee ON querythisweek.Employee = dbo_Employee.employeeno) ON dbo_Task.taskcode = querythisweek.Task; code for querythis week( calcualte the salary and hours worked) Code: SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hours_worked.hours, (select a.amount * dbo_Hours_worked.hours from dbo_Hourly_wages a where dbo_Hours_worked.Employee = a.Employee and dbo_Hours_worked.Project = a.Project and a.Year * 100 + a.weekno = (select max(b.Year *100 + b.weekno) from dbo_Hourly_wages b where b.Year < dbo_Hours_worked.Year or (b.Year = dbo_Hours_worked.Year and b.weekno
hardcoded parameters in querys--can I find all?   (423 Views)
We have a tbldepartments. At this point (has been a few years)...many dept. have merged or changed names. We want to 'change' the names of some and delete or add others. Ok on the delete/add. However, in many of our queries, we have the dept. name/number hardcoded in. And trust me, there are tons and tons of them...this is a big database. Short of going through each query, is there a way to 'search/replace' either in each query or in all To update these easily suggestions
Export query to Excel   (448 Views)
, I have a button on a form and when I press it I would like this to happen: Some code will be run that makes a copy of an templatefile (Excel), puts the values that comes from my query into the document and the saves it with a name that comes from two textboxes. I would also like to be able to put in some other text in the document, like two dates that I have on my form. Does anyone know how to do this