Subtracting Info Using Data From Another Table
Nov 18, 2007
hi, i've been trying to find a solution to this problem but i can't seem to get my head around it. i have searched through the forums but found all the threads to be confusing although it probably is simple.
im a doing a project for a2 for a trailer tent company
got 2 tables where the problem lies in
order
stock
they only sell one trailer tent to a customer
when a trailer tent is sold, i want to update no in stock in stock table
i.e. trailer tent sold --> "no in stock"field for that particular trailer tent goes down by one. I would like the user to be able to change stock level any time as well so don't want it to be fully automated
sorry if i'm not making it clear, not really sure how to explain it
thanks for help, much appreciated
View Replies
ADVERTISEMENT
May 17, 2006
Hello,
New to access :eek:
Trying to create a simple data entry form to get information and populate fields from another table. i.e. type in info in one field and the other fields are displayed in the form with information from another table.
Any help apprecated :)
View 3 Replies
View Related
Aug 20, 2014
I am trying to automate the function that imports Infopath (.xml) data into access tables. I can have a button run the MenuCommand, but then the users have to go through all of the prompts and I just don't trust them enough to do it properly.
All I want is the user to click a button, then it lets them browse for the desired file and then imports it.
View 1 Replies
View Related
Mar 26, 2014
I would like to create a report that would really impress my supervisors, i just started at the company. I'm trying to create a call action plan, so i'm recording clients information on one table, and meetings we have had with each respective client on another table. Some clients will have multiple meetings, some few, and some none. I have a relationship set between them from the client's id number on the client table to the ClientID on the meetings table. one to many.
When I go to create the report, only the clients with meetings show up on the report, I would like client info to always show up on the report and meeting info to only show up under each respective client when it exists. I have worked out how to shrink and hide any text box without any info on it. It just seems like the existence of a meeting dictates where the client will show up at all in the report.
View 1 Replies
View Related
Oct 30, 2006
Is it possible to perform calculations across 2 adjacent rows in SQL?
At the moment, I have a running count that tracks the number of updates that go into our database against a specific JobID.
Is it possible to subtract the running count for a previous update from the current update.
For example, I would like my resulting table to end up looking like this
JobID Time Updates Since Last Update
1 10:10:00 1 1
1 10:30:45 3 2
1 11:15:10 9 6
In this example, the Since Last Updates should be found by
subtracting the Updates at 10:10:00 from the Updates at 10:30:45 giving 2 and the Updates at 10:30:45 from 11:15:10 giving 6. It would continue like this for as many rows of data have the same JobID (which could be several hundred!)
What I would then like to do is use the 'Since Last Update' number to display only those updates that are applicable to a certain Time, perhaps using Select Top in SQL.
Am I approaching this from the right direction, or would it perhaps be better to try to do this using Time, as this will always be unique?
Thanks for looking
W1dge
View 1 Replies
View Related
Aug 5, 2014
I have a table products with a field "id_product" and "total" (Total items in stock)
I have a query with the fields "id_product" and also the field "total in stock"
I want an update query to update the field 'total' in table 'products' with infos from that query
For each id-product in table products, replace the field total with the field 'total in stock' from the query
So I want to update a filed in a specific table with infos form another table.
View 2 Replies
View Related
Feb 20, 2008
Hi All
I have a form built on query called Current Med. its have medications info for the people and we have situations where the med get discontined for some reasons. i would like to have the discontinued med info to put be into another form for discontinued meds automically but i do know how do that. Hope you get the picture.
any help will be highly appreciated.
View 5 Replies
View Related
Aug 29, 2006
I have a form that I am working on.
When you open the form, it asks for a tool # (which will be a drop-down box). There will be a command button that will bring up a sub-form. In the subform, there are 2 combo boxes. One picks the group that the tool falls under, and the other has the section of the group for the tool.
When the user picks the section, I need it to filter the questions that pertain to that section (which is listed in a list table), and pull those questions, and populate the master table with them. (i.e., Pulls "where is tool?" from tblQuestions, and puts it in the tblQuestionMaster when section is selected from combobox).
:confused:
View 1 Replies
View Related
Oct 26, 2007
Hi I could do with some advice on how to start the following and if best to use both excel and access to achieve it.
I want to enter a range of data when I receive a bill such as date, supplier, bill amount, description, quoted price, customer job and retail price. Once I enter this I want to be able to do the following,
1.Pick a customer job and date range and get a report for all the details of bills received in relation to that customer in that time so that I can send the customer a list of items they bought in that time period and what each item cost.
2.I want to be able to compare cost prices from different suppliers and print a list of all items which shows who the preferred supplier is for each item based on the cheapest.
3.The quoted price from suppliers should automatically appear when the description and supplier has been entered.
4.There needs to be some sort of warning if the quoted price doesn’t match the bill price
5.I need to be able to get a report which gives the details for any bill which was entered that was not bought from the preferred supplier.
Some advice on pointing me in the right direction to get started is what I need and would be greatly appreciated
Thanks
Adw
View 6 Replies
View Related
Jun 28, 2013
I need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.
I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.
How I can go about getting this X and Y data into the InstReclosers table?
...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.
View 3 Replies
View Related
Nov 7, 2006
I need help on this, from what the best concept is, to what I need to look into using:
I store Quote data from phone calls into tblQuotes. There is a seperate table that holds much of the same information except that it is for actual orders called tblOrders.
As far as function goes, I have each working much the way I need it to except for one thing... If a sales person is on the phone with a customer with a quote already in the system, right now they re-enter the data into the Order table. Most times, the order is what was quoted, but maybe with a few small changes (so I will wnt to keep a historical record of the quote). How could I copy the contents of the quote recordset into the recordset for an order, where all the sales rep does from there is edit the quote to the actual order?
Would I use VBA or an update query that is executed via VAB? Honestly, I am unsure how to do this at all, I hope someone has seen this before and has a good suggestion...
Thank you.
View 2 Replies
View Related
Oct 4, 2005
Haya all, I’m new to access and I’m trying to create a database for my boss, but am a bit stuck, it’s a Bundy clock system in access, I have the tables set up (I think), and what I’m stuck on is building a form where, when I put in the employs ID and press enter there name appears underneath, can anyone point me in the right direction of a tute that would show me how to do this?
Thanks so much
The way I have the table set up is 3 fields, staff ID, first name, last name. All within one table
I think I’m in over my head… but you have to learn somehow :confused:
View 2 Replies
View Related
Oct 20, 2006
I'm a relative newbie to the more advanced features available to Access.
If someone could help me or point me in the right direction, I'd really appreciate it.
I have one table that contains companies and associated contact info. Each company can have more than one unique number ID (3 digit alphanumeric).
I want to take this table and run a query to create a new table that will only have one instance of each company name, along with the other contact information. The unique ID's aren't needed in this table (but it would be great if I could somehow toss them in, too).
This new table would then be used to create a form with a combo box containing all of the names to quickly jump to their contact info.
Any and all help would be appreciated. If a query isn't the best avenue for this, then please point me in that direction, too.
Thanks!
View 5 Replies
View Related
May 13, 2005
Guys i have tried to seach the Forum but can't find a solution to my need.
I would like to get a collection of table names from another DB, what i am trying to do is populate a table with a list of table names that the front end links to and their paths once a user has selected the data file location.
Instead of me having to force a user to place the DB files in a location of my choice when the database first loads it will prompt the user to select the location of the data file then (this is why i need the above) populate a table with a list of table names and their source, then establish a link to them for the front end to work.
Also if i ever send amended DB files or they move the location of the data files they can automatically re-establish the linked tables.
Hope this makes sense.
Cheers guys.
View 3 Replies
View Related
Oct 18, 2004
I am very new to access and have a question which I think is very easy to answer. What I have is an access file with a listing of companies, their contacts and what services we provide for them. (each company/client/service is listed in a row with each new set of information on the row below it).
What I am looking to do it to create a data access page that anyone in our office can view. I want the access page to have a text box where they can type in the name of the company, then click a 'search' button. That will then display the results in a box below everything. The results would be the contact and the services (there are other columns in the datasource as well, but I didn't list them).
If anyone can shed some light onto my problem it would be most appreciated!
Thanks
View 3 Replies
View Related
May 11, 2005
Please note that I am self taught (90% of what I have learnt has been off these boards!). I did make this form with info I found on this forum.
I am having problems with a log in box for a database, the line of code is -
If Me.txtpassword.Value = DLookup("password", "customers", "[customerID] =" & Me.EbayName.Value) Then
This code is in a Form; "password" is the value in the table of "customers"; [customerID] is the primary key value I wish to remember; Me.EbayName.Value I assume is the value in the table that access is looking for.
Questions -
A) It is not looking up the value for [customerID] - what have I done wrong?
B) In the line of Me.EbayName.Value what does the 'Me.' part tell Access to do? I assume it's a pointer to it's own form? What part of the line do I need to change to make it point to another form (or table)?
C) When this is finished how do I get the program to remember the 'customerID' while the customer is fillling out other forms?
James
View 2 Replies
View Related
Aug 8, 2005
I have an inventory table and a repair table for computer equipment. The inventory table is already set and working with all the data I need.
In the repair table, I would like to just enter the ID number and have some of the information already stored in the inventory table automatically entered into the repair table as needed.
For example, computer 138 has repairs done to it. I would like to then go into the repair table to add a new entry, type in 138 and have the some of the data from inventory table (model, serial number, etc) transfer into the repair table.
Is this possible with Access?
I have only been able to do something with the lookup wizard, but that doesnt seem to work well.
Can anyone point me in the right direction?
View 1 Replies
View Related
Sep 19, 2004
I have managed to get one combo box on a form (Categories) to look up the approprate values for that category and populate another combo box, i.e. if I choose Premesis costs in the categories combo box, I am given the relevant choices in the Details combo box, eg. rent, cleaning...
Now this is all great but I want the values I select in BOTH boxes to be entered into the underlying table. I have tried putting the relevant field as the control source, but the way it is set up means that only numbers are put into the table. Here is the code and stuff (I got this from a help site, so I have changed my table and control name to theirs to make life a bit easier):
cboStore (i.e. the Categories)
Row Source: SELECT tblStore.lngStoreID, tblStore.strStoreName FROM tblStore;
Event - AfterUpdate:
Private Sub cboStore_AfterUpdate()
Dim sManagerSource As String
sManagerSource = "SELECT [tblManager].[lngManagerID], [tblManager].[lngStoreID], [tblManager].[strManagerName] " & _
"FROM tblManager " & _
"WHERE [lngStoreID] = " & Me.cboStore.Value
Me.cboManager.RowSource = sManagerSource
Me.cboManager.Requery
End Sub
Private Sub Label5_Click()
DoCmd.OpenQuery "qryCategories", , acReadOnly
End Sub
cboManager (i.e. Details)
Row Source: SELECT tblManager.lngManagerID, tblManager.lngStoreID, tblManager.strManagerName FROM tblManager;
The Tables:
tblStore: Field names: ingStoreID (Autonumber); strStoreName (text)
tblManager: Field Names: ingManagerID (Autonumber); ingStoreID (Number); strManagerName (text)
The Query (very simple):
qryCategories: strManagerName From tblManager; strStoreName from tblStore.
Phew! Is that enough info for someone to help me? I wouldn't mind even the numbers being in the table if there was some way that I could change them back to text for a report.
I'd be really greatful if someone out there could help - be gentle with me though, as I'm note very good at this code thing!
View 2 Replies
View Related
Dec 14, 2004
I've got a huge table with one column that shows a lot of duplicate
info in it. How can I let it identify the unique names and "hide" all the
duplicate info that falls under it e.g.
Columns:
Col.1:Fruit Col.2 Col.3
Apples A 1
Pears B 2
Peach C 3
Apples D 4
Apples
Apples
Pears
Pears
Pears
I would like the table only to show the say Fruit column with Apple, Pears,Peach
and list when you click on it all the related data to it. Along with this I would like to
associate all this info in the form. If I select Fruit in the form the related data should appear
below in maybe a subtable or something which I should be able to select from which info I need.
View 4 Replies
View Related
Mar 27, 2008
I know there should be a simple solution to this problem but i have not worked on access for some time and the cobwebs is too thick.
I have 2 tables one being Stock codes with related fields: Stock code id; Stock code;length;width;height.
Second table is Order Details with related fields: ID; Order id; Stock code;quantity;length;width;;height.
Did a query running the stock codes with length width and height.
Did a form with all details, calling up the stock codewith the query. The info on length width and height to the related stock code is on the form but how do i relate it back to the table Order details.
In the order details table the stock code is showing but none of the other related info.
As i said it is something small but i cant get round it.
View 3 Replies
View Related
Jan 15, 2006
I have a table that lists all of my available seating for different events.
My table columns look like this:
EventID - Section - Row - Seat - Available
Sample data in columns:
1 - A - A - 1 - Y
1 - A - A - 2 - Y
1 - B - A - 1 - N
1 - B - A - 2 - Y
2 - A - A - 1 - Y
2 - A - A - 2 - Y
2 - B - A - 1 - Y
2 - B - A - 2 - Y
EventID actually goes from 1-6
Section actually goes from A-F
Row actually goes from A-J
Seat actually goes from 1-36
When the user selects an event (or events) in the first box - I would like the query to eliminate the available seating, from the other events, in the results.
When the user selects a section (or sections) - I would like the query to further eliminate the available seating, from sections not chosen, in the results.
When the user selects a row (or rows) - I would like the query to further eliminate the available seating, from rows not chosen, in the results. And finally display whatever available seating is available - based on all three of the above criteria.
I purchased every access book I could find.. and I am slowly learning this. I'm a complete novice..
I'm not looking for any of you to do the work for me.. I'm more looking for guidence. I'm still getting to know all of the controls and their limitations. I have not yet written any VBA code. (It's still very confusing) I'm trying to get by on using the toolbox controls, using queries, macros and form controls.
View 2 Replies
View Related
Dec 25, 2005
I have a report in my DB that displays the results of several queries. How can I have the information displayed on my report entered into a table as a record when the report is generated?
Hope you can help!!
View 3 Replies
View Related
Apr 27, 2007
This may be a bit detailed, so stick with me. Is there a way to have a form which you can pull tabel information into, but look at a specific line? I am trying to create a database that will pull up the first line of a specific tabel's information, then when a command button is pressed, it will pull up a seperate form with the information from line 2 of that SAME tabel. I don't want this to be done by clicking the arrows at the bottom of the form. I actually want this to populate seperately.
Lets say I have a Loc_ID box that is pulling the Loc_ID from a tabel.
The tabel I am trying to pull from has an autonumber format and is sorted in that manor from a-z. Is there a certain type of logic that I should use however? As of now, each time I have a form come up, it starts at the first record, which is not what I want it to do. I want it to come up with the first record, then click a few buttons, then the next time a new form comes up with the same info on it, I want it to show the second record's information.
Any information anyone might have will be MOST helpfull! :D
View 3 Replies
View Related
May 17, 2007
Is it possible to construct a query that uses information from one table to run a series of Like OR Like... criteria?
I have created a table that contains partial part numbers and would like to query a second table for all the part numbers that contain these partial part numbers.
Meaning, BACB30DX6 would return the desired information for BACB30DX6-7, BACB30DX6-8 and so on.
The TBL_PARTS_FAM_DIA table contains approx. 200 items so 200 LIKE statements can't be the way to go... I'm hoping.
I tried: LIKE "*[TBL_PARTS_FAM_DIA]![PARTS_FAM_DIA] *" thinking that perhaps this would go through all 200 items... but of course that was wishful thinking.
Do I really need 200 LIKE statements or is there some easier way to do this.
Thanks in advance!
View 2 Replies
View Related
Dec 25, 2005
I have a report in my DB that displays the results of several queries. How can I have the information displayed on my report entered into a table as a record when the report is generated?
Hope you can help!!
View 1 Replies
View Related
May 9, 2006
Hi I have a field on my form called cost of order
I also have a field called deposit on the form that is generated by dividing the cost by 2.
is there a simple way (most probably) to the send this data to my payment table, I already have a deposit filed set up in the payment table I just want to post the data in from the form?
View 5 Replies
View Related
