Hello,
I am designing a query as i have been advised in the following Quote:
Doc ManNow, you write a couple of queries. As a parameter to the queries, choose a date that will be your archiving cutoff or split point. Usually the last day of some month or quarter. Doesn't matter as long as you are consistent about it. The technical cutoff will be, in effect, 23:59:59 of the chosen date. The first record to be KEPT will be 00:00:00 of the next day in sequence.
Query #1 - Compute stock on hand as of your cutoff date. Create a single stock-on-hand transaction with the cutoff date. Put it in a temporary table until you need it. This will be a two-layer query. I.e. a query of a query.
Query #1A - a summation query grouped by item number for all transactions earlier than the archiving date so you can get the "inventory of X at archiving date"
Query #1B - an append query that draws from the summation query and feeds it to the temp table. The stock-on-hand record will have the archiving date
.
I am able to get "inventory of X at archiving date" and append the results to a temporary table but I can't see how to append the "archiving Date" data itself to the same table as the "Archiving date" is entered via parameter prompt? Any ideas?
I am doing my comptuer science coursework and one thing I need to include is "how does access stored files" and the basic description of file organisation. After some googlging around though I have found nothing, can anyone hlep me out on this?
I know that you shouldn't store totals in Access, but I can't think of a way around what I want to achieve and wondered if anyone had any ideas.
An item on stock will move from one location to another, I will need to list the 'balances' of this stock at each location, easy enough to do, but there are hundreds of item movements per day and the users do not want a history of movements to be kept but do require an ongoing balance.
e.g. 100 of the item moves from A to B, this movement record gets deleted, how would I know that the item stock at A has been reduced by 100 and B raised by 100?
I have a database that stores customer records and their associated insurance details. I need to be able to track any changes made to that record within a set time period ie if details are changed fine it is reported next time report is run. But if details are changed more than once in between reports then first detail change is lost.
I would like to work out a way that changes can be written to a ??seperate version of the table?? and stored in there to be reported seperately and not just the final change as is the snapshot nature of a report? Is this possible? Could I possible make another version of the main table appended onto it? I feel I 'know' what I need to do but not how to even begin it! Any help would be appreciated.
I am currently designing a contacts database for my business and need some advice on how to store addresses.
As I see it, I can either use a single field and carriage returns, or multiple fields which for some records may leave some blank fields.
The data will be used to perform mailmerges and will also be output to a contacts directory and I would be looking to display the address both horizontally and vertically. Is there a simple way of manipulating a multi line field so that it can be displayed on a form across the page on one line in a report?
I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
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!
I've created a simple database in which we enter order id (automatic), date (automatic), customer, po#, shipping date, and ship via.
then we scan the order (which may be from 2-20 pages long) into the computer so that we do not need to keep actual paper (trying to save on office space).
these files then get inserted the scanned object subform (containing the fields scan number, object, and order id).
I'm realizing that because we'll be scanning quite a few orders that this may take up quite a bit of space, i'm wondering if linking the objects rather than saving them within the database will help.
the only problem with this is that if we move the files containing the actual scanned images, that the whole system will get screwed up.
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?
I'm building a questionnaire/survey database for my work. Everything seems pretty straightforward, but I'm a little unsure about how to store the survey configurations.
I'm assuming that the survey will change sometime between now and the end of time so I'm tracking survey configurations (which questions and in what order). Right now my "Configuration" table has two fields: ConfigID (Autonumber), Questions (Memo). The 'Questions' field is a space seperated list of question IDs. The other way I could've done it is to use three fields: ConfigID (Long), OrderNum (Int), QuestionID (Long).
I figure the first way is more memory efficient in the long run and it will take much less time to add a new configuration.
The way I'm doing things now works for me, but I'd like to know if anyone thinks I'm setting myself up for trouble down the road or if my way is actually less memory efficient (I'm not as concerned with processing time).
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?
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!
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.
Which is a more robust approach when designing an Access database in regards to storing images, such as TIFFs. Is it better to store the images in each data record as necessary or is it better to just store a path in the database that points to a location of where the image resides (on a hard drive somewhere for instance.)
New to Access and learning the hard way. Im sure this is an easy one, but sure would appreciate the help. I am trying to sum a group of cells and display the total, which i have done. But I also want to store this total in my database. So my question is, how do i move my calculated total to my field that has the "record source" bound back to my field in the database? Hope that makes sense. Thanks in advance.
I have a table of records which is is basically temp storage, once I'm done inputing information, an append SQL statement is run to move the data to its permament table, then I use a delete statement to remove the values in the temp table.
Now, I have a column called Result, which stores the values of a radio button group (1, 2, or 3). I have a series of queries which count the number of times 1 or 2 appear (3 in this case, doesn't matter since it represents N/A. 1 = True, 2 = False, 0 = Null).
I'm having trouble getting the result of said query to store in a table. The statement is as follows:
SELECT [qryGetFail]![CountFail]+[qryGetPass]![CountPass] AS Total
FROM qryGetFail, qryGetPass;
This returns the sum of both counts, so I know how many questions were answered either Pass or Fail, hence I know how to score record. Here is my Append SQL statement as it currently works:
INSERT INTO tblAuditResults ( Question, Result, Comment, ACAPS, AuditDate, TeamMember, Auditor ) SELECT tblQuestions.Question, tblQuestions.Result, tblQuestions.Comment, [Forms]![frmAuditCreate]![txtACAPS] AS Expr1, [Forms]![frmAuditCreate]![txtAuditDate] AS Expr2, [Forms]![frmAuditCreate]![cmbTeamMember] AS Expr3, [Forms]![frmAuditCreate]![txtAuditor] AS Expr4
FROM tblQuestions
WHERE (((tblQuestions.Product)="4" Or (tblQuestions.Product)=[Forms]![frmAuditSelect]![txtVarProduct]) AND ((tblQuestions.Function)="3" Or (tblQuestions.Function)=[Forms]![frmAuditSelect]![txtVarFunction]));
This stores everything I need it to. But I cannot for the life of me get the statement to accept a value from a qeury and store it. I have tried adding another expression but it always just prompts for the value instead of pulling the value from the qryGetCalc.
Is there a specific way to have an INSERT statement pull a value off an active query?
I am trying to see if it is possible to store colors in a form or table and then reference them while in VBA. What I am hoping to do is when I write all my code for command buttons to change On Got Focus, instead of writing xxx.backcolor = RGB (255,255,255) i could do something like xxx.backcolor = Forms!HiddenColors!Command That way if i want to ever change the color scheme of the db, I can change it in one place rather than hunt lines of code.
I have a table (tblData) that stores all information for a couple hundred records.
I have a method that copy/pastes all this information to a webbrowser based on the URL TitleBar. If the titlebar changes I need a way to update what to look for so it can paste it to the browser with the new titlebar. I don't want to have to update it for every record so I created a seperate table with one record with just an ID and the string for the titlebar. I have a seperate form from the main one that allows the user to update this and store it back into the table. The problem I am having is using the information from that second table (tblTitleBar) and puting it where it needs to be in the VBA code to access the correct browser.
I don't think any type of relationship will work because that just requires me to input the titlebar straight into each record for the first table anyways. I don't see any way of storing a changing variable besides puting it in a table. I was wondering if there was a way to code a call to the second table to retrieve the single record's field, but so far I have had no success.
Any help/words/advice would be greatly appreciated, thanks for reading my problem.
I am pretty new to access, I will get right to it.
I am scoring rating scales. I want to be able to enter just the responses from a person, say on 80 questions (preferably into a form), where all the answers are 0 to 3 and have Access add the questions into the right groups to calculate all the sub-scores of the rating scale. For example, Scale A may consist of Questions 1, 6, 15, 29, 35, and 70 I need access to add those up and save them so I can use them in a report. I also need access to add the frequency of certain questions that were responded to with the answer 3. I have gotten Access to calculate the scores in a form by adding extra variables to the form, erasing the variable name out of the white box and putting in the syntax =[Q1]+[Q6]+[Q15], etc. in place of a variable name but it just sits there on the form and doesn't store it anywhere. Thanks
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?
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..?
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.
