Order Data Table Help

Aug 9, 2007

Hello All,
I am new to access so this might be a stupid question but here goes.

I am making a new database for my orders (which I download from my eCommerce software in the form of a CSV file).

The Problem I am having is that my Order numbers are unique and that is what identifies the orders, however in the table data each order that has more than 1 item is listed on 2 seperate table lines. All info on the rows are identacle until the Part # field and the price field.

How can I make access understand that these few lines of data with the same order # are actually the same order with multiple items on the order?

Thanks,
Eddie

View Replies


ADVERTISEMENT

Forms :: Order Form Pasting Data Into Table

Aug 20, 2013

Okay I have an Orders Form, which generates a SubTotal Price (ex VAT), VAT, Shipping & Freight Charge based on an IIF statment of being under a 50.00 order apply 20.00 charge. And finally a Order Total with it all added together. This works fine, however I cannot seem to find a way to post these prices in my Orders Table. When I click on Datasheet View for the form, all the details appear as they should.

View 3 Replies View Related

Modules & VBA :: How To Arrange Table Data Into Date Order

Dec 31, 2013

I have an Access table with transactions that were entered in random order. I need to recreate the table with those transactions entered in date order. I started by making a copy of the table (table structure only) then creating an Append Query sorted by the date field thinking that the records would be appended in the order created by the query. It did not work as I wanted. My next technique was to use VBA code to open a recordset that was created with the following SQL statement:

strSQL = "SELECT * FROM tablename ORDER BY datefield" & ";"
Set rst1 = db.OpenRecordset(strSQL)

The compile hung up, apparently not liking something about the SQL statement. I have done these things many times before during the last 15 years of using Access. I am about to shoot my computer.

what I am doing wrong?

View 4 Replies View Related

Forms :: Create A Data Entry Form To Order Table Using 2 Cascading Combo Boxes

Jan 31, 2014

What I am trying to do is create a data entry form to an "order table" using 2 cascading combo boxes. I have created a data entry form based on a query. I can't get the cascading combo boxes to work properly.Here is the code:

Private Sub Combo0_AfterUpdate()
Combo2.RowSource = "SELECT L2_ID,L4_Element_name,L5_Category FROM qry_ord WHERE L3_ID = Combo0.Value;"
Combo2.DefaultValue = [Combo2].[ItemData](0)
Command4.SetFocus
End Sub

[code]....

View 2 Replies View Related

Tables :: Make Calculated Field In One Table That Calculates Values From Another Table Linked By Order ID

May 13, 2015

I 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 Related

Export Data Order

Jun 19, 2007

Does anyone know what would cause records in a table to change their sequence? I have a table that I clear out. Then I append a header record. Then I append the detail records. The table always is populated correctly. But sometimes, when exported to a text file, the header ends up down amongst the detail records. What would cause this?

View 3 Replies View Related

Order Data On A Form

Feb 16, 2005

I want to order the data on a tabular form.

I realise I could do it by basing the form on a query like:

Select *
from table
order by field

But I have allready made andlaid out the form, after basing it just on the table. Is there a way I can now order the data on the form, after having created it?

View 2 Replies View Related

New Record/order From Old Data?

Mar 26, 2006

Before I start I must say I’m not a coder (not even slightly). I’m just trying to reverse engineer a little access database to speed up order processing. The guy who created it has left :(

It’s a customer order database and at the moment if there is repeat business it requires manually copying over data such as name, address etc. from their old order. Is there some way to create an order from a previous or (ideally) currently viewed order?

This is the code for the “Create new order” option:

Private Sub Command93_Click()
On Error GoTo Err_Command93_Click


DoCmd.GoToRecord , , acNewRec

Exit_Command93_Click:
Exit Sub

Err_Command93_Click:
MsgBox Err.Description
Resume Exit_Command93_Click
End Sub

Is there something I could add that would copy and transfer the currently viewed name/address to a new order instead of just a new blank one? Is it even possible to copy/transfer data from currently viewed text boxes to a new order? Maybe there is a better way to do it?

Thanks :)

View 1 Replies View Related

Automatically Generating Order Data?

Aug 2, 2005

Hi all,

I'm currently working on a simple inventory system that i keep trying to improve and making it much less simple :(

In my product table I have a min and max level. What I'm trying to achieve is to set up a form where i can select a Supplier, then hit a button (or another form of trigger) and it comes back with all the products for that supplier where we are low on stock ie

Product - Supplier - On Hand - Min - Max
Prod1 - Supp1 - 2 - 2 - 4
Prod2 - Supp1 - 4 - 2 - 4
Prod3 - Supp1 - 1 - 2 - 4

So if on my form I selected supplier Supp1 it would automatically fill in order detail for Prod1 & Prod 3 for 2 & 3 units respectively.

Now I'm pretty sure i could do this as a report as the formulas are relatively simple, however i want to pull the data on a form so it creates an actual order with order id etc.

Is there a way i can do this?

Any help would be appreciated, cheers

View 2 Replies View Related

Memo Fields - Order Of Data

Dec 6, 2007

Hello people.

