Maintaining History Of Orders

Dec 8, 2007

I am creating a database for a company that sells a product with a variety of options.

They have all their previous orders in a works spreadsheet file. Each customer has their own file with every order for the past 15 years. There are probably about 1.5 million records.

The company wants all those 1.5 million records accessible in their access database.

I've brought in about 20 records for the history and they can run a query to see a customer's past orders by their account number.

My question: Should I put all the history into one gigantic table or would it be best to try and create separate tables for the history? (Maybe history by state.)

The history table then will take the new orders each year and add to that table.

(Eventually, once the database gets done I'm thinking we'll have to step up to SQL but not sure.)

Thanks for any advice.
Melanie

View Replies

Modules & VBA :: Entering Orders Into The Orders Table?

Mar 5, 2014

I have a database where I have 3 tables. first is client data, second orders data and third the products data.
What I would like, is to have a button on my form that after selecting from a combobox a product, it wil enter it in the tabel orders in the next available free record (row). I created a button and the combobox in the client form. The Products table has also the price per unit in it. It should take the whole row of data and put it in the Orders table.

Is there a way to do this? I guess it needs to be done with vba, but not sure.

View 9 Replies View Related

Maintaining And Updating A Database.

Dec 6, 2006

I've set up a database for product tracking. It is going to be used by several users at one time. I'm going to implement it in stages as inevitably there is going to be some fine tuning to be done etc. I have a few questions with regards to editing.

1, I presume that I cant alter the database while it is being accessed by others?

2, Is it better to alter another copy of the database and the import the data being generated and then copy it to back the server in one go?

3, If so how do i do this as I�ve had a trial go and failed miserably?

4, Is there another way of doing this minimising the down time of the database?

Being relatively new to access your help would be gratefully appreciated.

cheers

EQ

View 1 Replies View Related

Maintaining Associated Variable When Grouping

Nov 17, 2014

I have an issue using the Simple Query Wizard in Access.

I am attempting to Group by the field 'HouseName' and to group by the Max 'Area'. However I also want to retrieve from the query the County which is associated with this.

I have included a sample table below. I wish for my output table to be as follows;

HouseName: County: Area:
Park 1 A 100
Park 2 C 78
Park 3 A 70

(Where Park 1 is HouseName attribute, A is County attribute and 100 is Area attribute)

Sample Input Table:

HouseName: County: Area:
Park 1 A 100
Park 1 B 60
Park 1 A 85
Park 2 C 78
Park 2 D 34
Park 3 A 70

In SQL View my query looks like this;

SELECT TableExample.[HouseName], Max(TableExample.Area) AS MaxOfArea
FROM TableExample
GROUP BY Table.[HouseName];

How to link the associated County to the output.

View 1 Replies View Related

Maintaining Database With Access 2003

Apr 5, 2006

i inherited a database created i believe in access 2000
everytime i need to update a report or what not i must find a machine runnning 2000
how do i go about working on this database with access 2003

Thank you

View 3 Replies View Related

Maintaining A Field Entry Over Multiple Forms

Jul 17, 2006

I have a form with a "WorkerID" field. Once the worker enters his ID once, I want the same ID to show up for all the subsequent records so that he doesn't have to type it over and over again. Is there any way to do this? Sorry if I sound totally new to this (I am)!

View 1 Replies View Related

Maintaining Referential Integrity W/ Main Form

Aug 3, 2006

How do I maintain referential integrity between a main form and a subform, each based upon different (but joined with integrity enforced) table?

Here's the situation:
I have two tables: tblContracts and tblPayments. tblContracts has an autonumber field called IDKey as its primary key. tblPayments also has an IDKey field (Integer datatype). The two tables are linked in a one-to-many relationship on the field IDKey with referential integrity enforced.

I have a main form based upon tblContracts (the "one" side of the relationship) that has an embedded subform based upon tblPayments. the two forms are linked Parent/Child on the IDKey field.

Here's the problem: If a user goes to a new record in the main form, it allows them to enter information in the subform without entering information in the main form. This means that a new record (and its corresponding autonumber IDKey field value) does not get generated in tblContracts and I have an orphan record in tblPayments that is not linked to any record in tblContracts - which violates the referential integrity that is supposedly enforced between the two tables.

Any guidance on how to deal with this would be greatly appreciated.

TIA,
Carolyn

View 5 Replies View Related

General :: Securing And Maintaining A Stand-alone Unsecured Db?

Dec 3, 2012

