Hi there
I am hoping someone can give me a few pointers on how to get started on what I am trying to achieve....
I have 2 tables that form a 1 - to - many relationship.
I have designed a form that I wish to show fields from both tables.
Using ADO I would like to be able to open the form and be able to see my desired fields in the same form.
Can anyone provide any ideas on where I can get started.
Thanks for your help!!
I have the following Select Statement:
SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],
[code]....
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.
Code:
SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID )
ON trains.trainID = bookingLeg.tid) as firstJOIN
Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?
Hi.
Select Materials.ID, Materials.AvailableUnits, Substances.Name, Suppliers.CompanyName FROM Materials Right Join Substances on Materials.Substance = Substances.ID
This works in joining the substances.name (from substances table) with the value stored in materials.substance (which is ID of substances.ID)
How can I add another Join to join Suppliers.CompanyName with suppliers.ID. The current Materials.Supplier is a key of Suppliers.ID which holds the value of Suppliers.CompanyName. I've tried this a million times and as soon as I insert a second join, no matter what format I get "syntax error in join statement".
PLEASE PLEASE help!
Hi,
I have a problem about SQL outer join
I have two tables
-----------
table one
id value1
1 a
2 b
3 c
-------------
table two
id value2
2 d
4 e
5 f
---------------------
I like get the results
id value1 value2
1 a 0
2 b d
3 c 0
4 0 e
5 0 f
* if no value put 0 .I am using SQL Server 2005 now
I try left join ,right join and full join, but I can not get the results.Please help me.
Thanks for help
Mark
Hi,
I am trying to do an update on a table with a query which has a join as follows but getting an error:
UPDATE RoleObjects
SET AccessType = 1
FROM RoleObjects
INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID
WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')
I tried to debug the code and found that the following part of the code with the Select clause works fine.
SELECT *
FROM RoleObjects
INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID
WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')
Do u have any suggestions.. ??
Thanks a lot for your time.
I've been struggling with this for awhile now. I've been trying to research a solution but I'm not even sure what to search for.I have the following table structure:tblContactsContact_ID (PK)First_NameLast_NametblBidPackBid_Pack_ID (PK)Bid_Pack_DescriptiontblDistributionListList_ID (PK)Bid_Pack_ID (FK) (1-M)Contact_ID (FK) (1-M)What I would like to do is create a query to display all of the contacts from tblContacts that are not associated with a certain Bid_Pack_ID. My end goal is to create a form where I enter Distribution List Information. I want a listbox on the left with all of the contacts from tblContacts and a listbox on the right with contacts that have been selected for a particular Bid_Pack. When the user double clicks an item on the left it should show up on the right and be removed from the left. I'm looking for the same functionality as when you use the design wizard and you choose fields to include/exclude.For example, let's say I want to add contacts for distribution list "A". My thought is the box on the left would show all the contacts from tblContacts that aren't selected for distribution list "A" and the box on the right would show all of the contacts that are part of distribution list "A". As you double click an entry it would perform the appropriate record creation/deletion and each box would be required.So back to my original question, what is the correct method (relationship, join, querydef) to select all of the contacts from tblContacts that haven't been assocatied with a particular bid pack?Thanks for any help you can provide. Even a nudge in the right direction would be appreciated.
View 2 Replies View RelatedI'm sure this is easy to do, but I'm not sure how to word my search criteria when trying to look for a solution.
Let's say that I have 2 fields in MyTable: FirstName, LastName.
I've been trying to use the following query, but obviously it does not work:
Select (FirstName + ' ' + LastName) as MyName from MyTable.
I would like to have the following data returned to me in the following format:
John Doe
Jane Doe
Jim Doe
Jill Doe
Thanks,
CRhodus
Okay - the other database is in the works and is going sloooow (the one that is being created with normalization).
Meanwhile, my other database that has no normalization I am having a problem running a query (now I know why its so important to have good structure).
To give you an idea on how bad this is...:o .....
3 tables - 94 fields to each table - each table has identicle fields, just different data. :eek:
Anyways, I have students in each table (each table is an Annual Report). I want to be able to run a simple query and combine all the students into one.
How would I achieve this?
Thank you!
And for all who are thinking about designing a database - READ READ READ and do some more reading on normalization!!!
helooo...
i have 3 tables -Recipes, Ingredients and Products.
Recipes table:
RecipeID -PK
Ingredients table:
IngredientID -PK
IngreRecipeID -Foreign key to Recipes table
IngreProductID -Foreign key to Products table
Table:
ProductID -PK
how do i join them into 1 recordset? :confused:
HELP!!! :confused: I do have a 5 tables 4 tables do have a foreign key of the main table. I join the 4 tables with the main table but when I am editing the information I cannot edit it. and no error appears. so I am just wondering what happen with my joined tables? please help me!!!!Thank you in advance
View 1 Replies View RelatedHow do I join two tables. I have a table and a lookup table. My table has products on there that are listed as custom or basic. I have a look up table that has an ID for basic and Custom. In my table, i want it to read what the id is for each product instead of it reading "basic" or custom. Someone said that I need to join the two tables and do an update query, but I don't understand how to.
View 3 Replies View RelatedThat is the question:
Whether 'tis nobler in the mind to suffer
with VBA Programming and the outrageous errors,
Or to take up arms against a sea of Access troubles.
Sorry I was in the mood for Shakespeare.
A quick summary first:
In the attached file I have my Relationships. One main table, Workorders with various one-to-many relationships back to their respective tables. If you look at the attachment and see a field with 'wrk' that's my foreign key.
What I'm trying to do is this:
Where-ever there is a 'wrk' field I want to add in all the fields (minus the ID Primary Key) into the main Workorders Form.
Then on the form itself I should just be able to enter in the data that is required for the fields.
So here's what I'm thinking for the coding is to Join the various tables to thlet me know if I'm on track or not. I'll start with a small one, because if I can get that right, the rest should be simple.
SELECT Model.ComputerID, Make.ComputerID
FROM Computer
INNER JOIN wrkComputerID ON Model.ComputerID = Make.ComputerID;
Hi,
I've got three tables:
tblEvent
--------
Id (PK) | Event_Name
tblDelegate
--------
Id (PK) | Delegate_Name
tblBooking
--------
Id (PK) | Event_Id (FK) | Delegate_Id (FK)
I need to retrieve a recordset with the following information:
Booking Id | Event_Name | Delegate_Name
Can anyone see how to do a SELECT statement to do this?
ANy help would be great, thanks!
Hi,
I have the following four tables:
tblGroup:
Group_Id (PK) | Group_Name
tblSubGroup:
SubGroup_Id (PK) | Group_Id (FK) | SubGroup_Name
tblProductType:
ProductType_Id (PK) | SubGroup_Id (FK) | ProductType_Name
tblProduct:
Product_Id (PK) | ProductType_Id (FK) | Product_Name
I need to select a single Product_Name (first one which appear alphabetically) from tblProducts given a Group_Id.
Is this possible? Presumably I need to join the tables in between?
If anyone can help with this it would be much appreciated, thanks...
there must be a way to do this, but i haven't been able to figure it out yet...
table one has three fields i need to be concerned with, member_ID, range_start, and range_end. no part of any range spanning range_start through range_end will be duplicated.
table two has several fields i need, but the field of primary concern is a person_ID that will fall somewhere within a range designated in the first table.
how in the world can i join these tables, since none of the IDs in the second table are actually listed in the first, but fall in between values?
i need to get member_ID from table one, person_ID and several other fields from table two.
any ideas?
thanks for any help,
john
hi
i have 2 tables, A and B
A table
StockDate Price
02/04/2001 1.12
04/05/2001 1.15
14/08/2002 1.14
18/09/2003 1.26
and so on 1.48
up to 2005
B Table
StockDate Price
02/04/2003 1.12
04/05/2003 1.15
14/08/2003 1.14
18/09/2004 1.26
and so on 1.48
up to 2005
i like to know how to write codes to join 2 tables into 1 table like
C Table
StockDate Price
02/04/2001 1.12
04/05/2001 1.15
14/08/2002 1.14
18/09/2003 1.26
02/04/2003 1.12 --> B Table
04/05/2003 1.15
14/08/2003 1.14
18/09/2004 1.26
and so on 1.48
regards
ellen
how do you join two tables?
help plz. thank you!
What is the purpose or the advantage to doing more than one join between two tables? How many joins can you have between two tables?
View 2 Replies View RelatedFirst time user!!!
I work for the school dist. and we have 2 tables for 2 different groups.
I need to pull data from them both and create a new Query.
Example
Table 1:
Name, address, phone
Table 2:
Name, address, phone
(No similarities in ether database, and we'd like to NOT export data)
I'm hoping it's an easy fix that I've overlooked.
Michelle
Hi there
I have made a form I am trying to bound 3 tables to.
The 3 tables are
tblPersonalInfo
tblBookingInfo
tblSkills
How I am trying to set it up is that I enter someones personal details in once. But using the same personal details someone can have more then one booking.
With the skills....each person can have lots of different skills.
Basically I work for a recruitment agency and I have been asked to build a database that can add new people.add skills to that person.....then add different jobs to the same person.
I have made 3 tables
tblPersonalInfo
PersonalID
chrForename
chrSurname
dtmDOB
chrEmail
tblBookingInfo
BookingID
chrJobTitle
curPayRate
PersonalID
SkillsID
tblSkills
SkillsID
Skills
I have joined all tables togeather (I think wrong but trying) the tblbookinginfo is joined to tblbooking using a 1 - Many relationship using the primary key of tblPersonalInfo to the field PersonalID in the table tblBookingInfo
I have joined tblSkills to the table tblbookingID through SkillsID
I created a query using all 3 tables and have used this to bound the form....
The form contains 3 pages and the 1st page works ok (it only contains info from the tblPersonalInfo) the 2 other pages don't seem to work as I want them to.
I would like to be able to click to add a new person and also new booking and new skills
Currently I am able to add new personal details but nothing else.
Can anyone see what I am doing wrong?
Thanks for taking the time to read this
Have some problems with a Purchase Order (PO) System I'm trying to do.
Figure 1 is the PO entry form. The upper part of the form shows the Header, while the bottom part shows the Line, containing all the ordered item details.
I separated the information keyed in into 2 tables, namely Header table (Figure 2) and Line table (Figure 3). The header table shows only the header details. With the order ID, the header is linked to the Line table where all the ordered items detail can be found.
I want to join the 2 tables and put the data into a new table, which will later be exported to text/spreadsheet for other purposes. I know it can be done using the make-table query. But my concern is about the layout/format of the table.
I would want to put my data where there's one header row followed by the corresponding line rows. Then another new header row with its line rows. I'll give an example:
H XXX XXXXXXXX XX X
L XXXXX XXX XX
L XXXXX XXX XX
L XXXXX XXX XX
H XXX XXXXXXXX XX X
L XXXXX XXX XX
L XXXXX XXX XX
L XXXXX XXX XX
L XXXXX XXX XX
Note that not all the fields for Header row and Line rows are the same. So can I put different data into the same column?
Would appreciate it if anyone could solve my problems. Thanks. ;)
Hi,
I have an unbound form that is based on a query. On the Load-event I have populated textboxes with the underlying records (it works fine).
The underlying query is based on 2 tables that are joined on (PrimaryKey) Table1.ID = Table2.ID (ForeignKey).
The Form has one Add-CmdButton. When the user will click the Add-button the content of the textbox will get added or saved in the underlying database.
I want to know the SQL to add, so that I can write the code behind on the Click-event of the button.
I know on how to add records with a single table (writing code behind), however, I want to know on how to do when it comes with joined tables (INSERT SQL statement ?).
Thanks and regards,
Prodigy.
I would like to join two tables with one query. Here is the twist though.
The first table would have a set of value like so:
0
50
100
300
500
750
1200
1500
the second table would have values as such
0
1000
1300
1500
1750
by just setting up the relationship to show all values from the sirt table and only those from the second table that match I get a list of values that will not work for me. What I would like to reflect
in the query is all the values from the first table and if there is for instance no value of 500 in the second table I would like to return 1000. For instance there is a zero in each table so that would be output twice. There is however no value of 50 in the second table so the query would read 50 and null or blank when I would like it to read 50 and 1000 (The next value higher).
We have a huge Database (relative huge ). Because of that I had to split the database on multiple tables to not lose performance. The main seperation is for every year and Terminal so I have now 6 tables with the same data and structure. Its making maintaining, changes and coding realy hard
Tables:
tbl_DL_Salzburg_2012
tbl_DL_Salzburg_2013
tbl_DL_Munchen_2012
tbl_DL_Munchen_2013
tbl_DL_Ulm_2012
tbl_DL_Ulm_2013
At the end of every year we have more than 10 000 records in each table.
The second Problem for joining the tables is that i have to filter them on Terminal (Salzburg, Munchen, Ulm) and on Year (2012, 2013, 2014). The Form I use on that table is a split form (reason is the fast filtering and searching in the datashed).
The first problem with the large amount of records i can solwe with an sql server but there is still the problem with the filtering
I tryed to use the form RecordSource and an SQL statement but in that case (depends how the SQL is struktured and the RecordSource is used) the users cant add or delete records in the table. It shows them that the record is added or deletet but when they leav the form they are all gone
P.S. It's an multiuser database. Making things more complicated
How to solve this second problem with filtering and not disturbing adding and deleting records.
I know that splitforms are not good but the users don't want to use something other that also takes me away the solution with unbound forms
I would like to move to one table until the end of the year. If not I have to handle 9 same tables.
I am using Access 2010.I have a table that I am using to pull my data from other tables and a query.My table is called tblMyData.One of the field names is level1. This field points to another table, and gives the user the choices for states (examples California, Texas, Maine).
Another field name is level2. This field points to another table and give the user the choice for type of customer (examples Business, Consumer)
The field name level3 points to a query. The query, qryFinalChoice matches up the choices based on level1 and level2.for example, the user can pick California for level1, business in level2 and California Widgets or Los Angeles Clothing store in level3 (plus about 20 other choices).
If the user picks Texas for level1, business in level2, business in level2 and Houston rugs, or Texas style restaurant in level3 (or about 15 other choices).
-I am recording 1 for California on level1, 2 for Texas and 3 for Maine in level1.
-I am recording 1 for business and 2 for consumer on level2.
The query qryFinalChoice has all the combinations for state, business or consumer, and lastly business name or consumer name.
-qryFinalChoice has line1 to match up the choices for level1 in my table.
-qryFinalChoice has line2 to match up the choices for level2 in my table.
-qryFinalChoice has line3 to match up the choices for level3 in my table.
I do not want any of the Texas business names appearing when the user picks California, or vice versa.
My SQL in my tblMyData tab for level3 looks like this:
Select line1 from qryFinalChoice where line1=1;
I am able to get all the line items where California is a selection.How do I change my SQL to pull all the line1 choices where I have selected from level1, and all the line2 choices where I have selected from level2 automatically based on my pulldowns?