Im just after a bit of advice please??

I have created an audit trail for data held on a form so that when a field is changed it adds a line of text to a memo field with the date of the change and the old and new field values. The problem I have is that each time something is changed the line of text is added beneath the existing text in the memo field. This means that for users to view the most recent change they have to scroll all the way down to the bottom of the memo field. Is there a "Quick" way of me having the most recent entry first.
I am using the vbnewline command when adding the text. A sample is shown below:

Dim Response As Integer
Dim AnotherItem As Integer
Dim sqlstr As String

Set Myform = Forms![DataDetail]
Producer = Forms![ListOfProducers(Existing Data Item)]![LstProducers].Column(1)

Response = MsgBox("Are you sure you wish to add a Producer?", vbYesNo, "Data Dictionary v1.0")
sqlstr = "INSERT INTO DataProducers ( Data_Id, Producer_Id ) SELECT Data.Data_Id, Producers.Producer_Id FROM Data, Producers WHERE (((Data.Data_Id)=[Forms]![DataDetail]![Data_ID]) AND ((Producers.Producer_Id)=[Forms]![ListOfProducers(Existing Data Item)]![LstProducers]));"
If Response = 6 Then
DoCmd.RunSQL sqlstr
Myform!LstHistory = Myform!LstHistory & vbNewLine & "Changes made on " & Now & ""
Myform!LstHistory = Myform!LstHistory & vbNewLine & "The Producer '" & Producer & "' was added to this data item"""
Myform!LstHistory = Myform!LstHistory & vbNewLine & "-----------------------------------------------------"
MsgBox "Producer has been successfully added.", vbInformation, "Data Dictionary v1.0"


Thanks folks...

View 2 Replies View Related

Sort (order) Data On Forms

Apr 12, 2005

Hello,

This is related to "Old_value, new_value" thread I posted yestarday (Module&VBA).

I have a problem i missed yestarday. The code it's vorking great, but it allways takes the showed new_value and makes it old_value for the new record, and this is a problem, because the showed record is not the last for that customer.
So, I was thinking that if I make a new Autonumber field in my table, I could sort descending the values and this way the input form will allways show me the last entered data for that person.
As I noticed from a long time ago, if I sort a table that allready has a form, the form won't show the records sorted. Why? It's necessary to make the form again? I tried to order the data on the form from Properties window ->Data->Order by, but the ordering is Ascending and I need it Descending!
Can enybody help me?

Thanx,
Attila

View 1 Replies View Related

Forms :: Order Of Data Refresh?

Apr 9, 2013

I have a graph on my form, an Update/Refresh button, and a textbox that display a calculted value.

The textboxes controlsource is "=GetMyValue()"

Now when I click the Update button on the form, I would like to have the graph to refresh first, and then the textbox should update its value by running the GetMyValue function. But it works the other way around.

I've even tried to reset and set the textbox controlsource in the click event but it still get's preference above the refresh of the graph.

View 5 Replies View Related

Exporting Data With A Custom Sort Order

Jun 7, 2007

Hi,

I have a Form which contains a datasheet and I have some code which allows users to custom sort this datasheet. Another table based on this datasheet is then exported as a csv file.
However, the data is obviously not exported in the same order as the datasheet and it is quite critical for the application to be a sucess that it is exported in the custom order chosen by the user.

I have done some re-search, searching groups, forums etc and found the solution of having a 'sort column' in the table the datasheet relates to, I can then use this in the query that exports the data. My only problem with this is I had to manually fill in this sort column in the datasheet, once I had decided upon my final sort order. Is there anyway to fill this column automatically based on a row's position in a datasheet?

Many thanks in advance

View 4 Replies View Related

Access Newbie Needs Help! With Charts And Data Order

Apr 6, 2008

Hi, okay completely new to access. I'm trying to create a chart based on a query with 1 record 8 fields. I can not figure out a way to rearrange the data into two fields 8 records.

The background is I have a hundred units, each with 8 wheels. I have a table with the following fields.
-Unit part number
-Diameter of wheel 1
-Diameter of wheel 2
-Diameter of wheel 3
-Diameter of wheel 4
-Diameter of wheel 5
-Diameter of wheel 6
-Diameter of wheel 7
-Diameter of wheel 8

So, the query tells me how many wheel diameters between a certain range, with the following fields:
- Diameter >530
- 530> Diameter >540
- 540> Diameter >550
- 550> Diameter >560
- 560> Diameter >570
- 570> Diameter >580
- 580> Diameter >590
- 590> Diameter >600

Now I want to plot the range on the x axis and # wheels in that range on the y axis of a bar chart.

Any suggestions on how I can achieve this? How do I create the diameter range as a record rather than a field?

Many thanks!!!

View 4 Replies View Related

Reports :: Report Not Displaying Data In Order?

May 17, 2013

I have a report that generates the position of certain items. When I produce this report it doesn't put the data in order. All associated data with that position is correct, it is just not in numerical order. The attached screenshot shows the issue I am having. In the position column it should read 1,2,3,4 but in some cases the positions are not in order.

View 2 Replies View Related

