Solved Loop de Loop (2 Viewers)

[Space Cowboy]

Good Afternoon Good People,

I am struggling with an idea and need your help
How do I lift a table from a DB and post it here so that you can see what I mean.
I can also lift two queries to show my attempts so far, hopefully so that you can provide any guidance that you think would help me.

 

[MajP]

Create sample dB and import table, post
Export to excel, post
Copy and paste
Screen shot of design view and data view post image
Zip and post

Pick one or more

 

[Space Cowboy]

thank you
majP

 

[MajP]

If small table, simply copy and paste works well. Usually only a few rows needed. The copy paste brings in the column names. The only issue is it may not be obvious what the field datatype are.

 

[Space Cowboy]

Hi Majp,
You must be psychic.
I have run into the first problem with sample DB.
The date has a time function added to it. When I filter by date only it does not pick anything up due to the time aspect of the field.
DOH.....

 

[Pat Hartman]

If you want time included, use Now(). If you want just the date, use Date() when populating date fields. Time is always included but midnight is assumed for Date().

 

[Space Cowboy]

its the Northwind "orders" table where all orders contain date and time stamp
I want to filter on date only and its messing me up.
I will create my own table I think

 

[cheekybuddha]

I want to filter on date only and its messing me up.

Use a more appropriate filter instead.

eg for all records that occurred on 1st June 2024:

SELECT
  *
FROM YourTable
WHERE DateField >= #2024-06-01#
  AND DateField < #2024-06-02#
;

 

[Space Cowboy]

I have made a new table.

Then adding together the prices on each orderID


Giving me these total for each order


I have then tried to order the day totals and within each order the Hi price and rank 1-------whatever



The first part has worked



Disclaimer ; I am no where near experienced enough to work this out, I had help from CJ and I think I have messed it up.

 

[Space Cowboy]

I did have it working By day, but when I try to include several days data ( with the same mini report needed for each day) I cannot guess the loops

 

[GPGeorge]

Review the previous posts, which contain the appropriate method to filter by Date when date/time values are stored with Date and a non-midnight time.

You cannot always SEE the time portion of a date/time value, but it is always there, so you have to account for it.

Both Pat and Dave addressed this so review their suggestions and ask specific questions about anything you don't understand.

 

[Space Cowboy]

Review the previous posts, which contain the appropriate method to filter by Date when date/time values are stored with Date and a non-midnight time.

You cannot always SEE the time portion of a date/time value, but it is always there, so you have to account for it.

Both Pat and Dave addressed this so review their suggestions and ask specific questions about anything you don't understand.

Hi George,

that is not the specific issue I have, the date format was just to come up with a way to present data for inspection

 

[Space Cowboy]

I have input 3 days data into a small table

 

[Space Cowboy]

This is what I am trying to achieve.

a loop (i think) through each date and perform the analysis then move to next date and perform same operation on that day
then move to next date.
I hope that makes sense

 

[GPGeorge]

This is what I am trying to achieve.

View attachment 114429

a loop (i think) through each date and perform the analysis then move to next date and perform same operation on that day
then move to next date.
I hope that makes sense

I see, you took the Northwind data and converted the date/time values to midnight on that date. And then, in post #9 you updated the question from date filtering to focus on something else other than the dates.

I'm a bit unclear how this gives you useful data. Are you looking for the highest Unit price for an product in each order, regardless of the number of units of any product? That's all this data can produce. And the sum of unit prices for products in an order seems like a fairly obscure data point. If you order 100 units of a product priced at $1.00 per unit, and 1 unit of a product priced at $100 per unit, your sum will return $101 as the sum of unit price. The order total, though, would be $200 altogether-- (1 X 100) + (100 X 1). What purpose does this calculation serve?

And ranking those orders by the sum of unit prices? What business purpose does that serve?

I was on the team that created the Northwind Developer (and Starter) version templates for Microsoft, so I'm reasonably familiar with the data, but the business purpose of this ranking on Sum of Unit Price wasn't one of the things we thought of. Help me understand the goal.

I think the ranking you want will need to be done in a couple of queries, no looping required, but it'll help to put this into some real world context in case I'm misunderstanding something.

 

[Space Cowboy]

HI George,
Thank you for trying to help.
I have used the northwind type data as a way to express my attempts at a soloution to the problem I have.
The field names are irrelevant as I can transpose the theory to the real world DB
In reality the "orderId" is the sales rep who worked on a particular day
The unit price total is the sales that the rep generated and then ranked.
I am seeking to analyze the sales total of a rep on any given day to see who is performing best and then ranking the sales of each item. The sales price will be broken down by profit margin. to help me identify the best margin products and to focus attention via bonuses to the reps on those products.

 

[Space Cowboy]

I'm a bit unclear how this gives you useful data. Are you looking for the highest Unit price for an product in each order, regardless of the number of units of any product? That's all this data can produce. And the sum of unit prices for products in an order seems like a fairly obscure data point.

Yes George you have hit the nail on the head. It is very useful to me.

And ranking those orders by the sum of unit prices? What business purpose does that serve?

I have tried to explain in previous post. there is lots of other data to be tagged on, this will be the basic sort.

 

[GPGeorge]

HI George,
Thank you for trying to help.
I have used the northwind type data as a way to express my attempts at a soloution to the problem I have.
The field names are irrelevant as I can transpose the theory to the real world DB
In reality the "orderId" is the sales rep who worked on a particular day
The unit price total is the sales that the rep generated and then ranked.
I am seeking to analyze the sales total of a rep on any given day to see who is performing best and then ranking the sales of each item. The sales price will be broken down by profit margin. to help me identify the best margin products and to focus attention via bonuses to the reps on those products.

Start over then.

Please provide real data from a real data source and show it in the real tables.

It's been my experience over the years that attempting to "simplify" by aliasing names and offering "similar but not really the same objects" obscures far more than it helps.

Thank you.

 

[cheekybuddha]

First, forget 'loops' - I think that is the wrong terminology, and is confusing everyone here.

Second, please post the SQL of the query that produces the output shown in post #14

The needed UnitPrice can very likely be sorted with a simple adjustment to the ORDER BY clause.

Once that is fixed, we can help you with the ranking.

 

[Space Cowboy]

Hi cheeky buddah,

Thank you.

I have to click the OK 10 times, once for each row.
Then the data is produced with "error"