Side by Side data in from 1

[jpl458]

Windows 7, Office 10, SQL Server
Data is in SQL Server DB table, and looks like this:
Account
Location
Date
Time
Quantity
There are fifty locations,(about 700,000 rows the data is updated throughout the day.
I need to create a form that displays two columns of information for todays data that looks like this:

Location Qyantity Location Quantity
Loc01 14 25 32
Loc02 12 26 18
.
.
.
Etc
Location name is in Location and so forth.
There is no stock form that displays data in this format. I first thought of trying to use two queries, first sums the quantities for the locations and the second would populate the form. But it (the second) only populates the first text box with the info re Location 01.

Next I thought of using two forms side by side, each with 25 rows, but decided that was bogus, that there should be a way of doing this.

Any Help would be appreciated.

Thanks in advance.

jpl

 

[jpl458]

The form should look like this

Location Quantity Location Quantity
Loc01 12 Loc26 31
Loc02 6 Loc27 16
.
.
.
Etc
Fatfingered first try.
jpl

 

[MajP]

You could do this either with a query or with two subforms side by side. If the locations are truly 1-50 then you could build a query that joins location to location+25. Or using two side by side subforms the rowsource for the 1st would return the first 25 and the second the remaining 25.

 

[jpl458]

Could you give me an example of the join query? I don't see how that works. I like that solution because ap will be in runn time and it has to shae part of a screen with another ap. That's why I need the two up format.

Thanks

jpl

 

[MajP]

Not sure how you data is set up, but I assume you can do a query to return the locations and quantities. Also you should have a field to store the location number. I assume you have a location table that holds information on the 50 locations. So in that table you should have a field for location name and location number

ex: locID LocationNumber
Loc01 1
...
Loc50 50

If not, sorting and joining on a text field will be cumbersome.

I assume you can then build something like this

ID LocID  LocationNumber  Quantity
1  Loc01  1               12
2  Loc02  2               6
3  Loc03  3               4
4  Loc04  4               8
5  Loc05  5               15
...
6  Loc26  26              13
7  Loc27  27              19
8  Loc28  28              11
9  Loc29  29              7
10 Loc30  30              5
...
50 Loc50  50              4

so

SELECT 
 LeftSide.LocID, 
 LeftSide.Quantity, 
 RightSide.LocID, 
 RightSide.Quantity
FROM 
 qryOne AS LeftSide, 
 qryOne AS RightSide
WHERE 
 RightSide.LocationNumber = ([LeftSide]![LocationNumber]+25);

gets this

Loc01	12  Loc26  13
Loc02	6   Loc27  19
Loc03	4   Loc28  11
Loc04	8   Loc29  7
Loc05	15  Loc30  5

 

[jpl458]

Never would have come up with that query, and will have to play with it some. The data is all in one SQL Server table, and there is no Location table but building that is a gnit. The data is stored in records as described in my first post I'll Try both solution this weekend. (Might be posting questions re the query.) Thanks for the effort, really appreciat it.

Thanks again

jpl

 

[MajP]

There are several other ways to do that, type of query. It could be done with subqueries or a join. Without knowing what the real data looks like it is hard know what would work. Especially if you have 700k records it will have to be efficient.

For example you can instead with a join

SELECT 
 LeftSide.LocID, 
 LeftSide.Quantity, 
 RightSide.LocID, 
 RightSide.Quantity
FROM qryOne 
 AS LeftSide 
INNER JOIN 
 qryOne AS RightSide 
ON 
 LeftSide.LocationNumber = (RightSide.LocationNumber + 25);

I think this is actually far more efficient than the where.

 

[jpl458]

Got the two up query working, it's great. Will try the second version to see if it is a bit faster.

Thanks so much for your time.

jpl