Tables :: Generate New Order ID For Same Customer In Orders Table?
Oct 3, 2012how do i get form to generate a new order ID for the same customer in my orders table
View Replies
ADVERTISEMENT
Customer IDS With Multiple Orders
Dec 28, 2006Hi,
I have a table that is like the following: I have customer IDs and Order Numbers. Most customers have only one order number. However, there are a few customer IDs that have multiple order numbers. How would I select only those records for customers who have multiple order numbers?(I want to have all the fields available, i.e. customer address, name of the order, etc, not just the customer ID) What is the SQl for this? Thanks for your time.
General :: Create Invoice Per Month Of Multiple Orders Per Customer?
Mar 19, 2013My current database creates an invoice per order. Until now that worked wonderful. Now i want to create an invoice per month per customer with multiple orders on the same database. So i will have to create a new table/query on the existing order table.
View 1 Replies View RelatedGeneral :: Auto Generate Unique Customer IDs
Aug 16, 2012I am in the process of creating a DB for customer names, job numbers and document numbers, but have run into a problem right from the start.We are using a naming convention of the first 3 letters of the customer name, and 3 numbers after that. Eg. Customer names Allstar, Allpro would be All001 and All002 respectively.
The job numbers would then be sequential numbers, for each customer. So the first job for Allstar would be All001-001 and so on.How do I get Access to check/create the customer ID's? And then how do I get Access to check for the next job number for each customer?I was able to do this in Excel with the following;
=LEFT(B12,3)&COUNTIF($B$3:B12,LEFT(B12,3)&"*") in one cell
=IF(B12="","",LEFT(D12,3)&TEXT(RIGHT(D12,1),"00")) in another to add the preceding zero's.
Obviosuly , Excel is not the best way of tracking customers, jobs, documents etc.
Modules & VBA :: Entering Orders Into The Orders Table?
Mar 5, 2014I 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.
Tables :: Creating Relationship Table From Clients To Orders
Feb 4, 2014I have a Client database table in Access. I now need to add a simple order table (related) to the client table. I have a client ID field set to autonumber in the client table. As I start to create the orders table I'm not sure how to link the two so that I'm not entering data twice and have assurance that they are tied together.
View 4 Replies View RelatedCombining Customer And Customer Contact Tables
Dec 24, 2011I have a DB set up with a debtors table (Customers) and a Customer Contacts table, I was thinking of combining these two tables into one.Most of the Debtors are companies, but a few are actual people. With the current setup, i need to have both a debtor and a customer in two different tables, but with exactly the same data.
View 1 Replies View RelatedGeneral :: Create Check On A Field (customer ID) Of Customer Table In MS Access?
Sep 7, 2012create a check on a feild(customer id) of customer table in MS Access , as "Customer ID is of 8 characters, the first 4 are alphabets and the last 4 are digits"
View 6 Replies View RelatedAdd Customer Name Into Table Each Time Unique Customer Added To SaleTable
Dec 11, 2013I have a Table Sales - that lists customer and order information
Most of the customers are new - is there a way to populate Customer Name Table with a new entry each time a new customer is input into the Sales Tale
(Without having to add the custome to Customer Name Table - then going to Sales Order)
OR
Failing that - is there a way to use a list box that points to Customer Name Table (and if it doesn't find the one you want, will allow you toadd a new record) In the SAME form as you use to add to Sales Table.
So:
Date (textbox linking to form.Sales) Customer Name (List Box linking to form.CustomerName + adding the info to form.Sale)
Should Be Easy For Someone - Show Only Last Customer Order, By Date?
Feb 22, 2006Hi all, i have a query which will be used for reports, and only the last order placed by a customer is needed to be shown. Here's the table used in the query:-
tblCustomerPurchases
CustomerNumber
PurchaseNumber - PK,Autonumber
DateOfPurchase
SeasonID
TotalCost
So obviously for one customer there can be several orders, but i just want to show the latest one by the date field. I have tried grouping by the date field on both Last and Max, but with all the other fields from the table in the query (i need to have all the fields from the table for the report by the way) it still shows all the orders for a customer.
If i only include the customernumber field and dateofpurchase field and then group by date i have 4923 records, which is the correct amount, only one order per customer (the latest one) but adding any other fields to the query gives me the full 7000 odd orders (several per customer).
Any help would be much appreciated, many thanks in advance.
General :: Use Drop Down Control In Service Order To Select Customer
Mar 1, 2014I have a client table.
I have a client product table for ski's
Each Client has 1 or more products (skis)
I have a Service Order table and form that I use a drop down control in the Service order to select the customer.In the Service Order Form a I have a continuous Subform for detailed service.
This is where I'm having the problem.In the detail subform I want to select from a drop down box the customers particular ski that I want to service. how to have only that one customers ski's to show up.
Tables :: 2 Table To Generate Separate Matrix Table
Mar 9, 2014I have 2 tables
- Staff Position(Unique Position Name, Description, Hierarchy)
- Training (ID, Name, Description etc.)
I essentially want a table with Staff Position as the Field, and Training as the Rows. The intersecting entries/matrix will be Yes/No to say whether that staff positions requires that training.
Simple Example
..................... Worker ........ Senior ........ Principal
IT Training ......YES ............. YES .............. YES
Accounts ......... NO ............. YES .............. YES
Management ... NO ............. NO ............... YES
I need the user to be able to add as many training entries and as many staff positions as they want. It doesn't HAVE to be that sort of format...
Newbie Needs To Find Out How To Remove Autonumber From Access Customer Order Template
May 19, 2006I am trying to use the template provided in the Access database wizards, pages and projects. Its called Order Entry. What i would like to do is edit the template to fit my needs but i need to change the Product ID feild from Autonumber to a product id of my own. I would however like to keep it as the Key.
Any Suggestions?
Mulith
Newbie Needs To Find Out How To Remove Autonumber From Access Customer Order Template
May 19, 2006I am trying to use the template provided in the Access database wizards, pages and projects. Its called Order Entry. What i would like to do is edit the template to fit my needs but i need to change the Product ID feild from Autonumber to a product id of my own. I would however like to keep it as the Key.
Any Suggestions?
Mulith
Tables :: Auto Generate Hyperlink In Table Based On Field Name
Aug 29, 2014 I Have a table with 15000 entries. This table lists components we use. This table is used to generate queries/forms. I have been asked to add a hyperlink to this table to link to a drawing of the component. All the drawings are in PDF and in the same folder. I am looking for a way to automatically update the hyperlink fields all at once. The Hyperlink will be in the format of servershareddrawings12345.pdfwhere 12345 is the component name from the table.
But there are a few small problems with the component names.
1. If the component begins with a B- . The drawing name will be all the characters except when there is a second dash in the component. So if the component is B-12345-678 the drawing name will only be B-12345.
2. If The component begins with AB, The drawing will be the first 5 characters regardless of what follows, e.g. AB123.
3. Finally for all other components the full component name will be the drawing name.
Putting the naming to one side, how to go about auto generating the hyperlink. I have posted this in the table section, but maybe this should be in another section like queries or VBA.
Tables :: Make Calculated Field In One Table That Calculates Values From Another Table Linked By Order ID
May 13, 2015I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
View 3 Replies View RelatedTables :: Autonumber Order In Existing Table
Jul 9, 2015I was adding an autonumber field to an existing table and I assumed the numbering would follow the order of the primary key but that doesn't seem to be the case.
View 9 Replies View RelatedTables :: Representing Single Orders And Kits
Dec 27, 2013 Single products night be thinks like 2-inch wheel or 1 inch wheel in a choice of colours, front axle, back axle etc that can be ordered as distinct products
A kit is distinct product in its own right consisting of a bundled set of other single product components.
Question 1: I would quite like to have single products and kits use the same ProductID index but I think Single products and kits need to be on separate tables in order that the product components included in a kit can be defined. So what I would like to happen is that if I open a kit form on my database in order to define a new kit that it would somehow look at the index for the single product table and take the next assigned ProductID. I am not sure of the best design approach with regards to tables and relationships needed to enable this.
Question 2: When associating the single product components of a kit with the kit record it will be necessary to select from the entire list or single product components as all single products are potentially valid kit components. If I were to do this using the tbl_single_products as a lookup table it would be a very long lookup list the user would have to scroll through. Are there any functions or techniques that would allow me to dynamically search a lookup list as the user enters text e.g. The user enters "T" the list pointer goes to entries beginning with "T" the user enters "e" the list pointer goes to lookup list entries beginning in "Te" etc.
Question 3: Is there anyway apart from autonumber to create an automatically incrementing index of your choice for use in tables e.g. PROD001, PROD002 etc...
Tables :: Modifying MS Access Database - Direct Order Or Placement Of Fields Within Table
Feb 5, 2013Is it possible to direct the placement/order new of fields when modifying an Ms Access database in code?
I need to modify the schema of an MS Access database via code - but I want to be able to direct the order or placement of the fields within the tables.
For example - if TableOne has 3 fields - Field10, Field20 and Field30.
I would like to be able to add say Field15 between Field10 and Field20 - not just append it to the end of existing fields.
I believe it is possible as you can do it within Ms Access itself. I can use ADO, DAO, ADOX or SQL for that matter - but it seems all of these offer no placement of the field within the table.
Tables :: Tracking Number Of Orders A User Makes
May 7, 2014I want to track how many orders a user makes. So every time they place an order it increments 1 after each order.
I currently have the following tables,
Order
OrderDetail
User
Should i create an UserOrderHistory table?
Eventually i want this information to form part of an order number. eg
196(order primary key) AR (UsersInitials) /23 (number of orders User has placed)
Tables :: Queue With Several Concurrent Entries - Reduce Duplicate Orders
Jan 24, 2013I'm trying to create a database that is going to be used to deliver some work to several of our users. Each time they open a specific form they'll be delivered a job.
The tables are organized somewhat like this
tblQUEUE
PK_ID_Job(AUTO_NUMBER)| JOB_NUMBER(INT)|LOCKED(BOOL)
tblWorkOrders (FK_ID_JOB (INDEXED DUPLICATES ALLOWED))
FK_ID_Job(LONG)|Name(STR)|Date(DATE)|
Now it works like this, the user gets an ID_JOB from queue
In the form they get all the all the work orders with that ID_JOB, the thing is i'm getting users with same duplicate orders cause i can't update the locked efficiently.
Regarding the users, the database is split, multi-user, with >30 simultaneously
I'm been trying to use dao.recordset, with transactions to try and reduce the duplicate orders.
Tables :: Autonumber / Customer Reference Field?
Sep 30, 2014use an account reference from one field in a table on a second table and add a number after it... e.g.
Table 1 Table 2
PEFA PEFA/001
PEFA PEFA/002
PEFA PEFA/003
PEFA PEFA/004
and so on...
Modules & VBA :: List Of Orders - How To Keep New Types In Same Table
Oct 26, 2013I have a query which is my list of orders. Now I want to create other type of orders. New types don't use the code in red (tbl Offset and tblRodzajZlecenia). Is it possible to modify the query, in order to keep new types too?
New types of orders are in the same tables. But if I don't fill fields in tblOffset then query can't show new type of oder - that's the problem.
Code:
SELECT z.ID_Zlecenia AS ID, tblRodzajPracy.RodzajPracy AS [Rodzaj pracy], tblRodzajZlecenia.RodzajZlecenia AS [Rodzaj zlecenia], z.NazwaZlecenia AS [Nazwa Zlecenia], k.NazwaSkrocona AS Klient, z.DataPrzyjecia AS [Data Przyjęcia], z.TerminOdbioru AS Termin, tblStatus.Status, tblStatus.lp
FROM (tblKlienci AS k INNER JOIN ((tblStatus INNER JOIN tblZlecenia AS z ON tblStatus.Id_Status = z.Status)
[Code] .....
Forms :: Showing Sum Of Orders Table In The Form
Feb 26, 2014I have a database with 2 tables: tbl_Client and tbl_Orders
I would like to sum the total from the sold products and show them in the form in a text field called 'Total sales" the data from the table orders appear as a sub form in the main form Client.
It would be great to have for each client appear the total sales in the form, but I'm not able to find the formula for this so the table tbl_Orders field "TotalPrice" will be sumed.
Table Of Orders - Auto Fill A Column Based On Selection In Another
Jan 23, 2013In my database I have a table of customers and a table of orders (where many customers have more than one order, so the primary keys for each table are customerID and orderID).
In the orders table, the first columns are:
OrderID CustomerID Customer Forename Customer Surname
I need my table to auto fill in the customer forename and surname based on the selection of customerID (the names are saved in the customers table).
I already have a lot of data filled in, so am looking ideally for a way to fill these name columns without having to re-input all the data.
Using 2 Customer Ids In One Table
Nov 27, 2006i cant do this.. its too long..
basically i have a job table and a person table..
jobid, job date, job details....
personid, personname, person details...
the job table needs to store who BOOKED the job, and who was the PASSENGER in the job.
i want to use the person ids in the job table..
im having trouble here with the whole relationships and forms..
i need my user to be able to simultaneously add a booker and a customer to the person table and assign them both to a job and i dont know the best way to do it..
a few rules
job1 can be
booked by person 1
have passenger as person 1
job 1 can be
booked by person 1
have passenger as person 2
also the booker and/or passenger may be unknown.. which is why i want to create an unknown person in the person table.. with id 1 for example.. and anytime the booker or passenger isnt known my user can just type in id 1
someone help me.