Storing A Number In Another Table

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 Replies


ADVERTISEMENT

Table For Storing Updates

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

Storing Time In A Table

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

Storing Calculated Fields In A Table

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

Storing Data From Textbox To Table

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

Storing A Calculated Field On A Table

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

Returning A Row From SQL And Storing In Local Table

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

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 14 Replies View Related

Storing Info From A Formula In A Form To A Table

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

Storing The Date The Record Was Entered Into A Table

Jun 27, 2006

Hello everyone. I am wanting to store the date the record was entered into the table. But I don't want the users to have to type this in; I thought that Access could grab the date from the system date.

I am new to this and I looked at the Date function but I am thinking it will just always update to the current system date.

Thank you very much if anyone could point me in the right direction.

View 3 Replies View Related

Tables :: Storing Calculated Values In Table

Jan 8, 2014

I know that as a general rule one should not store calculated values in a table, but if I need to include such a value on a number of forms, and calculating that value involves manipulating an ADO Recordset, does there come a point where the performance hit outweighs the design "quality"?

View 1 Replies View Related

General :: Storing Calculated Fields In A Table

Jul 26, 2012

I know that it isn't usually best practise to store calculated fields in a table, but in my situation it is necessary, and I haven't really found a good answer for this after searching these and other forums.

Currently my database has one "Name" field (I didn't make it like this) which might look like this "Jonathan (John) Smith"
Now I also have reason to grab the name like this "Smith, John" for other purposes.

I've set up a form for inputting new people with separate fields for firstname, preferredname, and lastname. I have the calculated fields:

=[fname] & " (" & [pname] & ") " & [lname]
=[lname] & ", " & [pname]

(Later I will add in all the iif's for if there is no preferred name)

Upon finishing the form, the user hits Submit, and I would like to store the concatenated values into the table in their appropriate fields.

I can do this with a SQL INSERT INTO statement but I'd have to add all 25 or so fields from the form, and injecting user input directly into a SQL statement is not best practice either.

View 2 Replies View Related

Storing And Accessing Word Documents In A Table?

Jul 4, 2015

I have a set of maybe 5 template word docs which I populate with info form our database using bookmarks , the word docs are currently sat in a shared network drive.

I have started wonder if it would be better to store them in a table and call them from there to keep things together.

View 1 Replies View Related

Storing A Calculation As A String In A Table And Executing It In A Query.

Jun 11, 2007

N.B. This is not your usual 'Bad practice to Store calculated values in a table' post

I have a table of items.
Each item has a different formula attached which I need to store as a literal value in the table and execute it as a normal expression in a query or vb.

e.g

Columns:Item, QTY, Hrs, mins, ItemFormula
Data: Car, 2, 7, 3, [QTY]+ [Hrs]/[mins]

So ' [QTY]+ [Hrs]/[mins]' would literally be stored for that rercod and is how it would be displayed in a table, form, report etc as it is important for the user to see how the calculation is performed as well as seeing the end result which will be calculated as normal in vb or a query.
The formula will vary from record to record.

How do I go about this?

TIA

View 6 Replies View Related

Adding Feild Values And Storing In Another Field In Same Table

Mar 19, 2005

I have been struggling with this and would appreciate any help. It sounds so simple!!! I have 4 fields (Unit Price 1, Unit Price 2, Unit Price 3, Unit Price 4) in a Orders table that I want to add together and store in a 5th field (Sub Total) in the same table using a form. I am having problems with the sum and I am unsure how to get it to show in the Sub Total box in the form as well as the table. Thanks for your help!!

View 2 Replies View Related

Modules & VBA :: Storing Outlook Attachments And Patch In Table

Jun 13, 2015

Currently, I'm taking my unread emails, storing them in a table then sending any Excel files to a specified folder I've modified some code I found while surfing and it works great but I'd really like to send the attachment name and file path with the email recordset. It's been a long day so maybe it's super simple. Below is my code. Office 2010 Windows 8.1

PHP Code:

Function ReadInbox()
Dim TempRst As DAO.Recordset
Dim OlApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim Atmt As Outlook.Attachment
Dim InboxItems As Outlook.Items

[Code] ....

View 1 Replies View Related

General :: Storing 2 Identical Fields On Same Table In Database?

Nov 5, 2013

I want to be able to store 2 identical fields on the same table in the database. The copy will start off being the same as that input in the original field. 90% of the time the 2 fields will always be identical but the copy may be changed at a later stage. I looked into using the copy as a calculated field but found out these can not be updated. Is there another control I can use or do I have to code the population of the 2nd field specifically (in AFTER UPDATE EVENT?) myself. The added complication is that the original and copy must be selected from a combo box as all values are held in a separate table.

Also trying to make the second field = to the first field in the design stage of the table using default value

SO if I put default value = [table].[fieldname] I get an error stating could not find field 'table].[fieldname', and yes the opening and closing square brackets are missing from the error.

View 5 Replies View Related

Forms :: Storing Small Icons In A Table For Use In Continuous Form

Nov 29, 2013

I searched without success for a solution to show small pictures (icons) in a table field to show up in a continous form.

Purpose is:
a communication log table has comm codes such as
- mail out
- mail in
- tel call in
- tel call out

the comm log table has the fields
CustomerID, datetime, CommCode

The CommCode table hast the fields
CommID, CommCode, Icon

The form (subfrm) should show in continous form mode to each customer..Datetime, (commCode), and to visualize the Commcode the small image (icon) presented by an envelope with an arrow right, an envelope with an arrow left, etc.

I cannot find a solution for storing those little images and retrieving them from an OLE-Field.All my other pictures I do not have stored in the tables, but only the image path, but for that I would prefer to store them directly into a table field as they do not blow up the database.

View 1 Replies View Related

General :: Data Entry Form - DLookup And Storing To Table

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

Tables :: Consecutively Number Table Rows With User Determined Start Number

Jul 13, 2014

I have an Access2007/SQL Server 2012 system with 20 users for an insurance company. The company does most of its business via a network of vehicle dealers around the country. If someone comes in to buy a motorcycle, boat, or recreational vehicle at a dealership they need insurance to take it home, and our dealers send the quotes to us.

The dealers, in turn, receive payment from us each month for their efforts. Some are paid a % commission on the premium, some are paid for each quote they send regardless of whether the policy actually sells or not, and some are paid a set amount per sold policy. (Yes, that is relevant information!)

We already have reports that tally the amounts due each dealer based on their payment scheme, but last month our bookkeeper had to write about 650 checks manually because the check writing is not automated. She'd look at the report, and then enter name, address, and amount (in digits and words) into Quick Books and print the checks from there, a horribly tedious process. I've been asked to print the checks from Access. Basically one click would print all 650 checks.

I've opted to use a Make Table query to move the commissioned dealers amounts to a single location, and then to run two append queries to add the records from those paid per quote and those paid per policy. At the end of the day, one table contains all the information necessary to print the checks...except one.

The check number.

I need a way to sequentially number each record in the new table with a user generated starting point, the first check number.

By the way, the check blanks are on standard letter sized paper, three to a page, with tear-off perforations to separate them, in case that information has any relevance.

I think the best way to accomplish this is from the report itself. I've created a blank field on each record for the check number, and what seems most logical is that the sequential number is generated on print and written back to the table, rather than just generating all the numbers at once. That way, should print ever be interrupted, it will be easy to take up where we left off.

View 14 Replies View Related

Data Tranfer From One Form To Another Without Storing Data In Db Table

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

Data Tranfer From One Form To Another Without Storing Data In Db Table

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

Queries :: Storing SQL Queries To A Table

Apr 4, 2013

I can't seem to find it and this has to be the last bit of stuff I need to get my Database up and running for my boss using an EXE front end.

What I am trying to do is something similar to what I did for my reports

I have a table with the following fields

ID = Autonumber
Discrip = Text (Discription of the Report)

The combo box on the On Change event has this code

Private Sub ReportCombo_AfterUpdate()
DoCmd.OpenReport ReportCombo, acViewPreview
End Sub

Can I set something like this for SQL commands?

View 5 Replies View Related

Appending Records With A Desired Auto Number In A Table With Auto Number

Oct 26, 2005

Here is my issue. In a table with an Auto Number index some records have been deleted. I have been able to recreate them along with their original auto number. The problem is that I do not know how to append these records forcing the original auto number. I have tried changing the auto number field to a number field in the table, this works except I cannot change it back to auto number.

I am sure I’m not the first with this question or issue. I did search through a couple hundred entries about auto number before I posted this question.

Jim

View 7 Replies View Related

Forms :: Need To Count Number Of Records In Table Between Dates In Another Table

Mar 6, 2013

I have created a booking system for a set of resources for schools. Most schools have a membership which entitles them to 2 free sets. I have a booking form with a membership subform (membership table), and a booking details subform (kitloan table).

Once a school is selected on the main form, the membership subform shows the most recent record for that school based on schoolID.I want to display the number of sets they have already had within their membership period (can start at any time of the year, and lasts for 1 year) on the membership subform, so we know how many free ones they have left.

I therefore need to count the number of KitBkID (ID of the booking) in the Kitloan table where SchoolID = the SchoolID displayed on the membership subform, and the DateOut (booking date on kitloan table) is between the DateJoined and DateRenewal displayed on the membership subform (from membership table).

I can do this with a query which works when run and provided with the parameters SchoolID, DateJoined, and DateRenewal.

SELECT Count(Kitloan.KitBkID) AS CountOfKitBkID, Kitloan.SchoolID, Kitloan.DateOut
FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID
GROUP BY Kitloan.SchoolID, Kitloan.DateOut
HAVING (((Kitloan.SchoolID)=[Me].[SCHOOLID]) AND ((Kitloan.DateOut) Between [Me].[DateJoined] And [Me].[DateRenewal]));

What I can't do is get it to run on the form and take those values from the form.From the searching I've done, I'm thinking a DCount should be the way to go, but I cannot get the criteria right. I created a query (KitloanCountQry) so that criteria could come from both the kitloan and membership tables.

SELECT Kitloan.KitBkID, Kitloan.SchoolID, Membership.DateJoined, Membership.SCHOOLID, Kitloan.DateOut
FROM Kitloan INNER JOIN Membership ON Kitloan.SchoolID = Membership.SCHOOLID;

I have put the DCount as the control source for a textbox on the Membership subform (but have tried it in VBA too):
=DCount("KitBkID","KitloanCountQry")
This works but obviously gives me the total for all bookings.

[code]....

Although I have to admit to getting lost in the syntax. This produces #Error.

View 6 Replies View Related

Storing Files

May 8, 2005

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?

Thanks
Chris

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved