I've created a db for my mortage company and I have everything complete except returning customers. I want the customers to have the same customerID but different TransacionID. Sounds simple to me but im having problems. It could be a table issue but im hoping its just a form thing.
I have tables: Customers-CustomerID(autonumber),CustFirstName,CustLastName, PhoneNumber,EmailAddress,OriginatorID.
Then I have Table: Detail- CustomerId(lookup from customer table),TransactionId(autonumber),SSN,Address,City, State,Zip,Spoucename,CreditScore,
Then I have Table: Loan- TransactionId(lookup from Detail),LoanType,LoanAmount,StartDate,EndDate, LoanLender
I have a form for new customers which is the customer table. Then once the customer has his credit checked we enter the customer details with a form. Then when they want to move forword with a loan there is a form that pulls the TransactionID from the Detail Table.
I cant figure a way to make a form that will create a new transactionID associated to the custs CustomerID without overwritting the older record.
Heiiii Everybody I have s small problem please pay think for a second to answer me. !.... I have some forms and in those forms there is bar I don't know what is that , but there is something on the left side and I wanna remove it . what ever I do with form properties it is not going , may be I am doing wrong. I am sending the screen shot of one form please have a look and tell me how to remove it. thank you .
You know how you get the form header/detail/footer sections on a form. Well, how can you make text appear down the side (ie so that it overlaps all of the header/detail/footer sections).
Take a look at my drawing, it will explain it better...
Not sure if this is possible? I have a column called Issues and another column I want to call ShortIssues and I basically want ShortIssues to be Left(Issues,50) so that its just 50 characters long of column Issues.
I have a table1 with filed "EXZ" and that field contain some data like "7.5/400/Eex de IICT4" now i would like to run an update query on that table and update field "EXZ" from "7.5/400/Ex de IICT4" to "Eex de IICT4". The last part of the data is not allways the same but it does allways start with "Ex"! So is that even posible to do with query or do i have tu use a macro or what?
All I want is to pull all the employees with their corresponding total worked hours. There are cases when some employees forget to fill their time sheets in a week and might not have entries in weekly_timesheets table. Since I want all the employees irrespective of whether they have a record in weekly_timesheets or not I am using left join.
The left join I am using is as follows:
SELECT EMPLOYEES.EMPLOYEE_ID, WEEKLY_TIMESHEETS.TOTAL_HOURS_WORKED FROM EMPLOYEES LEFT JOIN WEEKLY_TIMESHEETS ON WEEKLY_TIMESHEETS.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID AND WEEKLY_TIMESHEETS.WEEK_NO = 46;
The moment I am trying to execute this statement Access is crashing. I couldn't figure out what am I doing wrong.
I want to execute the above sql query but the result is not what i'm expecting. the field entry that i wan to extract the leftmost 14 characters is 2.005113E+19 instead of just having plain numbers like 2005113009010532000000.....
I think it's becoz of the format that's why Left function is not working as expected. Can somebody help me out regarding this? Thanks very much
I have used the left function to provide me with the left x characters of a field, which works fine.
Description: Left([c$products],[diff])
However, I want to query this field to only give me certain descrpitions, but it will not work. Can anyone let me know if this is possible, or if I'm totally out of luck,
I'm kind of a newbie to Access but am learning :D I've found myself stuck with a join statement
I have 2 tables: tblFleet & tblService
I need to find the most recent service date from tblService for each entry under tblFleet....
This is my code thus far, but it shows all the records for both tables where the fleet_Num field is equal....
SELECT tblFleet.Fleet_Num, tblFleet.In_Yard, tblService.SrvDate FROM tblFleet LEFT JOIN tblService ON tblFleet.Fleet_Num = tblService.Fleet_Num ORDER BY tblService.SrvDate DESC;
SELECT tblEmployees.EmployeeID, tblEmployees.LicenceNo, tblTimeSheet.ShiftDate, tblEmployees.FirstName, tblEmployees.LastName FROM tblEmployees LEFT JOIN tblTimeSheet ON tblEmployees.EmployeeID = tblTimeSheet.EmployeeID WHERE (((tblTimeSheet.EmployeeID) Is Null));
errrr nope it doesnt work.. and that is direct from the wizard...
I have a work around but it is inelegant not to mention not workable..
what i need to do is filter on a shift date as i need to see what employees are not rostered on particular dates..
what i have had to do is make a query selecting base data from timesheet and filter the date at that point... make another query to do the left join.. again not elegant... to make a report i would somehow have to programmatically adjust the filter on the date.. too hard basket and will be too slow when i have a copious amount of data in 12 months... anyone know what the hell is wrong?
I have an imported table with zipcodes, some are 5 digit some are 9. When i go to build my query i dont get an accurate number, i tried a left function to get the 1st 5 digits but keep getting a invalid syntax. I am in the query builder. anyone help me with syntax, heres what i currently have
I have a loan db that I want to write a query to reflect all active loans with or without a current month's payments. In other words, I want it to select all active loans even if there was no activety during the month. I have 1 query that pulls out all active loans; qryActiveLoans. I then create a left join (2) with that query Grouping on the Loan# and summing the Principal and Interest fields where the payment Date is between #10/01/07# and #10/31/07#. I need to group on loan the number because some loans have more than one transaction in the month.
The problem is that the query does not select the loans that didn't have any payments during the month. I thought that a left join (2) will pull out ALL the records from the qryActiveLoans query. I've tried adding the word ALL after the SELECT statement but that does not work. I've also tried various forms of nz without success. This is probably a common problem but I could not find it in my searches of past postings.
Hi, when I type 0600 in a text box, it dispays it as 600. How do I make it so it displays the number that I actually type in, with a zero as the first digit? Please help. Thank you.
I am trying to do a left join in ASP but getting error message saying "Syntax error in From clause" The query runs fine if I have a record for a country in all the tables. But I have certain countries that do not have a matching record in currency table. For those countries I want the information from the other tables. Following is the query:
SQL = "SELECT Country.Country_Name, Country.Continent," & _ "Country.GNI_per_capita, Inc_Cons_Distribution.Top_10_percent," & _ "Inc_Cons_Distribution.Gini_Index, Population.Total_Pop, Population.Urban," & _ "Population.Rural, Population.Under_14, Population.Below_Poverty," & _ "Currency.Curr_Name, Currency.Exchange_Rate FROM (([Country] LEFT JOIN Population ON " & _ "Country.Country_Name=Population.Country_Name) LEFT JOIN Inc_Cons_Distribution ON " & _ "Country.Country_Name=Inc_Cons_Distribution.Country _Name) LEFT JOIN Currency ON " & _ "Country.Curr_ID=Currency.Curr_ID where country_ID in(" & countryList & ")"
Hi all - I have a database that has only 1 field of patient names (e.g., "Johnson,Peter S"). I also have a form that allows clinicians to lookup their patients and I want them to be able to type in the last name of their patient and get the info they need. Anyone know how to pull just the part of the name BEFORE the comma (i.e., the last name only)? The Left function won't do it because the last names are different lengths.
Also, is there a way to have the form pull all the names after each letter they enter? So when the user typed "J" it would pull Johnson, Jones, Jackowa, etc, but when they typed the "o" it would decrease the list to only Johnson and Jones.
I have a shared 2000 db and for the last few days someone has been leaving it open. In '97 you could open the .ldb file and see who it was, is there a way to find out in 2000?
Also, I have seen something on remotely removing users who leave the application open but can't find it now. Does anyone know how this is done? The data must be refeshed daily and hasn't been for the last four days due to this lock.
I use access 2000 (french) and runtime access 2000 (english) on XP. After upgrading workstations with last microsoft security patchs, the use of left, mid or right functions in queries doesn't work anymore. We know now that the patch acted on MDAC, is it the answer? I don't know how to make it work.
Hi, I am doing a Left Join to try to look up values in a large (about 100,000 records) table. If the value isn't found, I'm using the nz function to supply a value.
This query runs very slowly (takes about 2 minutes). I can understand why... I suppose that for every value it's trying to look up , it has to loop through all 100,000 records before it decides that it's not there.
So, I am just looking for ideas on how to make this run faster.
I do have indexes on all my join fields and criteria fields. Thanks for any suggestions.
Here's the SQL:
SELECT VM1a.row, VM1a.Column, VM1a.Noun, VM1a.Rev, VM1a.RefDes, VM1a.repcode, VM1a.Cell, VM1a.InspPoint, VM1a.DefectType, CLng(nz([FirstOfTruncatedOpSeq],0)) AS ZOpSeq, Sum(VM1a.DefectQty) AS SumOfDefectQty FROM VM1a LEFT JOIN BOMOutRefDesOnly ON (VM1a.RefDes = BOMOutRefDesOnly.RefDesOnly) AND (VM1a.BOM1 = BOMOutRefDesOnly.PCAItemNo) GROUP BY VM1a.row, VM1a.Column, VM1a.Noun, VM1a.Rev, VM1a.RefDes, VM1a.repcode, VM1a.Cell, VM1a.InspPoint, VM1a.DefectType, CLng(nz([FirstOfTruncatedOpSeq],0));
