Simple Relationships Problem (easy For The Experts)
Feb 18, 2008
Hi there,
I'm creating a database for my computer repair business and it's going to store details on my customers, their computers, and any repairs I carry out on them.
I've created some tables, but I'm unable to create the relationships I require between the tables. Here's what I have:
http://img352.imageshack.us/img352/4724/helpbk3.jpg
I want to create a one-to-many relationship between the tables left to right, excluding the Engineer table, which I will talk about in a moment.
The idea is - One customer has "many" computers, one computer has "many" repairs, one repair has "many" problems. Each repair has just ONE engineer assigned to it, although the engineer will be assigned to many repairs.
I can link the Customer and Computer tables, via the CustomerID, but when I try to connect the other tables, I get the "Relationship Type: Indeterminate" error.
Can anyone help me with what fields I need in each table, and what relationships I need to achieve what I need?
Many thanks :)
Paul
View Replies
ADVERTISEMENT
Jun 14, 2004
Hi,
I don't know how to create a simple query that will compare 2 tables and create a new one after that.
Each table has only one field.
Example:
Table 1 has records A, B, C, D and E
Table 2 has records A and C
I want new table to be like table 1 minus table 2. Like cleaning up table 1.
New table would have records B, D and E
I think it's very simple but I can figure how to do it.
Could you pls give me advice?
Thanks a lot,
skuba
View 3 Replies
View Related
May 24, 2007
hiya every1, (access 2k3)
quick easy question, as im a noob when it comes to coding/vb.
Within our company database we have the usual timesheet table. With the table I have create a query (called "dailytimeforkeith") that gives me the sum of time for an individual person for one day (in fact its the previous day). What I have done then is created a form (called "keithyesterdaytime") that shows this query entry (called "sumoftimespent").
Each user has a different database screen logon and forms that are present when they log in. So what I then done is on one of these forms the load up, i attached this:
Private Sub Employee_Enter()
Me.Employee = User.FirstName
If Me.Employee = "Keith" Then
Dim stdocname As String
stdocname = "keithyesterdaytime"
DoCmd.OpenForm stdocname, , , acAdd
............
then on "keithyesterdaytime" opening the following code runs
------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
Dim internal1 As Integer
internal1 = Forms!keithyesterdaytime.SumOfTimeSpent
If internal1 < 6 Then
DoCmd.Close
MsgBox " Less than 6 hours have been entered " & Chr(13) & " into your timesheet for yesterday. ", vbCritical, Title1
Else
DoCmd.Close
End If
End Sub
-------------------------------------------------------
Whenever i logon with the user keith, i get the following error 'runtime error 2427: You entered an expression that has no value.
If I open the form "keithyesterdaytime" on its own, the code works and i dont get an error.
What have i done wrong, and be gently. HEHEHE. Also is there any easy way i can do this instead of having to create the the form "keithyesterdaytime"
View 2 Replies
View Related
Nov 13, 2007
I need to complete a project for school for my computer class. I am having some problems with this one assignment. I am finished but want to know if anybody can help me. Just verify that the answer is right or wrong. Any input will be greatly appreciated. Here it is... Its just a fill in the blank so if anybody who knows MS access can help me that would be great. Thanks.
The word required are the ones with the underscores around them.
Fill in the blanks:
1. Some developers like to use the _Design View_ property for a field to document its contents, such as identifying a field as a primary or foreign key or providing users with instructions about entering values into the field.
2. A _Query__ is a database object that stores criteria for selecting records from one or more tables based on conditions you specify.
3. To select multiple controls, you could click a control, and then press and hold the _Shift_ key and then click the addition controls you want to select, releasing the key once all the controls are selected.
4. A form?s _Table_ is the underlying object that provides the fields and data in the form.
5. You can view a description of each property in a property sheet by clicking the property and then pressing the _F1_ key.
6. When you select a control, eight handles appear on its corners and edges. The larger handle in a control?s upper-left corner is its _Move_ handle.
7. A(n) _Data Type_ displays a list of values, and lets users select one from the list.
8. Each report can have up to _Over 300_ sort fields.
9. An advantage of using the _Table Wizard_ to create a table is that the sample tables and fields already have properties set that might work well in your database or that are easily changed to accommodate your specific data needs.
10. In Design view, you can use the _Property Sheet_ to set the properties for fields that do not use the default settings.
11. In Access, you can specify restrictions on the data that users can enter into a field by creating a(n) _Validation Rule.
12. If a user attempts to enter an invalid value in a field, the _Validation Text_ property for the field opens a dialog box with a predefined message that explains the valid values.
13. In Access, you define table relationships in the _Relationship_ window.
14. You use _Borders_ around field names to distinguish them from function names or other types of values.
15. When entering the data in a table in Datasheet view, you would press the _Control + Minus Sign_ key to cancel a record that you have just entered.
16. In Access, you can control the format of a value by creating a(n) _Input Mask_.
17. A(n) _Look Up_ field lets the user select a field value from a list of existing field values stored in the database or from a list of values specified when the field was created.
18. The primary objective of creating a(n) _Indexed_ field in a table is to prevent users from entering duplicate records into the table.
19. If an expression is complex, you can use _Brackets_ to indicate which operation should be performed first.
20. To move from one record to another in a main form, you would click the buttons on the _Navigation_ bar at the bottom of the main form.
View 14 Replies
View Related
Jul 28, 2006
I am new to access, so I haven't been able to come up to speed on all of the functionalities that it has. I have a table with a social security number, an effective date, a department, and pay periods. I have seven other tables with a social, a date, and absentee occurances. For any social in the seven tables, I need to subtract one pay period from the original pay period table based upon the max effective date that is less than the occurance date. I am dealing with large amounts of data, at least a million rows in the original table, and 50,000 in the 7 smaller tables. I know access may not be ideal for this solution, but it is what I have to work with. Any suggestions?
View 2 Replies
View Related
Aug 10, 2007
Dear Access Chart Expert
I have been trying to figure out how I can make the x-axis show month abbreviation names instead of numbers. Please see attached gif.
Currently, I use the Month([DateField]) function to get numbers of the month because if I use Format([DateField], "mmm") to get "Jan, Feb, March" the chart sorts the months in alphabetical order instead of the correct monthly order.
How can I be able to use the Jan, Feb etc... labels and have the chart sort them in the correct month order and not alphabetical order?
Thanks so much...
greatly appreciated.
View 4 Replies
View Related
Mar 14, 2008
Ok guys, I've been eyeing this problem since June and I must say that I'm rather sick of it. After months of dredging the web, I've come to the find multiple mocking hits implying that the answer may lay somewhere in the Experts-Exchange, where i understand that some of you have been know to roam.
Without furthur blather, I must admit that im trying to avoid paying 12 bucks for this answer, especially as I've grown suspicious that this is a straight up MS/Adobe collaboration failure... and now for the problem.
When opening PDF files from my database (Access 2003) Acrobat (7 and 8) successfully opens the pdf. When I attempt close the PDF, nothing happens and I get the following Error when i go back to access:
The operation on the Acrobat Document object failed.
The OLE Server may not be registered
To register the OLE Server, reinstall it.
Hit Ok and the PDF file closes. ANNOYING AS H#LL.
I have repaired access, and acrobat. I have reinstalled. I have checked registries. I have updated .dll's. The only help I can offer is that when opened on an IT guys computer the problem seems to be gone. I'd bet this is because he has a pro copy of adobe. Basically I'm trying to figure out if i need to convince the powers that be to put a copy of Pro on any computer using the DB (if thats whats really helped), or if there is still some way I can fix this myself. Thanks gentlefolk! You (plural) are the rule.
View 2 Replies
View Related
Jan 26, 2006
Create a temporary table to hold the results. Use VBA to open the data table and temporary table in two recordsets. Loop through the records in the data table and place the data in the temporary table in the required positions.
View 1 Replies
View Related
Jun 11, 2005
I am trying to merge data with MS Word, but am getting problems. I
only seem to be able to merge a single record. Can anyone help pls as this
is quite urgent
Thanks
barnettpaul@gmail.com
View 2 Replies
View Related
Jan 6, 2006
Forms used:
frmAnnualService
frmPostCardback
Tables used:
tblAnnualService
tblCustomers
I have a database created containing 2 tables. (tblAnnualService and tblCustomers). I have the Repair Order set as the primary key in both tables since it should never have a duplicate. I have a one to one relationship, enforce referential integrity and I have cascade updated related fields. The join type I chose is 3. Include ALL records from tblCustomers and only those records from tblAnnualService where joined fields are equal.
I have a couple issues ongoing at the moment. I have a button on the master form (frmAnnualService) used to input service information for each customer. This button runs and update query that updates tblAnnualService and all fields on this form are bound back to tblAnnualService.
With the setting on the relationship as listed above neither one of the tables is being updated. When I change my relationship and remove enforce referential integrity and cascade updated fields, change join type to 1. Change only include rows where joined fields from both tables are the same the tblAnnualService updates but the tblCustomers does not.
I have another form (a postcard mailer frmPostCardback) that is bound to the tblCustomers. I need to be able to from the frmAnnualService input the data, click the update query table button and have it update the tblAnnualService and the tblCustomers. I assume I do not have my relationship setup correctly. Can someone please assist? I some background with Access but need your comments to be specific and detailed so I can make sure I am correcting the issue properly.
Thanks in advance for any assistance I receive.
View 6 Replies
View Related
Nov 22, 2006
I Need to eliminate repeat entries of Names in a Combo pull down list
I have since found out that this is possible with the DISTINCT directive in a simple query like
SELECT DISTINCT [Name] FROM Miracle_Cloth_Main ORDER BY [Name] DESC; as the source
property of the combo box.
It looks like ACCESS needs A primary Key included in the querry for the source property
of the combo for the FindFirst function in the after_update event of the combobox
to work with a code similar to
rs.FindFirst "[RecordNum] = " & Str(Nz(Me![Combo112], 0))
The Wizard generate the following code even if you don't select the Primary Key(RecordNum)
during the creation process.
SELECT Miracle_Cloth_Main.RecordNum, Miracle_Cloth_Main.Name, Miracle_Cloth_Main.Cust_ID FROM Miracle_Cloth_Main ORDER BY [Cust_ID] DESC;
Even if you add the DISTINCT Cluse to the above code
it doesn't filter the repeat data.
Is there a compromise where I can get the Filtering by DISTINCT clause to work with the code generated by the wizard so that I get the FindFirst function as well to work ?
View 5 Replies
View Related
Jul 24, 2006
My friends,
I am trying as hard as I can to learn Access. I am developing a database for my signage design business. I've been at it for about 5 days already. I know this question would be a piece of cake for the experts in this forum.
When one of my artists finishes with an artwork, he will save this as a jpeg file. I want him to upload it to the database via a form.
1. I would like to come up with a command button that, when clicked, will open a browser on the users remote computer and enables him to find the file to upload.
2. When this file uploads, I want it to be saved in another directory, outside the db, and then linked to a field within the db.
3. And I want this feature also for mp3 files.
Can any one help with this?
View 3 Replies
View Related
Jun 1, 2005
I want to have code that disables fields based on the value of the contents of one of the fields. I wrote this code which goes on and on but it doesn't work. Can someone help?
Private Sub Form_Open(Cancel As Integer)
Dim Item_Number As String
If Me.Item_Number = "300123C" Then
Me.Batch_Lot_Number.Enabled = False
Me.Issue_Date.Enabled = False
Me.Production_to_BPT.Enabled = True
Elseif blah blah blah
else blah
Endif
End sub
View 3 Replies
View Related
Mar 22, 2005
I don't normally work in access so I am sure I am asking a really easy question.
I have a table that is connected to a form where users enter survey data. Currently they have a drop down form to mark the responses to the survey as "Fully Completed", "Partly Completed", etc.
On the table I want to add the text "Awaiting Completion" to all those without any entry so that we can find out how many are left to do.
I can filter the table to give me the correct records, and assumed I could paste the text directly into the column (as with Excel).
Can anyone please let me know how to update the column easily.
Thanks for your advice.
Chris
View 2 Replies
View Related
Mar 30, 2006
Warning - Complete newbie post!
Hi all,
I have 2 Access databases setup, each containing 1 table. Let's call them A and B. These two tables contain some of the same fields.
Table A is used for collecting new client registration info through our website and gets updated by SQL.
Table B is on a different server and is used for company newsletter mailings.
Table A is the one which is automatically being updated so I don't need to do anything with that table. Also, some fields in Table A are auto-incrementing numbers so we can differentiate a clients interest and provide a more informative newsletter for them specifically on their selected interest.
Whenever an entry in certain fields of Table A is made, I would like the same fields of Table B to be updated with that same information automatically. For example...
Client subscribes to our newsletter on our site and selects their interests, the email field now contains the subscribers email address and the various 'interest' fields contain a '1' telling us they are interested in that particular area of our business. As this takes place on Table A, it needs to be also replicated to Table B.
Surely this is possible, but how?
View 1 Replies
View Related
Apr 18, 2006
As I am an SQL novice I need some help
I have 3 tables
Table - Fields
SalesLedger - TransactionID, ProductID
Vouchers - TransactionID, Voucher_Ref
Products - ProductID, Product_Description
The Salesledger has fields TransactionID and ProductID
The Salesledger is linked to the Products table by the ProductID field
The SalesLedger is linked to the Voucher Table by the TransactionID field
How do I produce a query to list all the items in the Salesledger but if there is a Product in the Products Table matching the ProductID the Product details, and if there is a TransactronID in the Voucher Table list the VoucherRef.
At the moment, it only shows transactions if there is a corrosponding TransactionID in the Vouchers Table
Probably not explaned this at all well. I will try to elborate if required.
View 1 Replies
View Related
Jun 24, 2005
Hi all,
I am making a query which sorts birthdays into chronological order.
I have used the DatePart function to determine the month:
DatePart("m",[DATE OF BIRTH])
But I want to know how to display the month in CAPITAL LETTERS, i.e. instead of reading 1 it reads JANUARY
Thanks in advance
View 7 Replies
View Related
Feb 16, 2006
I have a list in Xcel of approximately 1100 client #'s that I have to match to our master list of 10000's.
I can create a query to pull these Clients, but I'd like it to only pull the 1100 requested. Is there a way to import an excel file to bump up against a query so that it only pulls the requested clients?
IE do I have to create a table and bring that data in then use them both in the query?
Sorry if this is a simple question, I'm just being thrown into the fire and have very little access background.
View 1 Replies
View Related
Mar 2, 2006
To start: Running Office 20003. I have created a survey with Frontpages wizard. Basically It has a topic and the end user rates that topic on a scale of one to five like so:
1. Work Environment
a. I enjoy my work place. 12345
b. I think my office has a good reputation. 12345
1-5 are radial buttons. It takes the users input and stores it in an access database as im sure you all are aware. Looks kinda like this:
1a 1b 2a etc.
3 5 1
What i would like to do is this: When the users are done submitting i would like to go back and take results. I.e. we had this many users select option 3 for question 1a and we had _ number of users select option 2 for 1a, etc.
The thing is i was wondering if there was a way to create a query for it. I know i could just put =1 or =2 but that would require running 5 queries on each question.... not fun. Anyway, any help would be appreciated.
Thanks,
Viko
View 1 Replies
View Related
Mar 22, 2006
Hi - I think this can be quickly sorted.
I use the following code in my query to merge together my customers details to 1 field:
LeadName: [Title] & Space(1) & [FirstName] & Space(1) & [LastName]
I want to do it for their address, what do i use instead if SPACE so that i get a NEW LINE each time rather than1 space to the right?
Thankyou
View 1 Replies
View Related
Apr 18, 2006
As I am an SQL novice I need some help
I have 3 tables
Table - Fields
SalesLedger - TransactionID, ProductID
Vouchers - TransactionID, Voucher_Ref
Products - ProductID, Product_Description
The Salesledger has fields TransactionID and ProductID
The Salesledger is linked to the Products table by the ProductID field
The SalesLedger is linked to the Voucher Table by the TransactionID field
How do I produce a query to list all the items in the Salesledger but if there is a Product in the Products Table matching the ProductID the Product details, and if there is a TransactronID in the Voucher Table list the VoucherRef.
At the moment, it only shows transactions if there is a corrosponding TransactionID in the Vouchers Table
Probably not explaned this at all well. I will try to elborate if required.
Edit/Delete Message
View 3 Replies
View Related
Dec 1, 2006
Hi all. I need to know if there is a way to show the last entry for a record. In my database there is a set of numbers i.e. 3000, 3001, 3002 etc which have multiple valuation figures with them i.e. 3000 could have valuation 1 £1000, valutaion 2 £1245. So as mention what i need is my query to show the last valuation entry for the selected reference number i.e. 3000 and valuation2 only.
Any ideas?
View 4 Replies
View Related
Dec 28, 2007
Hello,
I dont know if my brain is not working today....but I have a query that is prompting for user input....if the user clicks cancel it shows "Action Failed" "Halt". How can I get this to just close when cancel is clicked?
Thanks
View 2 Replies
View Related
Feb 21, 2008
Hello,
I working with different business groups who have vendors assigned to them. I have table of vendor names for each business group. I'm getting data from different sources with vendor names that are unlike table. example: ABC company, ABC. Even through these are different they are they same vendor. Is there a formula, criteria, etc. to pick these up? I will be updating the vendor table occastionally.
Thank you
View 4 Replies
View Related
Feb 18, 2005
i need to show a group of donators details in a report. i want to have one donator per report page, but at the moment its sticking three to a page.
any ideas???
View 1 Replies
View Related
Jun 23, 2006
HI
I do not know what I have done but I am banging my head against the wall, I have three tables in this recruitment db im making and I have a problem that I know is so simple but is killing me off.
In one of the forms (add new vacancy) it has a drop down box that selects the name of the company from table called (company main) within the company main I have two columns (company) & (company no).
now back in the add new vacancy form I have a box that should collect the informtion of the company no whenever I select the company from the drop down list, but you guessed it, its not finding the information please help before i go mad.
View 2 Replies
View Related
