Limiting And Storing Data From Another Table?
Dec 4, 2013
I have two tables one that lists accounting codes by county and one that a user will have an entry by county which needs to store the applicable acct code from the other table. I want the user to be limited to the use the accounting codes assigned to their county only when they enter data on the second table.
Table examples:
County
Code
Funds Spent
Accounting Code
Date Entered
Aitkin
123
$20.00
Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.
12/4/2013
Aitkin
112
$23.00
Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.
12/4/2013
Becker
145
$12.00
Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.
12/4/2013
County
Accounting Code
Aitkin
4872727001000000
Aitkin
4842727001000000
Becker
4872727005000000
View Replies
ADVERTISEMENT
Jan 12, 2006
:confused: Hi
I have some 100 questions for which user must answer by selecting any of the options provided using option buttons.
As i cant fit all 100 qtns in one form am splitting it into soem 10 forms.
User answers the qtns in 1st form and clicks on next button to goto to nxt form
thn agn he answers the qtns in tht form and clicks on next button to goto nxt form..so on.. until he answers all the qtns in the last form.
I want to transfer the answers selected from 1st form till the last one..where all the answers are stored in one table whn a submit button is clicked.
please let me kno ASAP if thr is anyway i can do it.
thnk in advance
View 1 Replies
View Related
Jan 12, 2006
Hi
I have some 100 questions for which user must answer by selecting any of the options provided using option buttons.
As i cant fit all 100 qtns in one form am splitting it into soem 10 forms.
User answers the qtns in 1st form and clicks on next button to goto to nxt form
thn agn he answers the qtns in tht form and clicks on next button to goto nxt form..so on.. until he answers all the qtns in the last form.
I want to transfer the answers selected from 1st form till the last one..where all the answers are stored in one table whn a submit button is clicked.
please let me kno ASAP if thr is anyway i can do it.
thnk in advance
View 8 Replies
View Related
Jan 9, 2007
i have a form with two combo boxes cboMth and cboYr
i also have three text boxes in the same form with computed results: txtA,txtB,txtC.
now how can i store txtA,txtB,txtC with reference to the (Mth and Yr) into a table?
e.g: the table will looks this way (below)
===== ==== ==== ====
Mth/Yr txtA txtB txtC
===== ==== ==== ====
01/2007 580 683 93
:
:
:
06/2012 312 510 80
:
:
how shall i write the codes?
View 1 Replies
View Related
May 8, 2015
I have a simple data entry form based on a table. However I have a few fields that I do a lookup in a field on the form from a query, and yes I know I should not have a lookup in the control source however, this is the way that I will be doing it on this occasion.
=DLookUp("[Salary]","[Salary Query]")
How I get the value from this unbound field to enter into the actual field in the table. Do I bring the actual field into the form and hide, and do some sort of after update, as I have tried and it does not work.
I have called the unbound field with lookup "Salary Level Base" and the actual field in the table is "Salary Base".
View 5 Replies
View Related
Jul 8, 2005
Hi,
I'm baffled by this ...
One of the forms in my database has a subform. The subform displays data from a table which is related to the data in the main form (also powered by a table) through a key relationship. So long as the relationships are set up correctly, the correct data magically appears in the subform as you scroll through the records in the main form.
I've tried to do this a second time, except in this instance the subform is displaying data from a query, and the data is related to the data in the main form not through a direct key, but through a link table. I've set up the relationships properly as before, but this time the subform stubbornly displays all the data from the query, not just that which is related to the record in the main form.
What am I doing wrong?
View 2 Replies
View Related
May 23, 2015
How to limit input data on the same month for each patient? The field is a data/text box on a subform
I found this code on Form Current Event!:
Code:
Private Sub Form_Current() Dim intMaxNumRecs as Integer
intMaxNumRecs = 5 'Max Number of Records to Allow
If Me.NewRecord Then With Me.RecordsetClone
If .RecordCount > 0 Then .MoveLast: .MoveFirst
If .RecordCount >= intMaxNumRecs Then
MsgBox "Can't add more than " & intMaxNumRecs & " records in the demo database!" .MoveLast Me.Bookmark = .Bookmark
End If End If End With End If End Sub
But I want a monthly limit...
View 1 Replies
View Related
Apr 25, 2005
Hey! I'm doing Computing AS level and the coursework is to create a system for "Terry's Turkey". This is basically my first introduction to Access, and our teacher is little to no use!
After alot of work working out things in access and reading alot of books, other than creating the switchboard, theres only a few things left to do!
The first is to limit the amount of "turkeys" that can be ordered. I have 2 tables that this envolves... Order and Customers, they have a one to many relationship (each customer may place one or more orders, each order must be placed by one and only one customer). They are linked by Customer ID. I need to set two limits, I guess by a validation rule of some kind. The first is that each Customer can place no more than 5 orders. The second is that their can be no more than 40 orders in total. A slight complication to this is that a customer can "cancel" an order, in this case its marked as cancelled (theres a yes/no field called Active to determine this), and ideally, these wouldnt be included in the counts.
So far I have the following as a query, though I have no idea how to put this into a validation rule
SELECT Count(Orders.OrderID) AS Var1
FROM Orders;
I guessed that to expand this to not including inactive orders something like
SELECT Count(Orders.OrderID) AS Var1
FROM Orders
WHERE active="yes";
Any help at all would be most appreciated! I have spent many hours pondering over this is and looking at as many access sources that I can! I think this is my last resort :(
View 3 Replies
View Related
Jun 7, 2005
Hi everyone,
I've recently begun building a database to keep track of stock at work. Nothing particularly special or difficult. I have a little bit of prior experience with Access and VB, but not heaps.
Basically, I've created a tables for parts, companies etc, and am relating all the data together.
At the moment I've made a form that allows you to enter a new part, with Part Number, Description, Category, Supplier, etc.
The complication comes when I want to create an ASSEMBLY of parts. I'd like to create an assembly (which has a lot of similarities to a part, in that it has a part number, description etc), and that assembly needs to store a list of parts that it includes. A simple assembly might include a few items, ie, a bowl of meatballs includes the bowl, a fork, the spaghetti, and 5 meatballs. A more complex assembly (a complete dinner for 5) might include 5 bowls of meatballs (a sub assembly), pepper and salt, 5 glasses and two bottles of coke.
I'm not sure how to best store this assembly data...
I can't really have a table with a finite number of "part" spots because the assemblies get quite large. I'd rather not use an ugly VB macro that stores the PartID's with quantities either, as that could get thoroughly out of hand pretty quickly.
I'm sure there must be a simple method for doing this, but without having a clue as to what it might be called I can't really look for it in help!
Any clues you guys could give me would be great.
Thanks
Col.
View 1 Replies
View Related
Aug 9, 2007
Dear Access Expert.
I have a choice of either calculating a field or storing it.
Specifically, I have two pieces of data ... departure time and arrival time which I have taken from another source. This other source also has the difference between these times. Currently, I am not storing the difference in a field as I have opted to calculate it in my database.
However, I am thinking that maybe it would be better to store it vs. calculating it because I have 35, 000 records and storage space is not going to be an issue.
What do you suggest if I my main concern is speed and effeciency?
Thank you very much.
View 6 Replies
View Related
Jan 14, 2008
I know this is probably a basic question but I just need to clarify something.
I created a combo box using a wizard in a form that relies on its options by looking up data from a spefic table (Y) i.e. such as employee role. So far so good.
However when I create the field in the table in which the user response is stored in Table X, and I am specifying all the details in the general and lookup tab,
Do I:
A. store the data as text or
B. do I go to lookup tab --> display control --> combobox etc..?
View 2 Replies
View Related
Oct 29, 2007
Well boys and girls, its about that time in the week where I have once again come up with a brilliant application for my little Access project, but have no idea how to make it happen.
I will start with a little background info... In the course of the parts recoveries we have different reasons for recovering parts, different people (locations) that request them, and different priorities for recoveries. For example, when a new model comes out, we will recover 100% of the parts for investigation by the factory... however, we may want to take a look at failed water pumps first. In this case, our recovery, until it is complete, will have higher priority than the 100%, otherwise we would not receive any parts.
This is because of the way our warranty system works... It assignes a three digit number to each part number of a recovery (between 1-999). The lower the number, the higher the priority.
So if I am setting up a 100% recovery, I would want to put it in the, say, 200's. This would leave room for an overlapping recovery to be inserted in the 100's where it would divert a certain number of parts away from the normal recovery stream. Then, once a user-defined date or quantity of parts has been reached, the corresponding number goes dead, and any of those part numbers are reunited with the original recovery stream.
Sticking to the example above, typically 100% recoveries would go for say... 2-3 years, where as an in-house recovery might be for 20 parts... Another difference is that the longer term recoveries are managed by the end date, where shorter term recoveries are by number of parts recieved.
What I want to do is be able to auto assign these numbers based on criteria derived from user-inputted data... (i.e. destination, start/end date, recovery type, etc.). Then, when the recovery is complete, whether based on date or quantity recieved, to delete the information associated with the 3 digit number, block its use for 45 days, and then be able to reuse the number for a new recovery.
Whew.
Thanks to anyone who can be bothered to read past the first paragraph, let alone offer any advice.
View 14 Replies
View Related
Sep 21, 2004
I want to limit the numer of times a record can be entere into a table. After a record has been entered 3 times, I want an error message or validation rule to tell me it can't be done.
View 7 Replies
View Related
Mar 10, 2015
For my database the business has Clients who they conduct Needs Assessments for, but they only conduct 3 needs assessments. I have a Needs Assessment table with Client ID as the foreign key. how I can limit the number of times a Client ID shows up in the foreign key field to three?
View 2 Replies
View Related
Feb 18, 2008
Hi guys,I'm new to Access except using it years back but much has changed from the 2000 version to the 2007 version - it's slowly coming back to me though, as it's mostly just an interface change. Although I'm highly experienced in SQL (mySQL) and database design / structure from being a Web Programmer.I'm wanting to store a stock amount for products. Using 3 tables, for products, orders and sales. Here's a real basic layout:productsprodID | productName | productStockordersorderID | supplier | prodID | quantityBoughtsalessaleID | customer | prodID | quantitySoldThe products table is just a list of the products. Sales is where I log what I sell to customers and the orders are what I buy from my suppliers.What I'm wishing to do is use the products table to store the stock of that item so as soon as I make a sale, that quantity for that prodID is subtracted instantly from the productStock and when I make an order it's instantly added.Is this possible, or is there a good way to do this?All help appreciated! :)Thanks,Nick.
View 2 Replies
View Related
Feb 7, 2014
I'm quite new to Access and am just working on a little practice database where I keep track of sales of a product.
Basically I have a table keeping track of the different types of products, a table keeping track of customers, a table for orders and one for replenishments of stock. I have a query that calculates the stock of each type of product based on replenishments and sales.
Now I want to make sure that a customer can't order any more than there is in stock. I know you can restrict what's entered into a table with a validation rule but that's only for static restrictions. After searching the internet for an answer I've pretty much learned that I can't really make this restriction directly in the table, correct? But then how can I achieve this? Do I have to restrict the amount through a form somehow?
View 3 Replies
View Related
Mar 19, 2005
I need to know if I can store data in more than one field, without allowing Duplicate entries.
Example: We have to send out letters with a claim number on it, sometimes we need multiple claim numbers on a letter, up to about 10 claim #'s is the most I've seen thus far.
We mail merge into Word to create the letters.
However, these claim numbers cannot be duplicated within the database and in order for this to work with the mail merge, I need several fields to store the claim numbers.
Fields are:
ClaimNumber
ClaimNumber2
ClaimNumber3
etc....
To be more specific. I probably have a bad data structure as I was kind of rushed and a little inexperience with putting the database together. It's for work and sometimes management just doesn't want to wait, they are so negative.
In brief, most everything is in 1 table and I know from reading this forum that I shouldn't do that, but that's another story.
Further, a claim# is tied together by the same claimant, the relationship code is what separates the claimant with the family, the insured id# is used for all claimants within the family.
You can also tie the claim# to a provider id#, some providers such as clinics and large hospitals have multiple providers that use the same provider id#, so a suffix code is used to separate the providers within that same #.
Please feel free to ask additional questions and I will give you the additional information you need to help me. Thanks again.
P.S. - Last year I created the database and this year I am improving it, so expect more posts from me in the future. I really appreciate all the help I get from everyone here!!!!
View 9 Replies
View Related
Mar 19, 2005
I need to know if I can store data in more than one field, without allowing Duplicate entries.
Example: We have to send out letters with a claim number on it, sometimes we need multiple claim numbers on a letter, up to about 10 claim #'s is the most I've seen thus far.
We mail merge into Word to create the letters.
However, these claim numbers cannot be duplicated within the database and in order for this to work with the mail merge, I need several fields to store the claim numbers.
Fields are:
ClaimNumber
ClaimNumber2
ClaimNumber3
etc....
Any help would be appreciated.
Thanks.
View 3 Replies
View Related
Nov 21, 2004
How do I create a table relationship where a secondary reltated table stores past dates, status code and customer ID. For example: I want to keep track of the past dates that an order was revised and why it was revised. So, if I have a customer who was due for installation on 10/21/04 and status is PEND for pending. THen on 10/18/94 I change the date of PEND to 10/25/04. I want to keep track of the times it changed and the reason why. HOw can I make the program automatically create a record in the revision table storing the past code, reason and date using customer ID as key and teh revision table would allow multiple records for each customer ID. DId I make sense?
View 2 Replies
View Related
Feb 11, 2008
Hi,
Firstly this is my first post so please forgive me if i do not conform to the usual way of asking a question!
I have an access database set up and it is working well, so far!
What I wish to do is the following, I am creating this for a cycling club to store race results...
I wish to store the time that they completed the stage in, how do i do this using the date/time field option when all it wants to do is store a complete time.
for example i wish to store 1 hour 12 minutes 15 seconds in the table as 1:12:15 for stage 1.
Plus they may have received a penalty, so in another field I would like to say store a 10 second penalty...
anyway you get the idea.... please could somebody inform me on the best way to do this, it has to be stored as a time so I can add all the stages up later and create a report!
Thanks in advance...
View 3 Replies
View Related
Mar 13, 2006
Hi
I have a database which picks up information from my accounts package. It has the following fields
ID,Name,Date,Ref1&Ref2
I need to extract the last ID number and store it in a table or somewhere and then extract the above information after that number into a csv file in the order above. It is used for my invoices and therefore when I do an import from my accounts package it updates all the transactions.
An example would be I say put 5 invoices on with a ID nr's of 1, 2, 3, 4 & 5 and output to a csv file. When I run the query/macro? it looks at the table with the last ID nr and starts from 6 and so on
I just need the ones I have done today. It has to be the ID number as each invoice is put on it gives it a sequential number, the dates could be different for each invoice.
I hope I've explained myself:)
Thanks in advance
Cheesey
View 1 Replies
View Related
Apr 20, 2007
I know that it is bad form to store a calculated field in a table - normalization and all that - but I can see no other way around a need that I have.
I'm trying to make a database to store injury time for employees. If an employee is injured, he can do light work for a certain amount of time, but this light work need not be contiguous. But, the total time on light work must be known so as to see when it is used up.
For example:
Bob is injured on 4/1/06 and goes on light work on 4/2/06 until 4/5/06 (4 days). Bob is then taken off light work until 4/10/06 when he reinjures the same injury. This does not count as a new injury, because it is an aggravation of the old one. So, Bob goes back on light work on 4/10/06 until 4/15/06 (6 days).
So, I need to store Bob's total time on light work for this injury (10 days) so that I can keep a running total to check against the maximum for a single injury. All I can think of is to store the sum of days on light work as a field in Bob's injury record, but that means storing a calculated field in a table.
Am I missing an easy way to do this, or is there a method to do what I want that I am just not aware of?
Thanx for any help!
View 2 Replies
View Related
Aug 10, 2007
Hi,
I know it breaks all the rules to store a calculated field on a table, but I've painted myself into a corner with the way I built this particular database, not good, I have repeating groups and basically the whole database is built this way. I need one form to store a calculated value on a table to solve a problem on a report that encapsulates the total of all the repeating groups. Before you start the lecture, I know I messed up, but this is way to far down the road to start over. Any help would be greatly appreciated.
Gary
View 13 Replies
View Related
Oct 16, 2007
Hi all!
I have a bit of an issue. Im trying to retrieve a row from SQL and insert it into my local DB table.
However, when I try and execute the code, I get an error message. The data had not been inserted into my local DB table.
Here is the code:
strsql = "INSERT INTO EntryDetail_Local SELECT EntryDetails.* FROM EntryDetails where entrykeyid = " & plngentrykeyid & ";"
Set qdftemp = mydb.QueryDefs("qry_Append_EntryDetail_Local")
qdftemp.SQL = strsql
qdftemp.Close
qdftemp.Execute
DoEvents
Any ideas?
Cheers
View 2 Replies
View Related
Mar 10, 2014
I have encountered an issue when I was inserting a string (with newlines about 176 characters) into access table. This field in access has the data type TEXT and it was truncated after the insertion. It is strange because I have three other fields with the same format and no truncation at all. May I know what could be the reason and how/what is the recommend data type for storing long text?
View 3 Replies
View Related
Nov 17, 2006
Hi,
I am an Access newbie and I have a form running about 5 seperate formulas and I would like to store the info in the table that it is associated with so that I can use it later in a query to create another formula. Any help would be greatly appreciated.
View 6 Replies
View Related