Queries :: Filtering Data - Results Need To Be In Specific Order

May 21, 2013

I'm taking my first database class and I'm working on a hospital project in which I need to determine which pair (one doctor and one nurse) has a decremental performance as weeks go on.

I have an "r" and "s" queries.

These are the fields:

r: [DOCID,NURSEID, WEEK, RESULT]
s: [WEEK, RESULT]

I would like to find what pair had a "Good" result in week 1, "Average" in week 2, and "Bad" in week 3. My problem is that the results need to be in this specific order.

The professor told us to use a formula and I got the solution after building 6 queries that involved union, difference, and cartesian.

View 4 Replies View Related

Order By Sum From Another Table

Dec 5, 2006

Hi All

I need to order a table of COMPANIES based on the sum of their LOGS in another table. This is what I have so far

Code:SELECT COMPANIES.COMPANY_NAME, COMPANIES.COMPANY_EMAIL, SUM(LOGS.LOG_REVENUE) AS TotalRevenue FROM (COMPANIES RIGHT JOIN LOGS ON LOGS.LOG_COMPANYID = COMPANIES.COMPANY_ID) GROUP BY TotalRevenue, COMPANIES.COMPANY_NAME, COMPANIES.COMPANY_EMAIL

But i get "No value given for one or more required parameters." in response to the 'TotalRevenue' aggregate field.

Any suggestions on how to accomplish this would be gratefully received.

Many thanks for your time

Nick

View 2 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

Forms :: Data Types Match And In Order But Getting Syntax Error

May 9, 2015

The Data Types match and they are in order. I get a syntax error.

PHP Code:
     
      strSQL = "INSERT INTO tblHour (Hours, WorkDate, SickDay, SickDte) VALUES (" _
   Me.AbsenceHrsID & ",#" & Format(Me.AbsenceDteTo, "yyyy-mm-dd") & "#,True,#" _
   Format(Me.AbsenceDteTo, "yyyy-mm-dd") & "#)"
CurrentDb.Execute strSQL, dbFailOnError 

View 4 Replies View Related

General :: Concatenate - Combine Dates Into One Cell To Have One Row Of Order Data

Feb 25, 2013

I have 2 gig of order data which has duplicates due to different invoice dates. How can I combine the dates into one cell so I only had one row of Order data.

i.e. order number, price, order date, invoice date

same order number, same price, same order date, different invoice date.

How do I combine the second line with the first so that I only have the data once.

View 10 Replies View Related

General :: Pull Data From SQL Server Based Off Of Order Number?

May 27, 2015

I am looking into re-creating something I have created in Microsoft Excel into Microsoft Access.What I do in the Excel spreadsheet, is when I type in an order number, various fields in the spreadsheet are populated using vlookups and ODBC's.

I wish to re-create this in Access but not sure how. I have a basic form, with various fields such as, order number, part number, description, quantity.What I have in my mind is that I would like to be able to type in an order number, then the rest of the fields would be populated by a query to a SQL Server database.i can see a way to do this using VBA but would rather look for a more direct way if possible.

View 2 Replies View Related

Tables :: Adding More Data In List Sorted In Numerical Order

Sep 27, 2012

I have a list of 22 soccer players with their weights sorted from heaviest to lightest in numerical order. I want to add further players and have the list reflect their standing each time it changes i.e. Col 1 Player ID, Col2 FirstName, Col 2 SecondName, Col 3 Mass:90 Kgs Col 4 Position In group: 1

A new player registers and is 95Kg.....he becomes #1 and the rest go down etc. Can this be done?

View 6 Replies View Related

Exporting Data In Excel Where Date Field Is In Ascending Order

Jun 20, 2013

I am trying to export a data in Excel where a date field is in ascending order

But on displaying i am getting in ascending order but the display is somthing like this:

13/05/2013
13/05/2013
05/10/2013
05/10/2013
05/08/2013
05/08/2013
05/07/2013
05/07/2013
05/06/2013
05/03/2013
05/03/2013
05/02/2013
05/02/2013
30/04/2013
30/04/2013

Date order is misplaced...

View 5 Replies View Related

Ascending E Descending Order Of A Table.

Jan 4, 2006

Happy new year everyone.

I have a table in which the data input comes from a dropdown list selected in a form combo box as:

contract signed
PIN issued
PDD issued
validation finished
Host country approval
EB approved
CER's issued.

I am designing a report in which I want to display the above list in Ascending or Descending order but as shown rather than alphabetically.
What I mean is no matter how the information is on the table the report showd display it in Ascending as contract signed, PIN issued, etc and in Descending as CER's issued, EB approved, Host country approval , etc.

Any ideas?

Thank you

dfuas

View 6 Replies View Related

Order By Weekday In Table And Form

Dec 27, 2013

I have a column in my table for day of the week (Monday, Tuesday, etc.). What can I enter in the "Order By" table-property to order the records by their chronological weekdays? Additionally, is there a similar type of action I can perform within a related form?

View 3 Replies View Related

Tables :: Autonumber Order In Existing Table

Jul 9, 2015

I 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 Related







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