have a table i am creating for a subform to calculate a price for a line total.
[item] [amount] [weight] [yeild] [linetotal] *[need coverted price from a query]
the converted price is in a query and i would like to load it with its selected item in a table (or form?) so i can multiply for a line total in the subform.
is this easy?
Hi, I am currently stuck on making a working one to many table relationship, I will post what I have so far, what I am stuck on is forming a proper working relationship.
tblCustomers
CustomerID
Name
Address(street)
City
Region
PostCode
Telephone
Discount %
tblOrders
OrderID
ProductID
Description
Qty
UnitPrice
tblProduct
ProductID
Description
Room
Finish
UnitPrice
Stock Qty
The desired output is a working order form, that when, for example you enter the product ID into the appropriate field automatically brings the rest of the product fileds such as description and unit price. And when the customer ID is put into the appropriate field all of the relative customer info is shown.
I am new to access and I apologise if my post is unclear or incomplete, any input is appreciated.
Thanks in advance.
The database has three tables:
Equipment: 3 columns - ID, Nomenclature, Location
Procedure: 3 columns - ID, ProcedureNumber, ProcedureName
PMHistory: 4 columns - ID, Nomenclature, ProcedureNumber, CompletionDate
The PMHistory-Nomenclature is linked to Equipment-Nomenclature so that when an entry is made into PMHistory-Nomenclature there is a drop-down window showing all the equipment available. PMHistory-ProcedureNumber is similarly linked to Procedure-ProcedureNumber.
Assume four entries into the PMHistory table:
Equipment1, Procedure1, 3-September-2006
Equipment1, Procedure1, 19-September-2006
Equipment2, Procedure1, 4-September-2006
Equipment2, Procedure1, 20-September-2006
Equipment3, Procedure2, 5-September-2006
Equipment3, Procedure2, 21-September-2006
How to write a query so the result will only contain the latest date for each Equipment/Procedure pair.
The desired result is:
Equipment1, Procedure1, 19-September-2006
Equipment2, Procedure1, 20-September-2006
Equipment3, Procedure2, 21-September-2006
Regards,
Hardin
Hi,
I'm new to queries. I've managed to build a very simple query which joins two tables. How to I save these results so that I can export a tsv file from them?
(I'm using Access 2000).
Thanks very much for your help...
I have created database but I now have 3 questions. I havce changed a subform to view as a pivot chart but I am unable to split and group in a similiar waay as in excel, is this just something I have to accept because otherwise my "lets have a works database" theory is shot.
2nd I would like to create a switchboard how do I do this.
and lastly some of the data I would like to utilise for mail merge document ( name and address stuff) are these easy to set up??
thanks very much
Andy
I am completely new to Microsoft Access but otherwise consider myself to be a Tech Geek. I have made a very simple and small database. It contains the name of my customers and the orders they have placed with my company over the past three years.
Now i want to generate report which when given the name of my customer would give me all the orders placed by that specific customer. I know how it can be done query but i want to do it through some kind of access page so that my subordinate who is very technically challenged can manage that.
Though i know it is a very basic question but i tried everything and could not do it....can somebody please please please help me.!!!!
Hi. I have a problem. I want to make a search form that will collect info from several tables and show the results in a list box, like a list(for example with the same name but with different other attributes ). I know it is possible and it is not hard but i'm novice to access. Thanks in advance :)
View 1 Replies View RelatedI'm trying to set up a database from scratch and have never used Access before. It's being set up to hold information gathered from a survey of roughly 2000 people on their fave Manchester songs and artists.
It's going to include their name, email, city and 1,2,3,4,5 choices of fave songs. The aim is to have everything in a simple datebase where I can work out who has voted for what in each section, how many votes a song got a position 1 and so on. But I also want to be able to work out the total number of votes over the five catagories.
Is there a way of doing this? I can bring up information by using query but can I be specific as to what information the query brings up?
This is probably a stupid question but any help would be very useful. I'm using the Idiots Guide to Access and as I said this is the first time I've ever used the program only installed it yesterday!!
Ta
Nick
www.PrideOfManchester.com
Hello, this is my first post. I am very much a novice and have come across a problem that I'm sure is simple to solve but so far the solution has eluded me. All I want to do is subtract a field in one record from the same field in the next record in a query or report but I have no idea how to do it. Can anyone help please ?Thanks in advance.
View 1 Replies View RelatedHI all,
I am a beginner at access, please help me.
I need to create a customer id filed that has letter and number together, i.e. ZH72773. And the id must change incrementally, but no number 00000. Also it should change the letters after the number reaches 9999, i.e. ZH9999 --> ZI0001.
Would anyone tell me how to do this, please.....thanks in advance
Songrit
Hi everyone, heres a question I hope is easy to answer:
I have two tables, one containing about 1500 entires another with about 43,000.
I want to find out which entries in table1 are also in table2.
I really appriciate the help, thanks so much!!
I am quite new to access and am not sure if what i am trying to achieve is quite simple or needs some VBA Code written.
I have a large table (about 10,000 records) with a varitey of different fields, including "Member ID", "Banker Name", "Location" etc. I would like to develop a form where I can filter on a field such as "Banker Name" or "Location" (using a list box) and below (maybe in a subform??) for all of the records to appear that fall under that banker or location in a datasheet view.
Any help would be greatly appreciated.
Sam
I'm brand new at Access and would appreciate your help.
I have an existing database (table) and would like to make a duplicate table so that I can modify the new one without messing up the old one. How do I duplicate the table either w/ or without the data?
I've created a name tag. I'd like to create another name tag that's the same format except for a different group of people pulled from the database. How can I do this without having to re-create the name tag all over again?
Thanks for your ideas!
Hi,
I work in the quality control business and recently purchased a system which on first inspection seemed like the way forward, however I have since discovered that imputting the data is very slow.
I wondered if Access could be programmed to create a better database.
When a sample is tested, I would like the numberical values to change colour depending on whether it is inside or outside the specifications. (Is this possible?)
The main problem I can see is that samples of different substances will be analysed and each substance has different specifications. (Is it possible to set this up?)
I hope this question makes sense to you all
Any advice would be greatly appreciated
Thanks
Jimmy
In the interest of speed in my current project, I'm working with bound forms, where in the past I have developed mainly using unbound forms. Hence, the word novice in my subject line.
Is there a way to do the following, and is it typically done?
The layout: A typical, record by record data entry main form, with a linked datasheet subform. The underlying query contains person's names and other associated data. The subform is a datasheet with an alphabetical listing of the person's names.
My goals (2 of them):
1. To have the datasheet subform go to the record selected on the main form.
2. To have the main form also go to the record selected in the subform.
The forms' On Current events conflicting with one another, as the subform's Current event fires whenever the main form's does.
could anyone advise how to get a date written to a field when a check box is changed to "yes"? i'm useless at VB!
thanks.
Hi everyone,
Basicly I am doing an Access Project for my school coursework, but I am really confused and my teacher isn't that great.
My coursework is based on a video rental company.
The database (in breif) has to be able to bring up a customers details, and add a loan to/under that customers details. (as you would expect from a video shop).
Now, to be honest, I dont know if I have the correct items in each table, but was wondering if someone would be so kind to have a quick look to tell me what to change and where to put the relationships for the database to work correctly.
I have taken a screen shot of my current database:
http://img259.imageshack.us/img259/8216/accessproblem4de.th.jpg (http://img259.imageshack.us/my.php?image=accessproblem4de.jpg)
If you would like any other information, please let me know.
I am really grateful for this!
PS65
Hello all,
I wouldn't call myself a beginner at Access but I am nowhere near a skilled programmer. I have a database I have to finish in the next several days and have some challenges that I have searched and searched the forums for answers to and can't seem to find the answer. Maybe I am not searching the correct terminology but I just can't find the answer(s).
#1 - I have a subform in which we enter quotes. Our quotes are for repairs to train cars and like automotives, they are done part by part (line by line). Each job per car can start out with say 5 lines and be modified several times (estimate and supplement) and wind up with as many is 80+ lines per job. To handle this, I have created main form with the job data and a subform containing the ob detail with a checkbox on each line so that we can select only the lines that need to be printed per quote and/or modification as they have to be submitted to the customer independently. This works great HOWEVER, since there are sooooo many lines and the check boxes have to be checked and unchecked for various reasons what I need is a check box or control that will check all or clear all of the checked boxes. Can anyone help accomplish this? I can't figure it out.
#2 - Based on that same form and checkboxes we another form/subform that has 2 columns, 1 for estimate approval and 1 for supplement approvals. These are set-up to feed into our customers required billing format. I have a pop-up form that pops up the lines as they are approved by the customer based on those same check boxes and what is being approved. This is working great as well, HOWEVER, again, these can be as many as 60 or so lines long and the approval number has to be entered into the appropriate box over and over and over. Is there a way to add a text box that has estimate approval and another that has supplement approval and auto populates the appropriate box (estimate or supplement) based on the value typed in the box? I have this working and it populates the fields but isn't saving them in the table.
#3 - I can't figure out how to set form size. For example....I have a form that opens to full size and I want the subforms to open to a smaller size. I have tried setting this but they seem to all still open to a full size form.
Thanks so much for any help you can give!
Dianne
Hello, I am pretty new to Access. I am able to create simple forms that will interact with tables, reports, etc.. I am trying to go a little farther and customize a form that will automatically be displayed when the user opens the file. I have read that you must have 'modal' properties set to yes. I have done this and when I enter then .mdb file, I still see the default menu.
Do I have to write an expression within the properties? And if so, on which command?
Also, I would like to become more familiar with customizing forms. Is anyone familiar with a web based step-by-step tutorial out there that will walk a person through the process? I am not satisfied with the Help menu.
Thanks for the help.
I hope (boy, do I!) that someone can help me with Access 2007 amd SQL.
I'm a graduate student taking a course in databases. I'm NOT techie, but I figured my husband or kids would be able to help me - they can't!
I am absolutely stumped and am hoping someone will be patient enough to chat with me about SQL and Access.
(You CAN query in SQL with Access, right?)
Thanks,
Bev
I want to do a query on a subform which picks up an ID field on the main form without using this as the connecting master, child key but finds this ID in one of two fields in the full table. My problem is, how to pass this field value from the master form to the query. I would use OpenArgs if I was calling another form, but how would one do this in a subform?
I would be most grateful for any assistance as this problem has been puzzling me for some time
I have managed ti create my first database, and i have an issue date and a renew date, at present i manually have to calculate and enter the renew date, what i am trying to achieve is that if i enter an issue date of say 01/01/06 and say the renew date is 4 years from now can this field be set so that it looks at the issue date and then say asks me a question like how long before renew , say inter 48 months it should then display a date of 01/01/10
View 1 Replies View RelatedWhen I started building forms to fill my junction tables, the example that I looked at based them all off of queries. That's what I've done for the most part. However, this latest subform that I've made and embedded into a main form is based off of a table. Will that cause problems for me down the road? I'm not so far with it that I can't redo it off of a query, I just don't want to!
View 3 Replies View RelatedHi folks,
I have a form with a regular save record command button, however I also want to add in an action to open a second form based on information stored within the form.
So users may be directed to Form.alpha or Form.beta depending on their data.
Can any one help?
Greetings Access Geniuses,
I am currently in the midst of conversation over at utteraccess.com about a large database project I am working on.
I will post the most recent updates here, but if you want to download the database for your own review, you'll have to go over to the other forum. By the way, this is NOT an advertisement. I am a real person with a real, significant, and immediate need of as much assistance as possible for this project.
Before I go on, a few things about me. Though I am in the legal profession, my technological background is very strong, including a high level of proficiency with MS Office apps with the exception of Access. I know my way around the program (the result of its homogeneity with the rest of Office), but have not made a real database in the past. I also do not know any programming languages.
I have recently indulged in an Access crash course of sorts, including some book and online study on things like planning, design, and normalization, but I am still having difficulty wrapping my head around making it work.
That said, the link to the other discussion is: [W W W DOT]utteraccess.[ADD DOT COM]/forums/showflat.php?Cat=&Number=1590364&page=0&view=collapsed&sb=5&o=&fpart=all&vc=1
Feel free to gloss over it to see how things have progressed. Below is a paste of my most recent substantive post. Any help anyone can give me is of great value to me and I really, really appreciate it.
_____________________
Hi Everyone,
Attached is the most updated version of my database project for your review. I have also attached a sample of the output we would like to have for each product. This sample is not based on actual data, but it clearly shows what we are trying to achieve via a form of some sort. More on this in a minute.
The following changes have been made to the DB:
-Changed tblTrustProspectusVersion to include the appropriate data, based on our business model.
-Added descriptions to all non-PK fields.
-Created relationships to illustrate how things fit together. Note that these are NOT the actual relationships, but are for illustrative purposes to help everyone here (including myself) further understand how things fit together.
Our Business:
I am part of my company's legal department. Our team handles a number of different things primarily associated with Securities and Exchange Commission (SEC). filings. We have two major product lines. Everything that happens with one in this DB happens with the other as well.
Each product within a product line has certain features and other necessary information we need to see when doing our filings (see the attached sample output). These features and necessities include, but are not limited to, various statuses, various numbers associated with the SEC, various important dates, etc.
In addition, each product has a certain "fund lineup" associated with it. These funds are made up of two components: the name of the fund (aka "portfolio") and the name of the subadvisor to that fund.
Futher, each of these funds is associated with a certain Trust. The SEC requires us to send prospectuses to clients based on these Trusts, which, as I mentioned, are comprised of the said funds.
Basically, we need to be able to select a product from a drop-down list and have all of the aforementioned information populate instantly.
Before I close, one question with respect to my "tblProductFeatures". Like I mentioned, each product has a certain set of features associated with it. Each feature has a certain fee associated with it. These features come in four basic categories: Living Benefits + fees, Death Benefits + fees; Maintenance fees (just short list of the possible fees); and 12b-1 fees (another short list of fees).
Since the features can be so easily broken down, should I add them to their own tables?
Please consider this as you give your advice on how to acheive my desired goal.
I hope this makes sense.
And again, thank you all so very much for your help thus far.
Best,
Gilbert
I have a main screen that has a tabbed form in it. Each of those tabs has a sub-form in it that displays information and allows some information to be updated.
In one tab I have a query based sub-form that returns information related to the project. This information cannot be manipulated or changed as it comes out of a company managed database. I would like the analysts to be able to add more granular information to a new table by inputting information in a dropdown field for one of the new table fields, but utilizing two of the existing sub-forms fields as identifiers in the new table.