Hi
I was wondering how to create a form which is using data from three tables, the data in the tables does not have any relationship setup as they are not related to each other.
When I use the wizard and I select the three tables, Access starts complaining about the fact that no relationship has been setup...
Please help,
Thank you,
Gurkie
In my database I have a table that keeps track of a package of items. The package is assigned a package type (counter display, end cap, half pallet, full pallet for example). The record of the association of the package and it's type is held in the main table.
Each of these package types is either a case or a pallet (counter display and end cap are cases and half and full pallets are pallets) This relationship is kept in another table (we will call it description table).
Now, based on the type of package and therefore it being a case or pallet a UPC and a GTIN number are assigned. The GTIN number is different if it is a case or is a pallet. I have a table that stores all of the UPC and GTIN numbers available in 3 columns, one for UPC, one for GTIN Case and one for GTIN Pallet (the UPC is a standard 12 digit and the GTINs are 14 digit -with the first 2 different to designate pallet or case. and all are based on the check digit formula necessary)
The user assigns the UPC and correct GTIN number by clicking a button which applies the next available UPC code to the package and determines if the description of the type (case or pallet) and inserts correct GTIN number into that field. I actually have all of this functioning correctly.
Now the problem. If a user changes the package type, and therefore changes the description, I need to add code to the update event of the combo box that gives the choices for package type that does the following:
Check to see if the original package type was a case or pallet (it's description) and if by changing the package type it is now changed to the other, update the record in the main table to the correct GTIN number based on the existing UPC Code.
OR as I write this, maybe the code could simply update the main table with the correct GTIN code based on the new description and the existing UPC code. This was I would not need to check for a change just do the update every time.
I am working on a dataBase and I need to have it check if a set of tables exist based on the data of another table and if any do not exist create the table(s) based on a template. the caviat is that the data in one table are both names of tables and field in another.
Table1 has fields named 'first name' and 'last name'; the data in that field is for example Peter Adams, John Smith. I need to check on start that the tables named PAdams, JSmith.and so on exist, if not create them from a table template with fields 'vacation', 'personal', 'sick, etc. also i need to check on startup that a table named Department exist; if not, create it with fields named PAdams, JSmith and so on.... is that possible?
I am using data from a sales and a finance system and essentially need to understand the difference between them and account for this "balancing" row.
My data is as follows
TBL_Sales
Month, Product_Type, Factory, Quantity, Sales, Margin
January, A, F123, 100, 1000, 10
January, A, F123, 800, 8000, 80
January, B, F123, 10, 100, 1
January, A, F124, 500, 10000, 1000
February, A, F123, 100, 1000, 10
February, A, F123, 800, 8000, 80
February, B, F123, 10, 100, 1
February, A, F124, 500, 10000, 1000
TBL_Finance
Month, Factory, Quantity, Sales, Margin
January, F123, 1000, 10000, 100
January, F124, 550, 10500, 1000
February, F123, 950, 9500, 95
February, F124, 600, 10000, 1000
I would like a query which would look at the difference between TBL_Sales and TBL_Finance and will then add rows to TBL_Sales to make them balance. In the example above I would want it to add the following (I've used C as a Product_Type to show that it's a manually entered value) -
TBL_Sales
Month, Product_Type, Factory, Quantity, Sales, Margin
January, C, F123, 90, 900, 9
January, C, F124, 50, 500, 0
February, C, F123, 40, 400, 4
February, C, F124, 100, 0, 0
This will be a definate newbie question but here it goes.... I am new to Access and fighting my way through building a database to learn. I have broken my tables down to as normalized as I am willing to go, mabey a little further than needed. So now I have several tables that are related. I need to know how to make a form update several tables at once and prevent the user from having to mess with the autonumbers that are created. Here is a sample of my tables:
All Primary Keys (PK) are autonumbers
tblStaff
StaffID PK
StaffTypeID FK to tblStaff
FirstName
MiddleInitial
LastName
SSN
AddressID FK to tblAddresses
tblStaffTypes
StaffTypeID PK
StaffType
tblAddresses
AddressID PK
AddressTypeID FK to tblAddressTypes
AddressLine1
AddressLine2
CityID FK to tblCities
StateID FK to tblStates
ZipID FK to tblZips
tblAddressTypes
AddressTypeID PK
AddressType
tblCities
CityID PK
City
tblStates
StateID PK
City
tblZips
ZipID PK
Zips
I now know that I may have taken the normalization a little further than needed but I have it done and would like to use it if I can.
I want a form where a user can add a staff member with all of their information on one form (name, ssn, and address) without having to deal with the autonumbers or needing to do it in a certain order to make it work. I also don't want excessive use of combo boxes (state is OK). So far I haven't found a way to do this, please help.
I need help. I am not sure if this is a form issue or a logic issue i have a db for tracking students the classes they take and their notetakers for classes. Each class is most likely unique to each student but sometime there are multiple students in each class that need a notetaker and there should only be one notetaker per class. I have 3 tables and they are all linked through a unique iD called CRN (number).
I am looking for a way to add a student to a class and create a class at the same time in one form. I am trying to limit the redunancy by adding a class each time and then adding a student to the class.
Any suggestions would be great. I have gone through 10+ redesign and am just at a loss on how to accomplish this.
I have a form within my database in which the user will enter data which will go into 2 separate tables. These 2 tables (Job and Client) are related. At the moment I have a subform in which the user enters Job information, and the main form where the user will enter client information.
The problem being is that the 2 sets of data do not associate themselves with each other, despite being related (The Client will be related to a job number. A client can have many jobs but a job can only have one client etc). It has to be done manually in the table which is not ideal as the DB will be split and rolled out to users via Access Runtime. I have been working on this DB for a while now and the problem is most likely right in front of me but I cannot see it!
I have a database I made to store a list of users and information about each user.
I have a UserDetail table, languages table, previous experience table, current experience table.
UserDetail table as follows:
UserID (PK)
First Name
Last Name
Full Name (calculated)
Department (using a multi select combo box. There are 3 departments and some people are in both).
I need to do the same thing on each table and each table is very similar so I'll just list one. This is the Previous Experience table:
PreviousXPID (PK)
Previous experience (e.g. IT, Marketing, Chemistry)
I have three junction tables. One for each Previous Experience, current experience and language table. The Previous experience junction table has:
ID (PK)
UserID
PreviousXPID
I created a relationship between the userdetail table and the junction table then the junction table to the Previous Experience table.
Now what I'd like to be able to do is edit people to add previous experience, current experience and languages to each. I already have a form that lets me add a new user to the database. It's just based on the UserDetails table. If I could have a way (perhaps using 3 multi select list boxes. One box for previous experience, one for current experience and one for languages) on that form to add the other details to that person, that would be ideal. I'd like to create a new user. E.g. Joe Bloggs in department 1 who speaks Dutch, used to have Marketing, Chemistry and aeronautical experience and now works in IT.
I can create the new user by putting last first/last name and selecting the department. But to add experience or languages, I have to go into that junction table and add the numbers myself. e.g. user 1, has experience 1,3,4 and 5. I'd like to see the name that relates to the ID and be able to select it from the list in the table I have.
So I have a form, it has a few fields from a related table in it, related by the name. I click 'new' on the form, but when I click save Access complains about the related table does not have a matching record. How do I have it create a new record?
View 2 Replies View RelatedHi all,
Right trying to create a database that is centered around displaying images.
I already have my forms and navigation working fine but need some help with the images. what i would like, if possible, is on the forms one image is displayed but it has navigation buttons underneath to switch between images in the form. nothing fancy just that!
if anyone has done this before or has any ideas it would be greatly appreciated!!
cheers
chris
I have my Assets form and the primary key is the ChargerID, in this form I have an "Add New Job For This Asset" button, which opens up the Jobs form at a new record.
How do I make it so that the ChargerID field is automatically filled with whatever the previous record was instead of being blank.
For example if I have Charger12345 open in the Asset form, I'd like to click the Add New Job button and it automatically have Charger12345 in the ChargerID field of the Jobs form.
i have been trying to create an employee database to record:
1. Name , Address
2. Telephone
3. Date of birth
4. employee ID#
5. Emergency contact -name, address, telephone
6. Photograph
7. Other pertinent information related to employees
So far I have incorporated the contacts database forms into my new database but I am unable to link the forms into one form that will incorporate all the information needed. Is there an existing employee database that I may edit to suit my purposes?
Hey guys,
OK, your gonna have to bare with me a little bit as its hard to explain and if any VB is given please add a few annotations as I have to explain everything i do in a report (doest have to be too detailed, just to make the code understandable :) ) and if it needs better clarification feel free to ask :), but basically, I have the following relationship set up:
http://img512.imageshack.us/img512/3246/relationnshipswd9.jpg
At the moment, because of the way it is set up, I cannot create a record in the transactions table unless an income record is given for it (because tb_income (one) to tbl_transactions (many)) but the way I want to work is as follows:
If you have a look at the tables tbl_transactions and tbl_income and their link. The way I want the system to work is when a new transaction is made, a new income record in the "tbl_income" table would be made with the date (in tbl_income) being the date at that particular time and all transactions created on the same date would all go in the subdatasheet for that one record created for that date; and if another transaction is made on an alternate date (say 00:00am of the next day) another income record would be created automatically with the date being of that particular day etc.
My other problem im facing is that everytime there is a transaction created, I want the stock level(s) field of products in that particular transaction to be decreased by the quantity purchased of that product but i have no idea how to do so
Any help?
Thanks a lot in advance!
Daniel
I'm currently working on a database which requires invoicing as a part of it. The invoicing is done based on quarters, and I want the users to be able to use a multiple items form, listing all of their clients, to create the invoices. Each invoice must be created individually so they can be e-mailed to the client, and saved to the clients folder. So I was wondering if it would be possible to create individual invoices for clients using a multiple items form.
View 1 Replies View RelatedI have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?
This is a skeleton view of what I'm going for:
Master Table:
tlbAuditReportHeader
- AuditDate
- AuditArea
- Auditor
Sub Table:
tblDiscrepancy
- Discrepancy
- CorrectiveAction
- ActualCompletionDate
- VerifiedDate
- Notes
Sub table to Sub Table
tblFollowUp
- FollowUpDate
- AssignedTo
- SpokeWith
- EstimatedCompletionDate
- Notes
Sometimes tasks change hands or are pushed back depending on work load. It would be nice to be able to track something like this.
Hey all.
I'm having a few issues with creating a Lookup on an Access form. I'll recreate the scenario below as best i can to help with my explanation.
I have 2 data entries. The first is a Post Code (Zip code), and the second is an Area Rating that applies to the Post code. For example:
Post code AB1 8 is awarded a rating of A
Post code SO1 3 is awarded a rating of F
I have already created a table in Excel with all 2,922 Post Codes (AB1 1 to ZE3 9) and the corresponding Area Rates (A to F)
What i'm making is a form that holds customer details. I'd like a combo box or text box that when you type in the Post Code, another text box automatically displays it's corresponding Area Rating.
Code:Post Code: Area Rating:------------- --------------| CM2 7 | | A |------------- --------------
I apologise for my really very poor diagram :P
So the Post Code box is either typed into or a combo box with the list of Post Codes, and the Area Rate box cannot be typed in to, but it automatically displays the record dependant on what is in the Post Code box.
Ideally, these will also be recorded onto the main table (IE not the table with the list of post codes or area ratings, but a 3rd table holding the rest of the details that will be used to create statistics and graphs)
Can anyone help me with how to do this? I've been trying to work it out all afternoon!
Any help is really appreciated.
I have a form displaying the 11 fields of the parent/primary table using a selection from a combo box. I am using queries and vba code modules respective to form, combo box and command buttons. I have initial code that uses the two fields from the combo box selection to append same to a new parent/primary archive table. I now want to add to the append SQL the remaining fields to the parent/primary archive table. When I add the second sql string for the remaining fields to the same procedure and execute I keep getting 'null in primary key'. If I copy the primary record and paste same into the archive table it works.
Private Sub Command26_Click()
On Error GoTo Err_Archive_Primary_Click
Dim strSQL As String
Dim strSQL2 As String
strSQL = "INSERT INTO ARC_289325045 ([Survey Point ID], [Survey Area Detail], [Date On Site]) " & _
"VALUES ('" & Me.cboAreaDetailDate.Column(0) & "','" & Me.cboAreaDetailDate.Column(1) & "'," & _
"#" & Me.cboAreaDetailDate.Column(2) & "#)"
CurrentDb.Execute strSQL, dbFailOnError
'strSQL2 = "INSERT INTO ARC_289325045 (RecordID, UnitID, UserName, [TimeStamp], [Survey Point - Area], Measurement, NewArea, [EXIT Form] ) " & _
'"SELECT FORM_ID_289325045.RecordID, FORM_ID_289325045.UnitID, FORM_ID_289325045.UserName, FORM_ID_289325045.TimeStamp, FORM_ID_289325045.[Survey Point - Area], FORM_ID_289325045.Measurement, FORM_ID_289325045.NewArea, FORM_ID_289325045.[EXIT Form] " & _
'"FROM FORM_ID_289325045"
'CurrentDb.Execute strSQL2, dbFailOnError
Exit_Archive_Primary_Click:
Exit Sub
Err_Archive_Primary_Click:
MsgBox Err.Description
Resume Exit_Archive_Primary_Click
End Sub
The next step is to do the same for the child table and append related records to the child archive table.
I have a form displaying the 11 fields of the parent/primary table using a selection from a combo box. I am using queries and vba code modules respective to form, combo box and command buttons. I have initial code that uses the two fields from the combo box selection to append same to a new parent/primary archive table. I now want to add to the append SQL the remaining fields to the parent/primary archive table. When I add the second sql string for the remaining fields to the same procedure and execute I keep getting 'null in primary key'. If I copy the primary record and paste same into the archive table it works.
Private Sub Command26_Click()
On Error GoTo Err_Archive_Primary_Click
Dim strSQL As String
Dim strSQL2 As String
strSQL = "INSERT INTO ARC_289325045 ([Survey Point ID], [Survey Area Detail], [Date On Site]) " & _
"VALUES ('" & Me.cboAreaDetailDate.Column(0) & "','" & Me.cboAreaDetailDate.Column(1) & "'," & _
"#" & Me.cboAreaDetailDate.Column(2) & "#)"
CurrentDb.Execute strSQL, dbFailOnError
'strSQL2 = "INSERT INTO ARC_289325045 (RecordID, UnitID, UserName, [TimeStamp], [Survey Point - Area], Measurement, NewArea, [EXIT Form] ) " & _
'"SELECT FORM_ID_289325045.RecordID, FORM_ID_289325045.UnitID, FORM_ID_289325045.UserName, FORM_ID_289325045.TimeStamp, FORM_ID_289325045.[Survey Point - Area], FORM_ID_289325045.Measurement, FORM_ID_289325045.NewArea, FORM_ID_289325045.[EXIT Form] " & _
'"FROM FORM_ID_289325045"
'CurrentDb.Execute strSQL2, dbFailOnError
Exit_Archive_Primary_Click:
Exit Sub
Err_Archive_Primary_Click:
MsgBox Err.Description
Resume Exit_Archive_Primary_Click
End Sub
The next step is to do the same for the child table and append related records to the child archive table.
Edit/Delete Message Reply With Quote Quick reply to this message
JJKramer
View Public Profile
Send a private message to JJKramer
Find all posts by JJKramer
Add JJKramer to Your Buddy List
Sponsored Links
In my database I have:
* A table of consultants
* A table of contracts
* A table of assignments (shows which consultants are assigned to which contracts, along with the start and end dates of those assignments)
* A table in which consultants book their time to projects
The consultant assignments change over time. What I need to do is create a query that shows a) the amount of time a consultant booked to a project(s), and b) the contract to which he/she was associated at the time. For example, if a consultant was associated to Contract A in February, Contract B in March, and Contract C in April, I want to be able to accurately reflect that in my query. I'm not sure how to go about doing this in my query.
I'm sure many here have solved similar problems, so any help you can give will be appreciated.
I am trying to create an input form for input to tables.There is a main TblContacts table with fields such as: LastName, FirstName, Address, etc. There are also ID fields for the Company and Country and joins to the Company and Country tables, each with just ID and name fields.I want to have all the fields from the TblContacts table and the Name fields from the Company and Country tables on the form. I want to be able to select the Name fields from the Company and Country tables from combo boxes and for these also to populate the Company and Country ID fields (from the TblContacts table).
To create the form, I selected Create Form whilst the TblContacts was open. Then, I added the combo boxes pulling the names from the Company and Country tables.My problem is that the selection of the Company and Country names above is NOT populating the Company and Country ID fields on the form (taken from the TblContacts table. I have checked in relationships that the joins have been created.
I have 2 table,
Table1 has fields
docno (text)
title (text)
progress (number)
sample data is like below:
doc-001 test1 90%
doc-002 test2 25%
Table2 has fields
sample data is like below:
id (number)
icon(attachment type)
1 image1
2 image2
Now I would like to make a query when progress of each docno is equal and more than 50% it uses image1 And when progress is less than of 50% it uses image2
Doc-001 test1 90% image1
Doc-002 test2 25% image2
is there anyway you can create one form which updates all the information in multiple tables. I need to create one of these to make the navigation of the database more easy, so if anybody could help me it would be much appreciated.
thanks.
Hi,
I have built an Access DB containing 3 tables: dimensions, time, companies. The tables are not linked and are to be used to look up values for the new form. The goal is to create an Access form that would allow the user to select distinct values from all 3 tables, enter some own data and then execute an append query to add the record to the main table.
Something like this:
Initially I have 3 tables:
Prepopulated Dimensions table with fields:
dimension
....
Prepopulated Time table with fields:
Date
Day
Month
Year
Prepopulated Companies table with fields:
Company
....
My form is to be able to select distinct values (combobox) from all three fields:
Company
Dimension
Day
Month
Year
Value (data entered by user)
The record then is appended to the Main table containing:
Company
Dimension
Day
Month
Year
Value (data entered by user)
Thanks a lot for your time and help!
Polar
When I have a simple table, users pictures inside maps (jpeg,png...) defined as OLE object, when creating the form from that tables, don't have picture shown, only name of the picture? I tried all the options, package, copy, paste... nothing...always the same... just picture name but no picture.
View 1 Replies View RelatedI am creating a database of bird banding and breeding records for the refuge where I currently intern. To do this, I have created the following tables:
Adult Banding Records
Nestling Banding Records
Historical Banding Records
The specific issue:
I have already created forms for entering data into these tables, but now I would like to create a "search" form that will use the primary key (which is always the band number of the individual bird, across all tables) to search ALL the tables and pull up all the information on that record. This will be convenient when we recapture a bird that is already banded, so we can look up their information in our database. Also, since I plan to add many more tables, it would be nice to not have to search each table individually.
How do I create a form that will allow me to enter data into three different tables?
I have a rather simple database for tracking students. When I get a new student I need to take information off their paper application and enter it into three separate tables, Student, Families, and Demographics. Each of these tables contains the fields StudentID, StudentFirst, and StudentLast.There is a relationship between the StudentID field on each table, with Student being the main table and Families and Demographics coming off of it. All relationships are one to many. StudentID is the key for the table Student.
I want to create a form to add a new record to each of these tables. I want my data to be displayed in the Columnar style. I only want to enter StudentID, StudentFirst and Studentlast once and have it populate to all three tables.
I can create a form and a query that displays existing entries exactly like how I want to enter them, but of course I can not edit or add to them. I have tried using subforms, but they don't seem to link up. Once I enter the Name, I want it to be on all the forms. I don't want to have type it three times. I also tried creating three separate forms and connect them with the Navigation Form. The issue again is that once I enter the name in one form, it is not automatically on the next form.