hi there,
I am trying to find a way to link three fields in a form together. For example, if the User chooses CostCenter, then automatically the fields OrgUnit and Region receive a value (CostCenter=000 11, Region=US, OrgUnit=Archite ctur)
I have tried to solve the problem with: [CostCenter] in the fields Region and OrgUnit. Because I have added columns to the CostCenter Table. However, what I am getting is just the CostCenterID number, so doesn't work.
Is the way I am approaching the only possible one? or is there a SQL-WHERE possibility, or even a macro. Does anybody have an idea?
Best Regards,
Alive
18 2154
Why not make a new table with the cost centres in it and have the other two fields associated in that table.
Then when you need all 3 fields simply have a query with both tables in it and it will automatically connect the associated fields with the selection.
[New Table]
CostCenter - Region - OrgUnit
00011 - US - Architecture
00012 - IR - Architecture
Once they pick 00011 if you run a query with both tables you will get all the information in one place.
thanks for the answer.
I have already a table CostCenter with columns CostCenterID, CostCenter, OrgUnit, Region.
I have tried - SELECT CostCenter.CostCenter, CostCenter.OrgUnit
-
FROM CostCenter
-
WHERE (((CostCenter.OrgUnit)=[Where CostCenter]));
but it never worked:
Best regards and thanks,
Alive
- SELECT TableOne.[Key], TableOne.[OtherFields], [TableTwo].KeyName, [TableTwo].[FieldName], [TableTwo].[FieldName] FROM [TableTwo] RIGHT JOIN TableOne ON [TableTwo].ID = KeyOne.KeyTwo;
thanks for that clear explanation. But what do you mean with tabletwo?
I have everything in one table.
Tablename: CostCenter
PrimaryKey: CostCenterID
Column2: OrgUnit
Column3:Region
thanks for the update.
You should have two tables for this to work, one with the 3 fields above and another table that "looks up" the key to pull all the information into.
Does that make sense?
So you have one table that you enter the Key into and another table that links that key to the information you want. Your query pulls both tables together to give you the information you want. [img=http://img247.imagesha ck.us/img247/6166/queryhw4.th.png]
Unfortunately, I can't view the image (it's a blocked site)
ok, so you basically mean that TableTwo is the Form where I Typ the CostCenter. TableOne is where the CostCenter has it's values, such as OrgUnit and Region.
Now I have tried to write the SELECT as such: - SELECT CostCenter.[CostCenterID], CostCenter.[OrgUnit, Region], [Work].WorkID, [Work].[OrgUnit], [Work].[Region] FROM [Work] RIGHT JOIN CostCenter ON [CostCenter].CostCenterID =CostCenter.Work;
However, Access gives me the error message: Join expression not supported
I am sorry that I am asking so many questions.
Thanks
That is no problem, if you don't ask you don't get answers (no matter how mangled...)
The basic idea is you have one table that stores your 3 fields. Another table then uses a lookup wizard on one field to link to your table that stores the 3 fields.
Now you can make a query with both tables. If you set it up so that all the values from the second table are taken and only the values in the first table where there are corresponding values in the second table (ie a right join) then you should be able to bring all the fields you want together on a form or report by looking at this one query in the place of two tables...
Does that help?
thanks for your help and patience.
I see, but as soon as I am trying something like that and create the query and put it into the ControlSource, I receive an error message from access ?Name. Although, I haven't misspelled anything.
Can I send you some screenshot?
PM them through and I will have a look
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: paul b |
last post by:
Hello,
I have a small problem in PHP an perhaps someone can help me:
I have a simple HTML login page with a username and a password field,
as well as a submit button, which I have to use for login(I cannot
simply replace it by a php-page :-().
is it possible to call this page via a hyperlink from a php-script and
to directly fill the username and password fields as well to raise the
event of clicking the submit button.
|
by: netsurfer |
last post by:
Hi:
Have a question on making the date automatically filled in by what the
user enters in by the date at the top.
The date entered at the top would most likely be on a Wednesday then I
need to have all the prior dates pop in. Example: User enters 2/9/05
in the date field at the top being a Wednesday...I need the dates at
the bottom to be filled in automatically, respectively as 2/3/05,
2/4/05, 2/5/05, 2/6/05, 2/7/05, 2/8/05, 2/9/05...
|
by: Charles Robinson III |
last post by:
When selecting a option from a drop down menu in the form view, which is
linked to a table with three columns, how do I get Access to automatically
fill in two other fields automatically once the first field is selected, and
by using the remaining two columns?
|
by: Aravind |
last post by:
Hi folks.
I have the following tables (PK = primary key, FK = foreign key):
Member (MemNo , MemName, MemType, Course/Faculty)
History (..., MemNo , ...)
Member and History are linked in a 1 (Member) to Many (History)
relationship.
I have a form (frmHistory) based on a query (qryHistory) which is
based on these tables.
|
by: jballard |
last post by:
Hello,
I have a database set-up with a form and two subforms in it. I have one
of the subforms (replacement parts) set-up where you can pick part
numbers from a drop down box and also pick a description of the part
from a drop down box. These are two separate fields. I want to be able
to pick the part number and have it put in the description
automatically or the other way around. Which ever would by easier?
| |
by: DP |
last post by:
hi,
i have created a video rental database system. i have created a customer
form, and a film form.
i related all the tables, with;
tblCustomer has membershipID, which is primary and autonumber,
tblFilm has FilmID, which is primary and autonumber,
tblFilmRental , which has RentalID, CsutomerID, and FilmID
|
by: moorebj |
last post by:
I am having trouble writing a SELECT query. I have a "Customer" table
with "Customer Name", "Street Address" and "Suburb, State, Post Code"
fields. On a form I would like to be able to enter in the "Customer
Name" and automatically fill in the "Street Address" and "Suburb,
State, Post Code" fields from this table. All of the fields on the
form are COMBO boxes. Any help would be greatly appreciated.
|
by: JA |
last post by:
Not exactly sure how to explain this. I have 4 fields (in access 2000).
Category, Catid, Subcat, subcatid.
There are 40 categories, and about 1500 subcats. I've added the categories
to the lookup, so when I enter them in the field, I can usually just type
the first letter or two. (they are in a dropdown box).
I would like to have the CatID show up automatically in it's field, after I
|
by: cclayton000 |
last post by:
Does anyone have a basic example of a script that can automatically fill fields in a subform? Here is the scenario:
I have a main form for a Sample Lot and I have added some side-fields (not bound to any table) that asks basic information: Number of Samples (x), Quantity, Unit of Measure, Location, Tray...I would then like a button that triggers a script to automatically create the Sample Details (subform) that is autonumbered and then...
|
by: pctec |
last post by:
Hello,
I have a combo box that is looking into a field from a table named Machines, this machine table has two columns; MachineName and MachineRate but the Combo Box it only showing the MachineName field.
What I want is to fill a different table with the MachineRate as soon as I select the MachineName. I’m already storing the MachineName with the Combo Box to table1 but need to extract automatically the MachineRate into a table1 as I select...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |