Query Based On The Higher MTD Sales

May 29, 2006

Hello,

I need to write a query based on the higher MTD sales in the series of each fabrics within series of Sales Group and Prod Group . All info is from one table

Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales

Sales Gr: Active
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: a,b,c,d,e,f...
StyleNum: 1,2,3,4,5,6...(PK)

Sales Gr: Dance
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: a,b,c,d,e,f...
StyleNum: 1,2,3,4,5,6......(PK)

Sales Gr: Yoga
Prod gr: Adult, Girls, Plus
Fabric Gr: a,b,c,d,e,f...
StyleNum: 1,2,3,4,5,6......(PK)

Thank you

View Replies


ADVERTISEMENT

Extract Sales Data Based On Another Table

Nov 10, 2007

Hi All experts,

I got two tables while one table contains (sales data) and another one contains (criteria). I would like to extract sales data based on the criteria tables and export to a new table.

Which method is the best to complete this?

Criteria contains many lines like this

CustomerID, ProductID & InvoiceDt
A, Guliter, 2007/10/5-2007/11/7
B, Piano, 2006/7/1-2006/12/31

Thanks in advance!
Regards,
Stanwell

View 2 Replies View Related

How Can I Create A Sales Form That Can Update Both The Product And Sales Tables

Oct 11, 2007

Am creating a Product-Sales Database, and I would like the corresponding Sales made in the Sale Table to be automatically deducted or to be reflected in the Product Table. The product table contains all my stock and has a relationship with the Sales Table. The Sale Table does not necessarily include the Stock. How can I create possibly a Sales Form that will be used as an entry point for all the products (stock) sold and automatically register the sold products in the Sales Table and at the same time make the required adjustments in the Products Table.

View 1 Replies View Related

The Higher The Database Size, The Slower The Query Process?

Jul 10, 2007

I am developing a database and testing it with dummy data. With 50,000 records, the query process using SQL command is just OK. But if I double the total records, the query process takes longer time. The query is to make recapitulation using SUM of SQL command based on certain criteria. Any idea to make the query faster?

Thanks.

View 2 Replies View Related

Forms :: Retrieve Info From Another Query - Higher Column Numbers?

Aug 22, 2014

When I attempt to use the me.field = me.combobox.column() method to retrieve info from another query,it only works if I use column 0 or 1... it doesn't work with higher columns number.

- field name: cbofi
- row source: listado-unico-fi <-- it is a query with unique values and 7 fields in total
- event procedure, change:

Private Sub cbofi_Change()
Me.txtDOCTPT = Me.cboFI.Column(1)
Me.campovia = Me.cboFI.Column(5)
End Sub

- txtDOCTPT is the field that indeed works
- campovia is the one that does not work

I tried using column(0) and column(1) and it worked fine... but higher column numbers do not work. Just mentioning because the field names are all right.

View 3 Replies View Related

Forms :: Create A Production Forecast Form Based On Previous Sales History?

Jul 19, 2014

I need to create a production forecast form based on previous sales history.The history is based a sales and grouped by month & year

So on the form, which needs to be a continuous form, I want products to show as rows and months as columns The sales history per month needs to be displayed as well as a field allowing to user to enter the production forecast.

I can write the sales history to a temp table.However I never know how many months history the user is going to want displayed at run time. Could be 3, 7, 12 or 15!

Attached spreadsheet shows what I am trying to achieve. Is this possible and if yes, how would I do it?

View 6 Replies View Related

Query Sales Anlaysis

Jan 16, 2006

Good afternoon

I have two tables - sales 2004 and 2005 - both showing account number, product, quantity, sales and margin. When I link account number and try to show both years the results are incorrect.....I know I am doing something wrong!! I have tried creating new table with blank fields for 2005 basing the table on 2004 and then updating 2005 - but no go.....

Help please

Many thanks

Catherine

View 4 Replies View Related

Sales Transaction Query

Mar 17, 2006

hi there...

I have been looking around the forums for a little while now... and cannot seem to find anything which will help me.... heres my query..

I have a stock control database.. which allows for sales transactions via a barcode reader...

my first query is.... when the user scans a product.. how can this be 'looked up' in the (UPC data table)... returning the correct information.. ie.. description.. but then taking the price from the product table.. the query doesnt seem to let me be able to join the two tables.. no idea why!?

secondly.. I want to create a form.. for the 'sales transaction'.. when the user clicks 'pay' i want the form to clear and store all these items in the sales table.. (deducting the units in stock at the same time..)

I hope anyone has any suggestions.. or pointers! Many Thanks
:)

View 7 Replies View Related

Crosstab Quarterly Sales Query

Mar 3, 2007

I have created a cross tab quarterly query to sjow the 4 quarters sales.

Problem is if there is no sales in one of the quaerters, the report based on that query gives an error as per attached photo and do not open.

any work around

View 1 Replies View Related

Combine Monthly Product Sales Query

Feb 27, 2008

I was wondering if there is a way to combine sales by month for a year where it would show the product then for say January and the total sales and so on for each month.

View 7 Replies View Related

Query/Update Sales Values For Individual Staff

Oct 16, 2006

Hey, I am currently doing an A-level ICT project and seem to be stuck at the first hurdel. My problem being that i wish to update the 'Number of sales' field for all the staff in the 'Staff' table. I have tried serveral queries to count the number of sales, which are in the 'Tickets' table for each and indidual 'Staff ID'.
This was suscuessful although i am wondering how to transfer these values into the Staff.[Number of Sales]. Also some staff had the value of 0 so did not come up in results of the querie.
Is there any possible solution or is there a more simple way to solve the problem.
Many Thanks in advance Dan Parker

View 1 Replies View Related

Queries :: Calculating Fiscal Year Sales In A Query

Feb 18, 2015

I'm making a query that has the following Fields

Product (Table PDZRN1)
Whse (Table PDZRN1)
Description (Table PDZRN1)
On Hand (Table PDZRN1)
Unit Cost (Table PDZRN1)
Unit Price (Table PDZRN1)
LastDate: Date (Table Sales_History) (using the Max function)

Where I am stuck is I want 4 additional columns to pull the last 3 fiscal years and total sales for each Product for the 3 previous fiscal years. The Field with sales amount is "Sales" it is located in Table "Sales_History", and the dates for the invoices are in the field "Date". I tried the following query, but I'm sure it didn't work because I failed to push the data back to the date field:

Field - FY2013Sales: Sales
Table - Sales_History
Total - Sum
Criteria - <= 04/01/2012 and <= 03/31/2013

how to link my criteria back to the "Date" field, or if I'm even going down the write path.

View 14 Replies View Related

Queries :: Table Of End Of Week Sales - Query Where There Is No Match

May 4, 2014

I have a table of end of week sales with ProductID, Volume_Sold, Year and WeekNo. I am about to create a historical table of RRP.

What is the best way to set this out so that I can query the two tables to that when I run a query over the two tables I get the correct price depending on the year and week number I am working with.

tblSales
Year int,
Week int,
CountryCode nvarchar (2),
StoreNo, nvarchar (35),
ProductId nvarchar (15),
Volume_Sold int;

My new table
"tblRRP"
Could contain
Year int,
Week int,
CountryCode nvarchar (2),
ProductId nvarchar (15),
RRP float;

The table is only appended to when the price changes. So some products may have a price increase 2 or 3 times a year others once every 18 months. And if the price changes any calculations need to allow for the 2 or 3 different RRPs the Product may have had during the queried period.

So that when I do year on year revenue calculations it works properly.

View 6 Replies View Related

Choose Higher Value

Aug 8, 2005

Hi,

Is there a way to automatically choose a value such as 0 if the expression goes to a negative number?

E.g. I have a query that will subtract a numeric field from 10. If 10 - [numericfield] is less than 0, then I only want it to display 0. Otherwise, it can display the value if it is positive.

Any help would be much appreciated.

View 3 Replies View Related

Starting Autonumber Higher Than 0

May 18, 2005

Hi,

I want to use an incremental number as a reference for records in a database. No probs, I use an autonumber. But the user wants the reference to start from 275.

Does anybody know how to start an autonumber off from a higher number (eg 275), without first creating and deleting 274 records. Is there an easy way of doing it.

Or should I not be using autonumber for this, but rather some other wizzy method which somebody is prepared to show me.

Thanks,
Adam.

View 3 Replies View Related

Sales Query Manufacture By Month - Total Column In Design View

Jan 2, 2014

I'm trying to create a simple sales query by manufacture by month. I want the month as the column headers (with a total YTD Column). Mfg's are the row headers. The query is already completed and I figured out how to do the row total but can't figure out how to create total column. Can I not add a total column in design view? How would I do that?

View 10 Replies View Related

Keep Access 2000 & Higher Out Of My 97 Database

Dec 6, 2006

