Relationship Problem....no Hair Left...!
Mar 3, 2005
Hi All.
Before I start, I have read other posts on relationship, but none that can answer this, so if it does, maybe someone can point me in the right direction.
I have put together database's before, but just with parent to child relationships. I need a db to go further.....and have been trying for about a week, just going round in circles!!
e.g.
I have the following tables...
tblProject
tblBuilding
tblFloor
tblroom.
I can create it so there are many buildings in each project, that is OK. I need it so there can be many floors under each of the buildings, and many rooms undereach of the floors etc etc etc.
Also, need a Unique_ID for every record of building, floor, room.
I will need in the end to run a report on a room, which will tell me the floor, building, project it is in etc.
I cannot work out the relationship's between each table and, I know I can insert a subform into a form which will link tblProject to tblBuilding, but how do I add tblFloor to that link?
I do hope this makes sense, and I have attached a db which may help explain
Many thanks in advance.
Frank.
View Replies
ADVERTISEMENT
Dec 28, 2006
I have created several Queries and I would like to have a column that numbered the results. I have looked all over saw a couple of samples but havent had any luck. Anyone have any suggestions. Thanks for your help and have a good New Year.
Just to clarify basically this is what I need. Loan Number is the Primary key.
# Column Borrower Loan Number
1 smith 123456
2 jones 023567
3 jang 102569
JW
View 2 Replies
View Related
Sep 22, 2007
I have a data source table with names, addresses and UK postcodes, and another table that has partial postcodes (the bit before the space) and I need to look up the local authority for each postcode. I created a front end that links to both tables and tried converting the full postcode in one table to the first 4 characters and the partial postcode in the other table to the first 4 characters too, using two separate queries, both of which give the right results. Then I used a third query based on both of the above to match up the records from both queries, but I get a "Data type mismatch in criteria expression" error. I am just using queries on tables because I'm not very au fait with VBA and so on. Any ideas, anyone please? (I can happily email all to anyone who wants to look.)
View 4 Replies
View Related
Feb 6, 2008
I'm certainly not new to Access, but this is the first time encountering a problem like this. I have a table with 3 Field Names, [Return], [Header] and [Date]. An example table looks like so..
Return Header Date
1 | 10 | 1/1/2007
2 | 10 | 3/1/2007
3 | 10 | 5/1/2007
13 | 20 | 6/1/2007
14 | 20 | 7/1/2007
I need to take to have group the Header's together, for instance 10, and have it return the [Date] for whichever line has the lowest [Return] value. I just can't figure out how to do it. Any help or insight would be GREATLY appreciated. I'm 90% done with the project, and this last part will seal the deal.
View 3 Replies
View Related
Feb 24, 2005
I have a database from which users e-mail country specific reports to a group of receipients.
I am currently using the following code:
DoCmd.SendObject acReport, "Report UK", "SnapshotFormat (*.snp)", "rec1@company.co.uk;rec2@company.co.uk;rec3@company .co.uk", "", "", "Report UK", "See attached document", False, ""
Everytime when a receipient needs to be changed or added the users ask me and I have to change this code; this is offcourse not the best way. For some countries I even have as much as 15 recepients
What I would like to do is set up a table showing for each country the e-mailreceipients, link this to my form and use the field from the form in my code which is activated by a click on a button.
So I started testing this, but I am really breaking my head on this one....
I found two other examples on e-mail from which I took some code and sort of got something working.
The problem that I have now (see attached database) is that even though there are e-mailaddresses in the field on the form the code considers the field empty (null) only after clicking on the field the script works.
My idea however was to make this field a hidden field - users should no be bothered about this
Anybody have an ide how I can overcome my problem?????
View 2 Replies
View Related
Jul 16, 2005
Let see if I can explain my goal. I've been fighting with this for some time with no resolve. In the included DB has a table that represents the fields that will be queried in the real DB. It shows records of one of many employees that were "tested" (shopped) on a date and whether it was a successfull shop or not. My goal is in three parts.
1)A query that goes through each employee and returns the records with the check box in SuccessYes checked (Yes) back to the last unchecked box, not including the unchecked box. In the example given the last (most recent) 5 records would be returned. If the shop of date 1/18/2005 was not checked, it would only return the last 2 records. Basically, I'm looking for a way to have a report that shows everyone's current successes in a row. It could be any number.
2)A query very similar but it returns only those employees that have the current successes in a row totalling 5 and where the field "Award5_Paid" is NOT checked (they have not been paid their reward). From there it should be easy to do one for the 10 in a row.
3)If I've got a form open (or...?) where I can place a command button, that when clicked, it appends the selected records with checks in the appropriate boxes for being paid. Example: If a set of employees come up in the query with 5 successfull shops in a row because the "Award5_Paid" field is unchecked, then I need to pay them. Click the button and the Award5_Paid field gets checked in THOSE RECORDS ONLY so that the next time I run the query, they won't show up and get paid twice. Issue? What's the coding or SQL or ??? that's run when clicked.
I hope that makes sense. I've been running around in circle trying to figure this one out. Maybe it can't be done, but if it can, I know some of you know how.
As always, thanks for the assistance.
-Jim
View 3 Replies
View Related
Feb 6, 2006
Hi,
I have a split database. The front end with forms etc is distributed to users and the back end with tables sits on a shared drive.
I have been trying to set the security on this and quite frankly it's driving me mad. I managed to get the security set with 5 user groups and about 100 users - it all worked well on my PC but when I distributed it, the copies open with the Admin as user, no password required, resulting in no security whatsoever.
Further reading showed me that I had to change the owner of all objects from Admin to a new adminitrator, who I named Custodian1. This I did. I then created a new database while logged on as Custodian1. So, the new database has all objects owned by Custodian1 and the database itself is owned by Custodian1.
The book said I had to delete the Admin user but, after asking if I was sure that I wanted to delete the user, Access would not let me.
I went through the Workgroup security wizzard a number of times to try to reset things that way, all to no avail. I then did the whole ownership change thing with the back end database and relaunched the wizzard. Still unable to delete the Admin user.
My final attempt was to use the wizzard and create a new WIF rater than edit the existing file. And still, I cannot delete the Admin user and distributed copies have no security.
I don't know how this ties in, but the distributed copies do not seem to be linked to the WIF although they find the Back End database, in the same shared drive/directory with no problems. If I set a password on a distributed FE database copy, it then cannot be opened as the users are not defined.
I'm obviously missing something fundamental to the whole process. A further difficulty is that I have the live database FE/BE, working copies FE/BE, The security wizzard backups FE/BA, and all the directories have similar names. On top of that, I have my normal none database duties to distract me. I'm finding it very hard to keep track of where I am or what I am doing.
Any help most gratefully appreciated and received.
Regards,
Keith.
View 14 Replies
View Related
Jun 18, 2006
Hi Guys,
The asset managment system I have been developing is due to go live next week and I am really struggling with the data entry and search forms.
I have attached a picture of the main form (and outlined each subform) to give you an idea of my project.
The attached form is actually a Main Equipment Type Detail form with a child subform AssetMain which has a number of child subforms i.e. Asset Details, Purchase Details etc.
When a user clicks to add a new asset a popup form asks them to select an equipment type i.e. PC. When they click it opens the attached form. Their selection filters the equipment type detail values in the combo i.e. Laptop, Tower, Server etc.
When a value is then selected in the combo you can then add an asset for that equipment type detail. (One Equip Type > Many Equip Type Detail > Many Assets)
1st Problem)
I have set the Asset Main subform to allow additions only. When the whole form opens, the Equipment Type combo (at the top) does not display a value but does actually contain the first value.
How do I force my user's to select a value in the combo before it actually filters the child subform?
2nd Problem) The Asset Main Subform (filtered by selected Equip Type) should only be used for data entry. I set the data entry property of the form to True but when the value in the Equip type combo changes, it displays all the relating records in the subform. My only solution so far is to stick
Subform.DataEntry = True
in the Equip Type combo's after update event. However, this doesnt look to good as it flashes up with the data and then displays a blank record.
Any ideas?
3rd problem) Due to the fact that I am using a number of subforms, my tab order is such that values are entered for the main form (i.e. ID, Condition) then subforms (i.e. manufacturer) then back to the main form (i.e. allocated to). This means that:
a) I am having problems validating the record beforeinsert as I have had to change the required property to "no"
b) Once the user has tabbed through all the forms what is the best way to go to a new record? Alternatively if I add a new record button I need to check that the necessary fields have been filled in (on all of the forms/subforms).
and finally the 4th)
I have a search form that queries the assets. The search results are displayed in a list box. When you dblclick the list box I need to show the asset details.
Question: Can I use the above form to do this? My only problem is that I need to open the AssetMain form which is a sub form in the above. If I try and use the existing form it will not open as many of the controls refer to the form as a subform. Do you think I need to create a separate form for showing the data to entering it?
Would really appreciate some help (not that you havent heard that before :) )
My head is going to pop in a minute!!!
Thanks
Ben
View 5 Replies
View Related
Jul 20, 2005
I have a column in access containing codes in format K2316 and wish to remove Prefix (K) from this code.
How can i do this as a calculated field?
thanks
View 14 Replies
View Related
Mar 25, 2008
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.
any ideas.
Thanks,
View 11 Replies
View Related
Jun 15, 2005
hey,
Is it posible to somehow intigrate this code or some other code that would do the same in to query?
code:
str1 = Right(Me.Text1, InStr(Me.Text1, "Ex") + 2)
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?
THX
View 3 Replies
View Related
Nov 21, 2005
Hi
I am trying to use left join on the following tables.
Employees
----------
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
and
Weekly_Timesheets
-------------------
EMPLOYEE_ID
WEEK_NO
YEAR_NO
TOTAL_HOURS_WORKED
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.
Kind Regards
Bhanu
View 6 Replies
View Related
Dec 8, 2005
Left$([Entry timestamp],14)
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
Regards
Dominic
View 8 Replies
View Related
May 17, 2006
Hello,
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,
Thanks,
J.
View 4 Replies
View Related
Jun 22, 2006
Hi Guys
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;
View 2 Replies
View Related
May 8, 2007
Simple exlusion query:
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?
View 5 Replies
View Related
Sep 13, 2007
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
Mail Zipcode «Expr» Left («stringexpr», «n»)
View 3 Replies
View Related
Nov 14, 2007
Hello
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.
Any help would be appreciated.
View 4 Replies
View Related
Sep 10, 2005
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.
What do I need to do?
Thanks for any help.
Scott
View 1 Replies
View Related
Oct 6, 2004
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.
View 6 Replies
View Related
Oct 25, 2004
Hi,
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 & ")"
Thanks in advance.
-GreetInfo
View 1 Replies
View Related
May 2, 2005
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.
Any thoughts would be great. Thanks.
View 4 Replies
View Related
May 19, 2005
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.
View 2 Replies
View Related
Apr 13, 2007
I would like to have a event for the left and right arrow keys, does anyone know how to do this?
View 4 Replies
View Related
May 10, 2007
Hi !
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.
If you have any idea, it would be great.
LawraC
View 7 Replies
View Related
Mar 18, 2005
I have a field within the table in hebrew and want to make it read from right to left. How can I?
Will that be working if I display it using ASP online?
View 1 Replies
View Related
