Basic Query Form, Foreign Key Field
Oct 18, 2006
hello.. i have 2 tables
CUSTOMER TABLE
customerid
customername
customerphone
BOOKING TABLE
bookingid
bookingdate
customerid
At the moment i have 2 forms.. one customer form based on the customer table and one booking form based on the booking table.
at the moment, my user enters a customer record using the customer form.. they then save and close the customer form..
they then open the booking form.. and in the customer id field they enter the customers id.. this way, they successfully register that customer to that booking..
what i want to do is allow the user to enter the customer details and the booking details in one form..
i assume i would have to create a third form based on a query..
if i include all the fields from both tables in this query.. and then make a form based on this query, how can i make the following 2 things happen..?
1. when the third form is opened and customer details are entered... a new customer record will be added to the customer table (with a new customerid)..
2. this id will then need to be auto placed into the foreign key customer id field in the booking table.
do you get me?
View Replies
ADVERTISEMENT
Jul 1, 2007
I've sent a post about this before and got some of the answer from 'Dennisk'- thanks.
I want to replace composite keys with a primary. I've just made an autonumber field on one table for my new primary key and already populated it. On the foreign table ive made a new field with 'number' datatype; this field is empty at the moment.
I'm not sure how to update the foriegn tables new field with the primary tables data. I've saw it before and remember it being easy, just can't remember how to do it.
I obviously want the numbers in the primary key field to relate to the correct records in the foriegn table.
Anyone know what the query is or if there's another way?
Thanks:confused:
View 1 Replies
View Related
May 24, 2014
I am trying to auto populate a form from a table containing CustomerID, CustomerName and PCCID_FK
Now in the form I want to auto populate the field cmbPCC when I update the cmbCustomer
using dlookup I've managed to show it manually
=DLookUp("PCCID_FK","tblCustomer","CustomerName='T est'")
It gives the ID of PCCID_FK which is what I want. how do it I modify the expression to replace the TEST part with a value in a combo box.
or is there a better way of accomplishing this.
View 1 Replies
View Related
May 30, 2005
Dear All.
I have a visual basic function, placed in a module.
I need to call it from a form field, and pass a value to it and the function has to return a value.
I tried it but it doesn't work
the value I get on the field is " #Name? "
any help will be very appreciated.
Thanks!
CS.
View 8 Replies
View Related
Nov 16, 2013
i have made two tables with data from an excel sheet. The excel sheet has many duplicates and im trying to eliminate this. The tables are:
tblTasks and tblTeam. Both have autoincrementing primary keys, and the tblTasks table has the TeamID (primary key from tblTeam) as a foreign key.
My question is, how do i populate the TeamID field via perhaps a query, as it is blank on all records. I have over 5000 tasks so a manual approach is what im trying to avoid. A sample of the fields in the tables is as follows:
tblTasks
TaskID - PK
Task Name
Team ID - FK
tblTeam
TeamID - PK
Team Name
there's also a Staff table. tblTeam has a one to many relationship with the Staff table. tblTeam also has a one to many relationship with the tblTasks table.
View 3 Replies
View Related
Aug 2, 2005
I have a form where all records are listed on one page with a vertical scrollbar (all simple textboxes):
----------------------------------------------
PrimaryKey Name ForeignKey
----------------------------------------------
1 Pam 20
2 John 20
3 Kim 20
4 Larry 20
... underneath these on the form is a blank record for NEW additions, which obviously has nothing in any of its textboxes, waiting for user to input some data, as normal.
The Foreign Key, due to the nature of my DB, will always be the SAME value for each record in the DB (don't ask, it's part of an export/import-to-main-db app). So I know that the NEW record, if created, will have the same value as the rest of the records.
How do I get it so that when a new record is created, the Foriegn Key textbox is automatically filled in with the value from the row above? (Due to referential integ reasons the Foreign Key field MUST be filled in accuratly or else an error ocurrs.)
Looking at the various events, I realise that it is the BeforeInsert event that will sort this out, but I'm at a loss regarding how to get the NEW record's Foreign Key textbox to match the Foreign Key value above it.
Any advice?
View 4 Replies
View Related
Aug 28, 2013
I've got a table - "Products" - in my database, with a text field - "Info" - which contains info about products.
Within this field I would like to have footnotes. To do this, I think the best way to do it is by putting numbers inside the text at the location of where I want the footnote to refer to. These numbers will actually be foreign keys to a table called 'Footnotes'.
I can then program the forms and reports to show any numbers as superscripts or whatever.
(Of course, if the user will actually want to insert a number into the text field which is NOT a reference to a footnote, I will have to make a workaround e.g. by making access put a symbol in front of the number, so access will know the number is just part of the text (and I will program the form to not show the symbol in front of the number)).
Is it wrong to have foreign keys within a text field? I think if executed correctly, it should work perfectly.
View 8 Replies
View Related
Jun 19, 2013
I basically have an Issue's table and a Customer Table relationship set-up.
I want to information to be viewed in the Customer table however I can only see 1 Issue where as a Customer may have 2 or 3 Issues.
I have come up with inserting the whole table into the form however I don't like this look.
View 5 Replies
View Related
Jan 12, 2006
Hi
Im an absolute noob when it comes to Access so please bear with me
For work I want to make a database to keep track of ordering, so thus far i have made 2 tables: suppliers and products
in my form to add products, i want it to display a drop down with the supplierName in it, but store the supplierID in the products table.
I figure this should be pretty simple, and could easily do it if this were php....
Can anyone help?
View 4 Replies
View Related
May 19, 2006
Hello,
Help appreciated,
trying to create a form so that when I type in a value it will bring back held in a table it will display two fields held in the same row. i.e.
Table 1
no
first name
surname
I want to type the no field and it displays the appropriate first name & surname fields for the row.
View 1 Replies
View Related
Sep 20, 2005
I have written a basic query that looks at the date field, department field and cost the cost field on a table. What I would like is a form that you can chose a date (19/09/2005) and a it looks at the table and calculates the total sum of money for each department for this date. Ie.
Date: 19/09/2005
Dept 1 = £123.00
Dept 2 = £1234.00
Dept 3 = £123.00
Dept 4 = £1234.00
Dept 5 = £123.00
Dept 6 = £1234.00
I can get the query to work but only 1 department at a time. Any ideas?
I have attached the DB
Thanks
D
View 6 Replies
View Related
May 22, 2006
Hello all This is a pretty basic query I guess but I am having trouble solving this.. Can anyone help plsssssssss.
I have two tables
Table1
Code Serial_No Amount1 Amount2
1 ABC 100 200
1 ABC 720 220
4 ABC 150 112
9 PQR 11 25
9 ZZZ 122 44
9 ZZZ 1250 42
Table2
Code Serial_No Amount1 Amount2
1 ABC 24 20
1 PPP 72 22
4 ABC 15 12
9 PQR 11 54
9 ZZZ 22 44
9 ZZZ 150 99
and total of the amount as 844 and 440
I have write a query to select only the Code common to both the tables and add total of amounts for these codes. For eg. I want rows
Outpput
1 ABC 100 200
1 ABC 720 220
1 ABC 24 20
Thanks in advance
View 4 Replies
View Related
May 4, 2006
I need some help creating a simple datebase to store my documents and other knowledge objects. There are two types of
knowledge objects I need to store: Documents and Questions/Answers. For this reason I have created three tables: Index,
Question, and Document.
The problems I am encountering is that the ID field in the Index Table is the Primary and relates to the ID field in the
other two tables. So basically when I am entering information it can either be a question or document. I want to create a
for so that when its a question that I am entering it records the information in the question table and when its a document
I am entering it records the information in the document table.
I'm a beginner at using access and don't know VB or SQL. Any help would be appreciated.
Table Structure
Index Table
ID (Primary Key)
Link
Type
Source
Reference
Question Table
ID (Foreign Key)
Question
Answer
Document Table
ID (Foreign Key)
Title
Summary
Notes
View 1 Replies
View Related
Aug 8, 2015
I have 7 fields in a listbox (1st is the ID, hidden) from the single table. 3 of these fields are foreign keys. How do I get them to display their related values instead of the foreign key value?
Background:I'm purpose-building my db to essentially track individually cataloged items, somewhat like a library system would. I have four main tables: tblMediaItems, tblUsers, tblTransactions, and tblLocations. Users wills be spending 90% of their time on the Details form for the particular media item record they're viewing.
I'm trying to show an item's transaction history on the main form. I added a listbox (because I liked that compact presentation style) and got the desired fields to show up from tblTransactions. I figured out how to accomplish this for a single column combo box with the wizard, but so far I don't see how I would do this with multiple fields in a listbox.
View 2 Replies
View Related
Jun 27, 2005
I have forms in a database that I am looking at. How can I tell which table the forms are connected to?
Easiest thing, I know. Help much appreciated.
-Mike
View 1 Replies
View Related
Sep 20, 2005
OK, here's the deal. I have two data tables, one of which holds many types of customer information and has a very large number of records, including, name, address, phone, and email. The other table contains only email addresses. Each email in the second, smaller table corresponds to a record in the larger list , by virtue of both records sharing the same email address (primary key?). I need to be able to take a short list of email addresses from one table and produce all of the records from the larger table that have corresponding email addys, essentially. Can somebody shed some light on how I can structure a query to solve this dilemma? Sorry for the newb question, just getting started out using db's.. Thanks!
View 1 Replies
View Related
Aug 10, 2006
Hello, i have a small database thats purpose is to show when a book has been returned to me. I am recording the date that books are loaned out. Books are loaned for a 2 week period. I dont know if it would be easier to just manually put in the date that books should be returned (so that means just manually calculate what the date will be 2 weeks later). The reason for this is because i want a query that shows when the date has passed and how many days overdue the book is. Would this be difficult to achieve? What i was thinking is a query that calculates when the 2 week period is up and adds the unique number for book in a report.
Can anyone help?
Thanks,
Marley.
View 2 Replies
View Related
Nov 28, 2006
i have a very basic query question that i can't seem to figure out.
i have two tables that have a one-to-many relationship - one has patient ID's, and the second table has test results for each patient.
i would like to query for all positive test results - however, if a patient EVER had a positive result, i want ALL records for that patient returned.
thus, if a record in the "many" table meets a particular criterion, i want it to return all records with the same ID. does that make sense?
thanks in advance...
View 6 Replies
View Related
Jul 11, 2007
hi..
quick question...
i want to update all the records in a certain field..
to make the first letter of each word a capital
to make every letter a capital
i know how to make un update query, what to i put in the criteria if the field is called
field1
cheers
View 5 Replies
View Related
Sep 20, 2005
I am trying to incorporate a field in my customer order form for "Invoice Number". Not every customer order form will have an invoice number.
The Invoice Number field will default as null, or blank. I want to make it so the Invoice Number gets automatically populated ONLY IF another field on the same form is populated with "X".
Also, I would like the Invoice Number to equal the Previous Record that has an invoice number + 1. For example, if the previous 5 client records did not have an invoice number (blank because the client's form did not have a certain field populated with "X"), but the previous 6th record had an invoice number of 1001, then the current invoice number would be 1002.
So far, I just have the Form for the underlying table created. I don't have the invoice field in the table created yet. Not too sure where to start.
Thanks much appreciated in advance.
View 3 Replies
View Related
Mar 16, 2005
This is probably really basic stuff to most of you, but here goes.
(In the past, I have always had tables with zillions of fields, rather than having linked tables, so I have never had to mess with this stuff before...)
I have a db with 2 tables. A Main table with, say, contact names and addresses and a Lesser table with specialised info that I only want for some of those contacts.
The Primary Key in the Main table is fldContNum (autonumber), the primary key in the Lesser tables is fldNum. The two tables are linked 0ne-to-one on those fields.
There is not a record in the Lesser table for every record in the Main table.
I have a form showing records from the main table and a sub-form showing records from the Lesser. They are linked Child fld Num / Master fldContNum.
At the moment, I can go to a Contact from the main table and I can enter info for him in the sub-form, thus creating a record in the lesser table. That works ok.
However, if I move the main form to another contact and then go back, that info that I just entered does not show in the subform. In other words the subform is in "add new record" mode, whilst I want it to go automatically to the existing "Lesser" record with that number.
I can make it do this with VBA and searching recordsets and suchlike easily enough, but I feel that there must be a much more basic way of linking the two form and tables.
Also I want to make sure that if there is already a "Lesser" record with that number, I want to make it so that the sub form cannot even try to make a new record (because then I obviously get primary key violations).
I hope that I have made myself relatively clear here...
Thanks
George
View 2 Replies
View Related
Apr 3, 2005
Hey,
Got a simple question really...
Designing a form and I have boxes where the data is entered.
The form background is grey and i would like the data boxes to be grey
as well, unless the box is "active" meaning its selected.
ive done it for one form but i can't remember how on earth i would do it on another from i need to do.
Many Thanks
View 1 Replies
View Related
Dec 12, 2005
Greetings, I'm a bit new to the world of MS Access and have encountered a problem when making a search form for my database. The query is using a Like Condition with the '*' Wildcard for a single field like so:
Like "*" & [Forms]![frmPickUpReport]![txtAgentName].[Text] & "*"
While the query on its own runs correctly from the viewer of course prompting me to enter the data manually the actual data from the form is being ignored. Instead of taking the text value within my form's text box, when I press the report creation button the same data box is prompted to me asking for me to enter the data manually.
anybody has a solution?
I'm using MS Access 2000 and cannot use any other application such as visual studion
View 3 Replies
View Related
May 11, 2006
I am not sure if I understand this...
I have MainTable, on which I base MainForm. I would like to have MainForm show only the records that have a null value in CertainField. If I write NullQuery to select only those records, can I redirect MainForm to NullQuery? Well, I know I can do that... but how does MainTable get updated with new records if MainForm is based on NullQuery????
Any help is greatly appreciated.
Tom
View 4 Replies
View Related
Jun 5, 2006
Hi All,
I have two tables; "Section_Failure_Mode" and "Risk_Assessment". They are linked by a common field called section_failure_mode_id. I would like to write a query where you can delete every record in "Risk_Assessment" where the checkbox (called Selected) is not selected (i.e. equal to 0) in the table "Section_Failure_Mode". However, I do not want any records to be deleted from "Section_Failure_Mode", only from "Risk_Assessment".
I have put a link to the screen cap of the query I have tried below. Is this the correct way to do it?
13797
Best Regards,
Aaron
View 1 Replies
View Related
Sep 18, 2006
I have an access Query(named newSerial) :
SELECT TOP 1 (Productbase.Serialnumber+1) AS Expr1 FROM ProductBase
ORDER BY (Productbase.Serialnumber+1) DESC;
I want to access this new value. Its not a key since an autonumber may be any number. When I create a new product. How do I get this value inside a form?
It works fine when clicking on it. But if I use an unbound box I get 1 as result and not like 76067 which it should be. If I click the query in access it works fine and show 76067.
I tried:
outbox=Expr1
' gives null
'or
outbox=[newserial].[Expr1]
' which gives "Access cant find the field "|" refered to you in your expression"
What should I write? Whats the correct expression?
View 7 Replies
View Related