My database is written in Access 97 (I know get out of the stone age) but we have a mix of office 97 and office 2003 users. Anyway I'm afraid that one of the few people that have access 2000 or higher will open my database and convert it. I found (through here) code to check the version but it is attached to a form opening which is way to late. Is there a way to have access check the version as soon as you click to open the database and if it isn't 97 close the database?

View 4 Replies View Related

Queries :: Get Next Higher Value OR If Result Empty

Feb 28, 2014

I'm trying to create a query which returns me the next higher number..My table: tblPersons

Code:
ID, Name, balance,
10 John 1000
11 Alice 2000
12 Bob 3000

My query:

Code:
SELECT TOP 1 name FROM tblPersons WHERE( (balance)>([InputBalance])) ORDER BY balance ASC

Input:
[InputBalance] = 500
Result: John

[InputBalance] = 1234
Result: Alice

[InputBalance] = 9999
Result: EMPTY
Result should be Bob

it's possible to combine this logic into one query?

View 4 Replies View Related

Queries :: Differentiate A Query Based On All Group Records Or A Query Based On Only One Record

Dec 22, 2014

I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?

View 14 Replies View Related

Modules & VBA :: Filter Subform Data - Only Show Records Where Field A Is Higher Value Than Field B

Oct 24, 2013

I want to filter my subform data, to only show records where field A is a higher value than field B.

Code:
Me.MySubform.Form.Filter = "A > B"
Me.MySubform.Form.FilterOn = True

This way it doesn't find field B.

Code:
Me.MySubform.Form.Filter = "A > " & MySubform.Form!B
Me.MySubform.Form.FilterOn = True

This way it seems to filter all record to the field B value of the first record.

View 5 Replies View Related

Queries :: List Of Dates And Records With No Matching Record OR Existing Record With Higher Date

Nov 24, 2014

I've been asked to get some information from my database and I'm a bit stuck.

I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field

My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.

So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)

I can manage a query that looks at a certain date that it prompts for on each run:

Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));

I would like a query that lists all dates in a range, and shows the same information for each day listed.

View 9 Replies View Related

Queries :: Query To Run Before Report Based On Criteria Based From Two Combo Boxes On Form

Mar 20, 2013

I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.

View 3 Replies View Related

Cash Sales Vs Invoices

Mar 25, 2007

I'm creating a database where I have a customers and invoices table, obviously there is a relationship between the two tables using a customerID field where I have referential integrity enabled. However not all invoices require a customer account as some are cash sales where no customer details are required.

I'm unsure of the correct procedure, all I can think of is to create a separate table for cash sales??

View 3 Replies View Related

Querry About Sales Commisions

Oct 1, 2005

Hi There
I Need A Querry For Commission Sales.
I Have 10 Items 7 Items At 2% Of Commission 3 At 4%. Each Salesman Have To reach A Min Of 5000 USD Of Sales Per Month Then All Sales Above That Amount Commission Will Be Calculated.
I Need A Querry To Calculate Sales And If Salesman reached The Min Sales Commission Is Calculated .

Any Help ???????
Thanks

View 1 Replies View Related

Calculating MTD And YTD Sales For Report

Dec 1, 2005

I have been stuck on this one for a while. I have an Access 2003 Database that I need to calculate the MTD sales and the YTD sales of parts ordered

Here are the relevant tables and thier attributes
Part -
PartNum
Description
UnitPrice
OnHand
ReorderPoint

Orders -
OrderNum
OrderDate
CustomerNum
CustomerPONum

OrderDetail -
PartNum
NumOrdered (Quantity)
QuotedPrice
NumShipped

If I have left anything off that may be vital please ask and I will supply the info. I need this info ASAP though.
I think the answer has something to do with the Date() and CurrentDate() but I have no clue. Please let me first know the queries I need to run and that should be it. I just need to put MTD and YTD Sales in a report.

I REALIZED THIS SHOULD BE A QUERY THIS WILL BE POSTED IN QUERY INSTEAD

View 10 Replies View Related

Negative Sales Goals

Nov 30, 2006

Sounds weird huh?

I have a salesman that has a negative "Net Flow" goal (sales goal). Others have positive goals.

His YTD Net Flows (Sales) is say...$1062 (million) and his goal is "-266 million"
Of course he is doing quite well, when I use the following code to get a % to goal I get "0" whenever there is a negative goal involved.

% to Goal: IIf([NetFlowsGoal]>0,([Total Net Flows]/[NetFlowsGoal]),0)

But it works fine if all numbers are positive as you might guess.

Any pointers would be great...Thank you

View 4 Replies View Related







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