I have an application that is used by individual teachers to generate reports for central admin. It is not secured except I have locked out access to all objects, Navigation pane is hidden, etc to protect the integrity of the tool. I must also maintain this as changes are propagated. I am trying to find a way to upgrade forms, functions, queries, etc without manually having to unlock and relock every db for each school site when changes are required. I have tried importing and exporting from a master db, turning objects on and off by recognizing my password, splitting code and data (db's are on flash drives and path changes every time they are inserted, many teachers cannot handle refreshing table links), I have tried writing code to import the changes at next startup, etc. It is written on Access 2003.

View 3 Replies View Related

Queries :: Maintaining Proficiency - Expiry Of Count

May 25, 2014

I have a table for logging experience for employees related to a given requirement.

The requirement is that any employee, to maintain proficiency ("be current") , must maintain an event count of minimum 5 within last 3 months. Or else the employee is not current and other measures must be taken.

I want to know when the currency expires for each employee (= at which date does the employee no longer have a count of minimum 5 within last 3 months).

So with the data below

tblProficiency
--------------
ProfID (PK, autonumber)
ReqID (FK)
EmpID (FK)
Completed (Date)
EvenCount (integer)

Code:
ProfID EmpID ReqID CompletedDT EventCount

10 2 1 10/04/2014 4
11 2 1 11/04/2014 3
12 2 1 12/05/2014 2
13 2 1 13/05/2014 5
14 2 1 14/05/2014 3
15 2 1 15/05/2014 2
16 2 1 16/05/2014 1
17 3 1 17/05/2014 3
18 3 1 18/05/2014 4
19 3 1 19/05/2014 3
20 3 1 20/05/2014 1

The result should be:

EmpID Expires
2 14/08/2014
3 18/08/2014

I need a hint for some SQL to do this.

View 4 Replies View Related

Creating Simple Invoices But Maintaining Information

May 19, 2012

I need to create buyer and seller invoices within my access 2003 databases, my sister in law runs an antiques auction house and I'm working on a database to capture all their information.

I've created a 2 queries (a buyer invoice and seller invoice), show all unsettled items with the relevant item information and fee's.

However, I would like it to automatically allocate an invoice number, and store the information back to a table (seller and buyer ID, total number of items on the invoice, total fee etc)

I'd like store the invoices as well, so within the customer record form, I can include a box showing a list of the buyer invoices on one side and seller invoices in another (not all customers are buyers and sellers).

Once I've issued an invoice, can I automate it to show that item as then as invoiced?

Can the invoices be editable at all?

When they come in and settle their account, I also need to feed this information back in as well.

Can i arrange an invoice for seller's particularly, to show all sold items, and the fee's associated with them

Returned items as well as any associated fee's with them?

I have been looking at the Northwind example.

I see how they have an orders details table and orders table, I could replicate this.

I could have an Buyers Invoice table and Buyers Invoice Details table, but how do I generate an invoice, pull the next Invoice number from my (currently blank) invoice table, and pull in the outstanding items for that buyer for that specific auction date (I have a query), and then populate back information from the invoice?

I like the fact that their order form is editable, do any changes go back to the query, that then populate the invoice when you click print invoice?

I need to be able to (at the end of the auction) pull all items from my item log, for that buyer, and print them an invoice, automatically saving all the invoice details back into my database.

I need be able to automatically flag the items in my items table, as invoiced and pull the invoice number in?

I can see how all the Northwind tables, queries, and forms relate to each other and subforms, but I'm not sure how to actually create an invoice, get invoice number, merge with my query, and then feedback in.

View 1 Replies View Related

Maintaining Unique CustomerID With Multiple Purchases

Mar 28, 2015

I am trying to make a basic database system to track orders and contacts and the like for my small business. I have created a series of databases that look pretty standard, something like this example:

I want to create a form that I will open and complete every time I process an order. I want to fill out both the customer info and the order details at one time.

Assuming that the "CustomerID" in the "Customers" table is set to Autonumber in order to create a unique ID, how do I keep from creating multiple new "customers" every time I enter a new order regardless of whether or not that customer already has an ID number from a prior order?

It seems that I would need to make some sort of Macro that would say something like, if the "CompanyName" entered is already listed in the 'Customer' table, use the existing CustomerID, if it is not listed in the Customer table create a new CustomerID.

Should I be looking into Macros, and if so should the macro be built into the form or the database?

View 1 Replies View Related

Tables :: Importing Excel Data - Maintaining Column Order

Mar 4, 2013

I've been using MS Access 2007 for years to manage some Excel data. running some queries etc. Just recently I've been encountering problems when importing data into an existing table. When I do it now, I get a "Subscript out of Range" error. To troubleshoot, I imported into a new table and when doing so, the fields no longer match the column order of the spreadsheet. They all get imported but appears in a different order. I think this is why I am getting the error message. How can I go about ensuring that the data gets imported properly into my already existing table? My fields in "Design View" will match the order of the Columns in the Excel spreadsheet.

View 2 Replies View Related

Deleting Record Using Main/Subform While Maintaining Record Position

Jul 6, 2006

Hello All,

I have an ordering db that utilyzes the typical Form / Subform layout. I
would like to be able to delete a record from the Main form and maintain the
same postion relative to the other records. For example, if I am scrolling
through the records and want to delete record #45, After I delete it I would
like to be able to have record #44 visible to the user.

I am currently using (2) SQL statements to delete the records from the main
Order table and the Order Details table which are linked without Cascade
Delete Related Records being on.

I had set-up a recordset procedure to try and find the bookmark set before
deletion but the only way I can get the sub-form to not show a blank form (no
controls visible for the record just deleted and the #deleted in controls on
the main form) is to requery after the delete, which loses the bookmark. If
i place the ReQuery at the end it displays the records properly, (without the
blank record) but returns the record to the first record. I have tried
turning off any sorting references to OrderBy, etc. but it still returns to
the first record.

I have also tried using "DoCmd.RunCommand acCmdDeleteRecord" but it doesn't delete the record from both tables.

Dim rst As Recordset
Dim strSearchName As String

Me.AllowDeletions = True
Me.sfmOrderDetails.Form.AllowDeletions = True

Set rst = Me.RecordsetClone
strSearchName = Str(Me.sfmOrderDetails.Form.txt_Order_No.Value)
rst.FindFirst "Order_No = " & strSearchName

CurrentDb.Execute "DELETE Order_ID FROM tblOrderDetails WHERE Order_ID = " &
Order_ID,dbFailOnError
CurrentDb.Execute "DELETE Order_ID FROM tblOrders WHERE Order_ID = " &
Order_ID, dbFailOnError

Me.Requery
Me.sfmOrderDetails.Form.Requery

Me.Bookmark = rst.Bookmark
Set rst = Nothing

Thanks!

View 6 Replies View Related

Customer History

Nov 1, 2005

Hi,

I currently have a "customer" form (which displays client details from a table - name, address etc) I would like to add history comments for each customer.

Does anyone know how i would go about doing this.

I image i could press a button on the current "customer" form that will open a history form which after typing a comment would be displayed on the main "customer" form, with date, time and comment.
I would obviously be able to add lots of comments to each record.

i would be greatful for any kind of help

Regards
Chris

View 4 Replies View Related

Patient History Db

Mar 19, 2006

I need to write a db for my boss where he can access his patient treatment information. How can I make the db (Access '03) to be able to add new treatment to the same patient and to display all treatments seperately on a form? There are about 500 patients. Appreciate any assistance.

View 1 Replies View Related

Calling History

Jul 13, 2006

Hi. I am creating a contact management database. Also I am new to Microsoft Access. How would I create a calling history record for each of the clients in the contact database? What would be the best way and how would I go about it? The calling history ideally would include the call recipient, time, date, subject and notes. Thanks. Take care.

View 1 Replies View Related

Help Creating A History...

Apr 27, 2007

Hi,

I am trying to create a historical record of Employees at our business. What I would like to do is have a query that will create a begin date and create that date for each day starting w/ like 1/1/06 and continue thru Date()-1. For example:

Jane Doe and John Doe started employment on 1/1/06. Jane Doe was employed until 1/5/06. So using their "Hire Date" I would like the query to do this..

Created Name Hire Date Termination

1/1/06 Jane Doe 1/1/06 1/5/06
1/1/06 John Doe 1/1/06
1/2/06 Jane Doe 1/1/06 1/5/06
1/2/06 John Doe 1/1/06
1/3/06 Jane Doe 1/1/06 1/5/06
1/3/06 John Doe 1/1/06
1/4/06 Jane Doe 1/1/06 1/5/06
1/4/06 John Doe 1/1/06
1/5/06 Jane Doe 1/1/06 1/5/06
1/5/06 John Doe 1/1/06
1/6/06 John Doe 1/1/06
1/7/06 John Doe 1/1/06

So in this case Jane Doe will show up every day in the "Employee List" until she was terminated. Is it possible to loop a date like this in a query or will I have to make an estranged table with a list of dates? TIA for any help.

View 2 Replies View Related

Keep History Of Changes To Fields

Apr 27, 2008

Hi

I have 2 fields - "Balance" and "Date" - each time data in the "balance" field changes, the "Date" field updates to todays date (thanks to help from Kiwiman in the forum earlier).

I now need to keep a history/log of all data input into balances and the dates.

The new field to store this information is called "Balance History". so an example of what i want to end up with would be

01/01/08 300.00
01/02/08 250.00
12/02/08 210.00

Any help would be greatly appreciated

Thanks

Geoff

View 1 Replies View Related

History Words

Jan 9, 2006

Hi,

In MS Access, form

I want make like this:

1st time:
Company Name: Automobile
2nd time:
Company Name: AuID

If I type A, then it will show out the word, but the cursor is till After A, because if we type another character which will change another word
3rd time:
Company Name: Automobile

4th time:
Company Name: Au[I]ID[I]

What is the keyword to search about it in the forum?
Anyway, does anybody know how to solve it?
Please let me know, thanks.

View 1 Replies View Related

Multiple Orders

Sep 2, 2007

I am new to access and have a form set for processing orders. How do I process multiple orders i.e a customer orders an onion and a pepper. Currently I have to create 2 orders and would like 1 order with 2 items. I am sure this is very simple. Hope someone can help

View 6 Replies View Related

Orders Database

Dec 27, 2007

I know this is probably very simple but I do not know how to do it.
I have:
Table 1 - customers
Table 2 - Orders

At the moment, when a customer orders 2 items, I have to create 2 seperate order numbers.
I would like to have both items under one order number. Can anyone help with an easy solution.

Thank you

View 1 Replies View Related

Multiple Orders

Oct 18, 2007

I have asked this before but could not work out the answer i got(sorry). I need things in very simple terms. I hope someone can assist me.

I have customer table and an order table. Customer table obviously holds all customer info. my problem is orders.

I would like a table/form that allows me to input muliple orders. i.e:

Customer: Smith
Item: Apple
qty: 2
Item: Pear
qty: 1
Item: Orange
qty: 3

I would like all items under 1 order. Currently I have to process them all individually. Can someone help please

Thank you

View 2 Replies View Related

Duplicate Orders

Jun 9, 2005

It seems I continue to run into this problem.

A company sells products in packages.

Example: Let�s say you build PCs

All of your PCs are sold as a complete product with 10 or so components

Your choices are to reenter every item each time you make a new quote, or automate the process.

This is the current method

Step1:
Create multiple tables identical with the orders detail table, and create all of the records needed to satisfy the order packages. Each table is one order package.

Step 2:
Create an order as you normally would.

Step 3:
Change the linking number in the package table (to the appropriate order number)

Step 4:
Paste the contents if the table into the Order Detail Table.

This works well but is labor intensive, and is not error proof.

So finely the question. Is there a way to automate this process with the selection being made form either a drop down list or a query .

My gut tells me it a macro thing but I don�t use macros for much and my VBA skills are limited to adapting code that I pick up here and there.

Any guidance would be greatly appreciated.

Sincerely,
Joe

View 2 Replies View Related

Adding Price History

Sep 11, 2005

ok i didnt know what section to put this in so i put it here. what i wanna do is add a price change history to my database. for example is the price of an item is changed in my database i want to be able to see what the previous price was and when it was last changed, either in forms of reports doesnt matter i just need somewhere to view this info. problem is i have no idea where to start.

any help provided is muchly appreciated thanks.

View 4 Replies View Related

Help With Data Structure And History Log!

Mar 24, 2006

Hi Guys

I am pretty new to programming with access but i am trying to create a database system with history logs and i am stuck on how to structure it.

I'll explain a little more, i am trying to setup an application so that if somebody brings an item into myself to purchase i can book it into the system so that it records the customer that it came in with and also the item details. I will then need to resell this product onto another customer, the problem i am having is that once the item has been purchased and sold on i will then need to keep a record of this so that if the customer comes back with the same item he has purchased to resell back to us we can keep all the details in history so that each product that is purchased and re-sold has all its past history from every customer that has purchased it and sold it back to me.

Any help would be much appreciated.

Thanks
Brad