468,101 Members | 1,449 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,101 developers. It's quick & easy.

Space Allocatiojn

I have a table of SpaceAreas eg Food Store, Garden Shed etc with the first
and last bin for each Space Area defined. eg Food Store First Space 1, last
space 26 and Gargen Shed First space 1, last Space 50.
SpaceAreas Table is
SpaceAreaID Auto
SpaceAreaDesc Text
FirstSpace Integer
LastSpace Integer

I have a secont table of the Equipment Stored in each space

Space Table is
SpaceID Auto
SpaceTypeID Long Points to the SpaceArea
SpaceNo Integer
EquipmentID Long Point to the equipment

No trouble printing a report of what is where, but I want a report of All
the locations ie
FoodStore 1 -
FoodStore 2 Butter
....
FoodStore 26
Garden Shed 1 -
Garden Shed 2 Mower
Garden Shed 3 -
....
Garden Shed 50 Fork
And what is in them. Many will be empty

I am trying to avoid populating the Space Table with blank data

Any thoughts please

Thanks

Phil
Apr 29 '07 #1
10 1745
In your query you use for the recordsource of the report, double click on
the relationship line between SpaceAreaID and SpaceTypeID and change it from
a Type 1 to a Type 2 or 3. Choose the one that says Include all the records
in SpaceAreas and only the related records in Space.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com
"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>I have a table of SpaceAreas eg Food Store, Garden Shed etc with the first
and last bin for each Space Area defined. eg Food Store First Space 1, last
space 26 and Gargen Shed First space 1, last Space 50.
SpaceAreas Table is
SpaceAreaID Auto
SpaceAreaDesc Text
FirstSpace Integer
LastSpace Integer

I have a secont table of the Equipment Stored in each space

Space Table is
SpaceID Auto
SpaceTypeID Long Points to the SpaceArea
SpaceNo Integer
EquipmentID Long Point to the equipment

No trouble printing a report of what is where, but I want a report of All
the locations ie
FoodStore 1 -
FoodStore 2 Butter
...
FoodStore 26
Garden Shed 1 -
Garden Shed 2 Mower
Garden Shed 3 -
...
Garden Shed 50 Fork
And what is in them. Many will be empty

I am trying to avoid populating the Space Table with blank data

Any thoughts please

Thanks

Phil

Apr 30 '07 #2
Thanks Steve.

The records in the Space table don't actually exist. I just want a blank
line on the report to show that they are available to put equipment in.

Phil
"Steve" <so***@private.emailaddresswrote in message
news:HZ*****************@newsread1.news.pas.earthl ink.net...
In your query you use for the recordsource of the report, double click on
the relationship line between SpaceAreaID and SpaceTypeID and change it
from a Type 1 to a Type 2 or 3. Choose the one that says Include all the
records in SpaceAreas and only the related records in Space.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com
"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>>I have a table of SpaceAreas eg Food Store, Garden Shed etc with the first
and last bin for each Space Area defined. eg Food Store First Space 1,
last space 26 and Gargen Shed First space 1, last Space 50.
SpaceAreas Table is
SpaceAreaID Auto
SpaceAreaDesc Text
FirstSpace Integer
LastSpace Integer

I have a secont table of the Equipment Stored in each space

Space Table is
SpaceID Auto
SpaceTypeID Long Points to the SpaceArea
SpaceNo Integer
EquipmentID Long Point to the equipment

No trouble printing a report of what is where, but I want a report of All
the locations ie
FoodStore 1 -
FoodStore 2 Butter
...
FoodStore 26
Garden Shed 1 -
Garden Shed 2 Mower
Garden Shed 3 -
...
Garden Shed 50 Fork
And what is in them. Many will be empty

I am trying to avoid populating the Space Table with blank data

Any thoughts please

Thanks

Phil


Apr 30 '07 #3
Phil,

Try the suggested query - you should get what you want. You should get a
list of all areas; where there is something in the area it will show in the
report and if the area is empty, the field for what is in that area will be
blank. Just like your example.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com


"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
Thanks Steve.

The records in the Space table don't actually exist. I just want a blank
line on the report to show that they are available to put equipment in.

Phil
"Steve" <so***@private.emailaddresswrote in message
news:HZ*****************@newsread1.news.pas.earthl ink.net...
>In your query you use for the recordsource of the report, double click on
the relationship line between SpaceAreaID and SpaceTypeID and change it
from a Type 1 to a Type 2 or 3. Choose the one that says Include all the
records in SpaceAreas and only the related records in Space.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com
"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>>>I have a table of SpaceAreas eg Food Store, Garden Shed etc with the
first and last bin for each Space Area defined. eg Food Store First Space
1, last space 26 and Gargen Shed First space 1, last Space 50.
SpaceAreas Table is
SpaceAreaID Auto
SpaceAreaDesc Text
FirstSpace Integer
LastSpace Integer

I have a secont table of the Equipment Stored in each space

Space Table is
SpaceID Auto
SpaceTypeID Long Points to the SpaceArea
SpaceNo Integer
EquipmentID Long Point to the equipment

No trouble printing a report of what is where, but I want a report of
All the locations ie
FoodStore 1 -
FoodStore 2 Butter
...
FoodStore 26
Garden Shed 1 -
Garden Shed 2 Mower
Garden Shed 3 -
...
Garden Shed 50 Fork
And what is in them. Many will be empty

I am trying to avoid populating the Space Table with blank data

Any thoughts please

Thanks

Phil



Apr 30 '07 #4
Thanks for coming back again, Steve.

Perhaps I haven't made it quite clear. In the Space table, I have only
created records where there is actually eqipment stored. So there are
actually only 3 records.
The report should show 3 records with those details and 26 + 50 - 3 blank
spaces ( 26 spaces from the Food Store + 50 from the Garden shed less the 3
records with details). It is then easy to see at a glance where there is a
free hole to store other items.

Thanks again

Phil
"Steve" <so***@private.emailaddresswrote in message
news:ea*****************@newsread1.news.pas.earthl ink.net...
Phil,

Try the suggested query - you should get what you want. You should get a
list of all areas; where there is something in the area it will show in
the report and if the area is empty, the field for what is in that area
will be blank. Just like your example.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com


"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>Thanks Steve.

The records in the Space table don't actually exist. I just want a blank
line on the report to show that they are available to put equipment in.

Phil
"Steve" <so***@private.emailaddresswrote in message
news:HZ*****************@newsread1.news.pas.earth link.net...
>>In your query you use for the recordsource of the report, double click
on the relationship line between SpaceAreaID and SpaceTypeID and change
it from a Type 1 to a Type 2 or 3. Choose the one that says Include all
the records in SpaceAreas and only the related records in Space.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com
"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
I have a table of SpaceAreas eg Food Store, Garden Shed etc with the
first and last bin for each Space Area defined. eg Food Store First
Space 1, last space 26 and Gargen Shed First space 1, last Space 50.
SpaceAreas Table is
SpaceAreaID Auto
SpaceAreaDesc Text
FirstSpace Integer
LastSpace Integer

I have a secont table of the Equipment Stored in each space

Space Table is
SpaceID Auto
SpaceTypeID Long Points to the SpaceArea
SpaceNo Integer
EquipmentID Long Point to the equipment

No trouble printing a report of what is where, but I want a report of
All the locations ie
FoodStore 1 -
FoodStore 2 Butter
...
FoodStore 26
Garden Shed 1 -
Garden Shed 2 Mower
Garden Shed 3 -
...
Garden Shed 50 Fork
And what is in them. Many will be empty

I am trying to avoid populating the Space Table with blank data

Any thoughts please

Thanks

Phil



May 1 '07 #5
Phil,

Try the query for the recordsource of your report. It will give you 79
spaces. 3 will show equipment in the space and 76 will be blank.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
Thanks for coming back again, Steve.

Perhaps I haven't made it quite clear. In the Space table, I have only
created records where there is actually eqipment stored. So there are
actually only 3 records.
The report should show 3 records with those details and 26 + 50 - 3 blank
spaces ( 26 spaces from the Food Store + 50 from the Garden shed less the
3 records with details). It is then easy to see at a glance where there is
a free hole to store other items.

Thanks again

Phil
"Steve" <so***@private.emailaddresswrote in message
news:ea*****************@newsread1.news.pas.earthl ink.net...
>Phil,

Try the suggested query - you should get what you want. You should get a
list of all areas; where there is something in the area it will show in
the report and if the area is empty, the field for what is in that area
will be blank. Just like your example.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com


"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>>Thanks Steve.

The records in the Space table don't actually exist. I just want a blank
line on the report to show that they are available to put equipment in.

Phil
"Steve" <so***@private.emailaddresswrote in message
news:HZ*****************@newsread1.news.pas.eart hlink.net...
In your query you use for the recordsource of the report, double click
on the relationship line between SpaceAreaID and SpaceTypeID and change
it from a Type 1 to a Type 2 or 3. Choose the one that says Include all
the records in SpaceAreas and only the related records in Space.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com
"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>I have a table of SpaceAreas eg Food Store, Garden Shed etc with the
>first and last bin for each Space Area defined. eg Food Store First
>Space 1, last space 26 and Gargen Shed First space 1, last Space 50.
SpaceAreas Table is
SpaceAreaID Auto
SpaceAreaDesc Text
FirstSpace Integer
LastSpace Integer
>
I have a secont table of the Equipment Stored in each space
>
Space Table is
SpaceID Auto
SpaceTypeID Long Points to the SpaceArea
SpaceNo Integer
EquipmentID Long Point to the equipment
>
No trouble printing a report of what is where, but I want a report of
All the locations ie
FoodStore 1 -
FoodStore 2 Butter
...
FoodStore 26
Garden Shed 1 -
Garden Shed 2 Mower
Garden Shed 3 -
...
Garden Shed 50 Fork
And what is in them. Many will be empty
>
I am trying to avoid populating the Space Table with blank data
>
Any thoughts please
>
Thanks
>
Phil
>




May 1 '07 #6
Honestly, Steve, it doesn't.

Just 3 records regardless of which way the join is done.
The query doesn't have any concept of what the FirstSpace and LastSpace are
used for. The difference between these 2 figures are the number of records
required.

Thanks again

Phil
"Steve" <so***@private.emailaddresswrote in message
news:2P******************@newsread3.news.pas.earth link.net...
Phil,

Try the query for the recordsource of your report. It will give you 79
spaces. 3 will show equipment in the space and 76 will be blank.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>Thanks for coming back again, Steve.

Perhaps I haven't made it quite clear. In the Space table, I have only
created records where there is actually eqipment stored. So there are
actually only 3 records.
The report should show 3 records with those details and 26 + 50 - 3 blank
spaces ( 26 spaces from the Food Store + 50 from the Garden shed less the
3 records with details). It is then easy to see at a glance where there
is a free hole to store other items.

Thanks again

Phil
"Steve" <so***@private.emailaddresswrote in message
news:ea*****************@newsread1.news.pas.earth link.net...
>>Phil,

Try the suggested query - you should get what you want. You should get a
list of all areas; where there is something in the area it will show in
the report and if the area is empty, the field for what is in that area
will be blank. Just like your example.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com


"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
Thanks Steve.

The records in the Space table don't actually exist. I just want a
blank line on the report to show that they are available to put
equipment in.

Phil
"Steve" <so***@private.emailaddresswrote in message
news:HZ*****************@newsread1.news.pas.ear thlink.net...
In your query you use for the recordsource of the report, double click
on the relationship line between SpaceAreaID and SpaceTypeID and
change it from a Type 1 to a Type 2 or 3. Choose the one that says
Include all the records in SpaceAreas and only the related records in
Space.
>
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com
>
>
"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>>I have a table of SpaceAreas eg Food Store, Garden Shed etc with the
>>first and last bin for each Space Area defined. eg Food Store First
>>Space 1, last space 26 and Gargen Shed First space 1, last Space 50.
>SpaceAreas Table is
>SpaceAreaID Auto
>SpaceAreaDesc Text
>FirstSpace Integer
>LastSpace Integer
>>
>I have a secont table of the Equipment Stored in each space
>>
>Space Table is
>SpaceID Auto
>SpaceTypeID Long Points to the SpaceArea
>SpaceNo Integer
>EquipmentID Long Point to the equipment
>>
>No trouble printing a report of what is where, but I want a report of
>All the locations ie
>FoodStore 1 -
>FoodStore 2 Butter
>...
>FoodStore 26
>Garden Shed 1 -
>Garden Shed 2 Mower
>Garden Shed 3 -
>...
>Garden Shed 50 Fork
>And what is in them. Many will be empty
>>
>I am trying to avoid populating the Space Table with blank data
>>
>Any thoughts please
>>
>Thanks
>>
>Phil
>>
>
>




May 1 '07 #7
Phil,

I relooked at your original post and would suggest a different table
structure. You can then get the report you want.

TblSpaceAreas
SpaceAreaID Auto
SpaceAreaDesc Text

TblSpaceAreaNumbers
SpaceAreaNumberID Auto
SpaceAreaID Long Integer Associates a space area number with a space
area
SpaceAreaNumber Integer Numbers each spot in each area

TblEquipmentInSpaceAreaNumber
EquipmentInSpaceAreaNumber Auto
SpaceAreaNumberID Long Integer Associates an equipment item with a
specific space and a specific space area number
EquipmentID Long Integer

TblEquipMent
EquipmentID Auto
EquipmentDesc Text

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com


"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
Honestly, Steve, it doesn't.

Just 3 records regardless of which way the join is done.
The query doesn't have any concept of what the FirstSpace and LastSpace
are used for. The difference between these 2 figures are the number of
records required.

Thanks again

Phil
"Steve" <so***@private.emailaddresswrote in message
news:2P******************@newsread3.news.pas.earth link.net...
>Phil,

Try the query for the recordsource of your report. It will give you 79
spaces. 3 will show equipment in the space and 76 will be blank.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>>Thanks for coming back again, Steve.

Perhaps I haven't made it quite clear. In the Space table, I have only
created records where there is actually eqipment stored. So there are
actually only 3 records.
The report should show 3 records with those details and 26 + 50 - 3
blank spaces ( 26 spaces from the Food Store + 50 from the Garden shed
less the 3 records with details). It is then easy to see at a glance
where there is a free hole to store other items.

Thanks again

Phil
"Steve" <so***@private.emailaddresswrote in message
news:ea*****************@newsread1.news.pas.eart hlink.net...
Phil,

Try the suggested query - you should get what you want. You should get
a list of all areas; where there is something in the area it will show
in the report and if the area is empty, the field for what is in that
area will be blank. Just like your example.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com


"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
Thanks Steve.
>
The records in the Space table don't actually exist. I just want a
blank line on the report to show that they are available to put
equipment in.
>
Phil
>
>
"Steve" <so***@private.emailaddresswrote in message
news:HZ*****************@newsread1.news.pas.ea rthlink.net...
>In your query you use for the recordsource of the report, double
>click on the relationship line between SpaceAreaID and SpaceTypeID
>and change it from a Type 1 to a Type 2 or 3. Choose the one that
>says Include all the records in SpaceAreas and only the related
>records in Space.
>>
>PC Datasheet
>Providing Customers A Resource For Help With Access, Excel And Word
>Applications
>re******@pcdatasheet.com
>>
>>
>"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
>news:46**********************@ptn-nntp-reader02.plus.net...
>>>I have a table of SpaceAreas eg Food Store, Garden Shed etc with the
>>>first and last bin for each Space Area defined. eg Food Store First
>>>Space 1, last space 26 and Gargen Shed First space 1, last Space 50.
>>SpaceAreas Table is
>>SpaceAreaID Auto
>>SpaceAreaDesc Text
>>FirstSpace Integer
>>LastSpace Integer
>>>
>>I have a secont table of the Equipment Stored in each space
>>>
>>Space Table is
>>SpaceID Auto
>>SpaceTypeID Long Points to the SpaceArea
>>SpaceNo Integer
>>EquipmentID Long Point to the equipment
>>>
>>No trouble printing a report of what is where, but I want a report
>>of All the locations ie
>>FoodStore 1 -
>>FoodStore 2 Butter
>>...
>>FoodStore 26
>>Garden Shed 1 -
>>Garden Shed 2 Mower
>>Garden Shed 3 -
>>...
>>Garden Shed 50 Fork
>>And what is in them. Many will be empty
>>>
>>I am trying to avoid populating the Space Table with blank data
>>>
>>Any thoughts please
>>>
>>Thanks
>>>
>>Phil
>>>
>>
>>
>
>




May 2 '07 #8
Thanks Steve

I think you are right. Was hoping to avoid this solution as I have about 44
tables, 160 queries, 40 odd forms and a similar number or reports etc miles
of code (Front end DB is over 12MB) and I am dreading having to go through
everything to see what is using the space information

A few more sleepless nights

Thanks

Phil

"Steve" <so***@private.emailaddresswrote in message
news:IB****************@newsread1.news.pas.earthli nk.net...
Phil,

I relooked at your original post and would suggest a different table
structure. You can then get the report you want.

TblSpaceAreas
SpaceAreaID Auto
SpaceAreaDesc Text

TblSpaceAreaNumbers
SpaceAreaNumberID Auto
SpaceAreaID Long Integer Associates a space area number with a space
area
SpaceAreaNumber Integer Numbers each spot in each area

TblEquipmentInSpaceAreaNumber
EquipmentInSpaceAreaNumber Auto
SpaceAreaNumberID Long Integer Associates an equipment item with a
specific space and a specific space area number
EquipmentID Long Integer

TblEquipMent
EquipmentID Auto
EquipmentDesc Text

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com


"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>Honestly, Steve, it doesn't.

Just 3 records regardless of which way the join is done.
The query doesn't have any concept of what the FirstSpace and LastSpace
are used for. The difference between these 2 figures are the number of
records required.

Thanks again

Phil
"Steve" <so***@private.emailaddresswrote in message
news:2P******************@newsread3.news.pas.eart hlink.net...
>>Phil,

Try the query for the recordsource of your report. It will give you 79
spaces. 3 will show equipment in the space and 76 will be blank.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
Thanks for coming back again, Steve.

Perhaps I haven't made it quite clear. In the Space table, I have only
created records where there is actually eqipment stored. So there are
actually only 3 records.
The report should show 3 records with those details and 26 + 50 - 3
blank spaces ( 26 spaces from the Food Store + 50 from the Garden shed
less the 3 records with details). It is then easy to see at a glance
where there is a free hole to store other items.

Thanks again

Phil
"Steve" <so***@private.emailaddresswrote in message
news:ea*****************@newsread1.news.pas.ear thlink.net...
Phil,
>
Try the suggested query - you should get what you want. You should get
a list of all areas; where there is something in the area it will show
in the report and if the area is empty, the field for what is in that
area will be blank. Just like your example.
>
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com
>
>
>
>
"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>Thanks Steve.
>>
>The records in the Space table don't actually exist. I just want a
>blank line on the report to show that they are available to put
>equipment in.
>>
>Phil
>>
>>
>"Steve" <so***@private.emailaddresswrote in message
>news:HZ*****************@newsread1.news.pas.e arthlink.net...
>>In your query you use for the recordsource of the report, double
>>click on the relationship line between SpaceAreaID and SpaceTypeID
>>and change it from a Type 1 to a Type 2 or 3. Choose the one that
>>says Include all the records in SpaceAreas and only the related
>>records in Space.
>>>
>>PC Datasheet
>>Providing Customers A Resource For Help With Access, Excel And Word
>>Applications
>>re******@pcdatasheet.com
>>>
>>>
>>"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
>>news:46**********************@ptn-nntp-reader02.plus.net...
>>>>I have a table of SpaceAreas eg Food Store, Garden Shed etc with the
>>>>first and last bin for each Space Area defined. eg Food Store First
>>>>Space 1, last space 26 and Gargen Shed First space 1, last Space 50.
>>>SpaceAreas Table is
>>>SpaceAreaID Auto
>>>SpaceAreaDesc Text
>>>FirstSpace Integer
>>>LastSpace Integer
>>>>
>>>I have a secont table of the Equipment Stored in each space
>>>>
>>>Space Table is
>>>SpaceID Auto
>>>SpaceTypeID Long Points to the SpaceArea
>>>SpaceNo Integer
>>>EquipmentID Long Point to the equipment
>>>>
>>>No trouble printing a report of what is where, but I want a report
>>>of All the locations ie
>>>FoodStore 1 -
>>>FoodStore 2 Butter
>>>...
>>>FoodStore 26
>>>Garden Shed 1 -
>>>Garden Shed 2 Mower
>>>Garden Shed 3 -
>>>...
>>>Garden Shed 50 Fork
>>>And what is in them. Many will be empty
>>>>
>>>I am trying to avoid populating the Space Table with blank data
>>>>
>>>Any thoughts please
>>>>
>>>Thanks
>>>>
>>>Phil
>>>>
>>>
>>>
>>
>>
>
>




May 2 '07 #9
Phil,

Below is something you might be able to use. You would need to work wih your
Space Areas table and this technique to create a list of each area and all
the numbered spaces in each area using a query. You would have the criteria
of space area number as Between FirstSpace and LastSpace. Once you have this
list, you would then create another query that included the above query and
your Space table. In that query you would join SpaceTypeID with SpaceAreaID
and SpaceNo with the field in the first query that gives you the number of
each space in each area. The joins would have to be set to display all the
records in the first query and only the related records in your Space table.

Steve
Use A Table Of Numbers To Solve Many Difficult Problems

In many of my databases I keep a 'utility' table called 'tblNums' which has
one integer field 'num'. It is merely a table of Numbers in sequence from 0
to some arbitrarily
large number N where N is the largest number I think I'll reasonably need to
use in the database. Let's say for something like a scheduling Database
might use N=365 which
will cover days for a year, weeks for 7 years, and whatever. Possibly you
could get away with less (52 or so might do the trick - depends on the
intervals and how far ahead you want to schedule). So anyway (0, 1, .....
365 = 366 Records for leap years). Once you get used to the idea, you also
get used to the idea of checking query parameters at runtime, etc to verify
you have enough numbers and adding them as necessary. The table is simple to
create using VBA and never needs to be altered unless you want to add more
numbers.

Now for a query which takes a paramStartDate and returns Rows, up to
paramEndDate, for successive Weeks.

SELECT [paramStartDate] + (tblNums.num * 7)
FROM tblNums
WHERE [paramStartDate]+ (tblNums.num *7) <= [paramEndDate]

Note you could have used DateAdd("ww",tblNums.num ,[paramStartDate]) instead
of [paramStartDate] + (tblNums.num * 7). I generally prefer the former. But
wait, there's more.

Same idea for Monthlies to show [paramNumMonths]

SELECT DateAdd("m",tblNums.num,[paramStartDate])
FROM tblNums
WHERE tblNums.num < [paramNumMonths]

To get the Weekly example to Start of a Monday, just make sure that
[paramStartDate] is on a Monday.

For a more 'generic' approach, the following uses 4 Parameters...
paramStartDate ' First Event
paramEndDate ' Latest an event can recurr
paramAddType ' What Frequency - must correspond to a
valid DateAdd() 'interval' argument
paramAddMult ' Multiplier for AddType

ParamAddType And paramAddMult work together
"ww" & 1 = Every Week
"ww" & 2 = Bi-Weekly
"m" & 2 = Every 2 Months

SELECT DateAdd([paramAddType],tblNums.num * paramAddMult,[paramStartDate])
FROM tblNums
WHERE DateAdd([paramAddType],tblNums.num *
paramAddMult,[paramStarDate])<=[paramEndDate]

For that matter, assuming a table with....

tblEvents
EventName
FirstDate
EndDate
AddType
AddMult
You can generate a schedule as follows:

SELECT EventName, DateAdd(AddType,num * AddMult,FirstDate)
FROM tblEvents, tblNums
WHERE DateAdd(AddType,num * AddMult,FirstDate)<=EndDate

Obviouly this can only use intervals (+multiples) supported by DateAdd() but
that covers all but a small range of common intervals (i.e. it will not do
4th thursday of every month).

You can build Append Queries with the above technique as well.
Creating The Table Of Numbers

You can use a table of numbers "tblNumbers" and a field "Num" with records
0-9, you can build a query of all numbers. For instance if you want numbers
0 -999, use a query:
SELECT [tblNumbers]![Num]*100+
[tblNumbers_1]![Num]*10+
[tblNumbers_2]![Num] AS Expr1
FROM tblNumbers,
tblNumbers AS tblNumbers_1,
tblNumbers AS tblNumbers_2
ORDER BY [tblNumbers]![Num]*100+
[tblNumbers_1]![Num]*10+
[tblNumbers_2]![Num];
If you add 37257 to this column, you will have dates from 1/1/2002 to
9/26/2004. Add another copy of the same table and you can have 9,999 records
and dates to 5/18/2029.


"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
Thanks Steve

I think you are right. Was hoping to avoid this solution as I have about
44 tables, 160 queries, 40 odd forms and a similar number or reports etc
miles of code (Front end DB is over 12MB) and I am dreading having to go
through everything to see what is using the space information

A few more sleepless nights

Thanks

Phil

"Steve" <so***@private.emailaddresswrote in message
news:IB****************@newsread1.news.pas.earthli nk.net...
>Phil,

I relooked at your original post and would suggest a different table
structure. You can then get the report you want.

TblSpaceAreas
SpaceAreaID Auto
SpaceAreaDesc Text

TblSpaceAreaNumbers
SpaceAreaNumberID Auto
SpaceAreaID Long Integer Associates a space area number with a
space area
SpaceAreaNumber Integer Numbers each spot in each area

TblEquipmentInSpaceAreaNumber
EquipmentInSpaceAreaNumber Auto
SpaceAreaNumberID Long Integer Associates an equipment item with a
specific space and a specific space area number
EquipmentID Long Integer

TblEquipMent
EquipmentID Auto
EquipmentDesc Text

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com


"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>>Honestly, Steve, it doesn't.

Just 3 records regardless of which way the join is done.
The query doesn't have any concept of what the FirstSpace and LastSpace
are used for. The difference between these 2 figures are the number of
records required.

Thanks again

Phil
"Steve" <so***@private.emailaddresswrote in message
news:2P******************@newsread3.news.pas.ear thlink.net...
Phil,

Try the query for the recordsource of your report. It will give you 79
spaces. 3 will show equipment in the space and 76 will be blank.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
Thanks for coming back again, Steve.
>
Perhaps I haven't made it quite clear. In the Space table, I have only
created records where there is actually eqipment stored. So there are
actually only 3 records.
The report should show 3 records with those details and 26 + 50 - 3
blank spaces ( 26 spaces from the Food Store + 50 from the Garden shed
less the 3 records with details). It is then easy to see at a glance
where there is a free hole to store other items.
>
Thanks again
>
Phil
>
>
"Steve" <so***@private.emailaddresswrote in message
news:ea*****************@newsread1.news.pas.ea rthlink.net...
>Phil,
>>
>Try the suggested query - you should get what you want. You should
>get a list of all areas; where there is something in the area it will
>show in the report and if the area is empty, the field for what is in
>that area will be blank. Just like your example.
>>
>PC Datasheet
>Providing Customers A Resource For Help With Access, Excel And Word
>Applications
>re******@pcdatasheet.com
>>
>>
>>
>>
>"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
>news:46**********************@ptn-nntp-reader02.plus.net...
>>Thanks Steve.
>>>
>>The records in the Space table don't actually exist. I just want a
>>blank line on the report to show that they are available to put
>>equipment in.
>>>
>>Phil
>>>
>>>
>>"Steve" <so***@private.emailaddresswrote in message
>>news:HZ*****************@newsread1.news.pas. earthlink.net...
>>>In your query you use for the recordsource of the report, double
>>>click on the relationship line between SpaceAreaID and SpaceTypeID
>>>and change it from a Type 1 to a Type 2 or 3. Choose the one that
>>>says Include all the records in SpaceAreas and only the related
>>>records in Space.
>>>>
>>>PC Datasheet
>>>Providing Customers A Resource For Help With Access, Excel And Word
>>>Applications
>>>re******@pcdatasheet.com
>>>>
>>>>
>>>"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
>>>news:46**********************@ptn-nntp-reader02.plus.net...
>>>>>I have a table of SpaceAreas eg Food Store, Garden Shed etc with
>>>>>the first and last bin for each Space Area defined. eg Food Store
>>>>>First Space 1, last space 26 and Gargen Shed First space 1, last
>>>>>Space 50.
>>>>SpaceAreas Table is
>>>>SpaceAreaID Auto
>>>>SpaceAreaDesc Text
>>>>FirstSpace Integer
>>>>LastSpace Integer
>>>>>
>>>>I have a secont table of the Equipment Stored in each space
>>>>>
>>>>Space Table is
>>>>SpaceID Auto
>>>>SpaceTypeID Long Points to the SpaceArea
>>>>SpaceNo Integer
>>>>EquipmentID Long Point to the equipment
>>>>>
>>>>No trouble printing a report of what is where, but I want a report
>>>>of All the locations ie
>>>>FoodStore 1 -
>>>>FoodStore 2 Butter
>>>>...
>>>>FoodStore 26
>>>>Garden Shed 1 -
>>>>Garden Shed 2 Mower
>>>>Garden Shed 3 -
>>>>...
>>>>Garden Shed 50 Fork
>>>>And what is in them. Many will be empty
>>>>>
>>>>I am trying to avoid populating the Space Table with blank data
>>>>>
>>>>Any thoughts please
>>>>>
>>>>Thanks
>>>>>
>>>>Phil
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>




May 2 '07 #10
Thanks Steve

Worked a treat. Useful tecnique, may solve a lot of other problems.

Many thanks again

Phil

"Steve" <so***@private.emailaddresswrote in message
news:%g******************@newsread3.news.pas.earth link.net...
Phil,

Below is something you might be able to use. You would need to work wih
your Space Areas table and this technique to create a list of each area
and all the numbered spaces in each area using a query. You would have the
criteria of space area number as Between FirstSpace and LastSpace. Once
you have this list, you would then create another query that included the
above query and your Space table. In that query you would join SpaceTypeID
with SpaceAreaID and SpaceNo with the field in the first query that gives
you the number of each space in each area. The joins would have to be set
to display all the records in the first query and only the related records
in your Space table.

Steve
Use A Table Of Numbers To Solve Many Difficult Problems

In many of my databases I keep a 'utility' table called 'tblNums' which
has one integer field 'num'. It is merely a table of Numbers in sequence
from 0 to some arbitrarily
large number N where N is the largest number I think I'll reasonably need
to use in the database. Let's say for something like a scheduling Database
might use N=365 which
will cover days for a year, weeks for 7 years, and whatever. Possibly you
could get away with less (52 or so might do the trick - depends on the
intervals and how far ahead you want to schedule). So anyway (0, 1, .....
365 = 366 Records for leap years). Once you get used to the idea, you also
get used to the idea of checking query parameters at runtime, etc to
verify you have enough numbers and adding them as necessary. The table is
simple to create using VBA and never needs to be altered unless you want
to add more numbers.

Now for a query which takes a paramStartDate and returns Rows, up to
paramEndDate, for successive Weeks.

SELECT [paramStartDate] + (tblNums.num * 7)
FROM tblNums
WHERE [paramStartDate]+ (tblNums.num *7) <= [paramEndDate]

Note you could have used DateAdd("ww",tblNums.num ,[paramStartDate])
instead of [paramStartDate] + (tblNums.num * 7). I generally prefer the
former. But wait, there's more.

Same idea for Monthlies to show [paramNumMonths]

SELECT DateAdd("m",tblNums.num,[paramStartDate])
FROM tblNums
WHERE tblNums.num < [paramNumMonths]

To get the Weekly example to Start of a Monday, just make sure that
[paramStartDate] is on a Monday.

For a more 'generic' approach, the following uses 4 Parameters...
paramStartDate ' First Event
paramEndDate ' Latest an event can recurr
paramAddType ' What Frequency - must correspond to a
valid DateAdd() 'interval' argument
paramAddMult ' Multiplier for AddType

ParamAddType And paramAddMult work together
"ww" & 1 = Every Week
"ww" & 2 = Bi-Weekly
"m" & 2 = Every 2 Months

SELECT DateAdd([paramAddType],tblNums.num * paramAddMult,[paramStartDate])
FROM tblNums
WHERE DateAdd([paramAddType],tblNums.num *
paramAddMult,[paramStarDate])<=[paramEndDate]

For that matter, assuming a table with....

tblEvents
EventName
FirstDate
EndDate
AddType
AddMult
You can generate a schedule as follows:

SELECT EventName, DateAdd(AddType,num * AddMult,FirstDate)
FROM tblEvents, tblNums
WHERE DateAdd(AddType,num * AddMult,FirstDate)<=EndDate

Obviouly this can only use intervals (+multiples) supported by DateAdd()
but that covers all but a small range of common intervals (i.e. it will
not do 4th thursday of every month).

You can build Append Queries with the above technique as well.
Creating The Table Of Numbers

You can use a table of numbers "tblNumbers" and a field "Num" with records
0-9, you can build a query of all numbers. For instance if you want
numbers
0 -999, use a query:
SELECT [tblNumbers]![Num]*100+
[tblNumbers_1]![Num]*10+
[tblNumbers_2]![Num] AS Expr1
FROM tblNumbers,
tblNumbers AS tblNumbers_1,
tblNumbers AS tblNumbers_2
ORDER BY [tblNumbers]![Num]*100+
[tblNumbers_1]![Num]*10+
[tblNumbers_2]![Num];
If you add 37257 to this column, you will have dates from 1/1/2002 to
9/26/2004. Add another copy of the same table and you can have 9,999
records
and dates to 5/18/2029.


"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>Thanks Steve

I think you are right. Was hoping to avoid this solution as I have about
44 tables, 160 queries, 40 odd forms and a similar number or reports etc
miles of code (Front end DB is over 12MB) and I am dreading having to go
through everything to see what is using the space information

A few more sleepless nights

Thanks

Phil

"Steve" <so***@private.emailaddresswrote in message
news:IB****************@newsread1.news.pas.earthl ink.net...
>>Phil,

I relooked at your original post and would suggest a different table
structure. You can then get the report you want.

TblSpaceAreas
SpaceAreaID Auto
SpaceAreaDesc Text

TblSpaceAreaNumbers
SpaceAreaNumberID Auto
SpaceAreaID Long Integer Associates a space area number with a
space area
SpaceAreaNumber Integer Numbers each spot in each area

TblEquipmentInSpaceAreaNumber
EquipmentInSpaceAreaNumber Auto
SpaceAreaNumberID Long Integer Associates an equipment item with a
specific space and a specific space area number
EquipmentID Long Integer

TblEquipMent
EquipmentID Auto
EquipmentDesc Text

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com


"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
Honestly, Steve, it doesn't.

Just 3 records regardless of which way the join is done.
The query doesn't have any concept of what the FirstSpace and LastSpace
are used for. The difference between these 2 figures are the number of
records required.

Thanks again

Phil
"Steve" <so***@private.emailaddresswrote in message
news:2P******************@newsread3.news.pas.ea rthlink.net...
Phil,
>
Try the query for the recordsource of your report. It will give you 79
spaces. 3 will show equipment in the space and 76 will be blank.
>
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com
>
>
>
"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>Thanks for coming back again, Steve.
>>
>Perhaps I haven't made it quite clear. In the Space table, I have
>only created records where there is actually eqipment stored. So
>there are actually only 3 records.
>The report should show 3 records with those details and 26 + 50 - 3
>blank spaces ( 26 spaces from the Food Store + 50 from the Garden
>shed less the 3 records with details). It is then easy to see at a
>glance where there is a free hole to store other items.
>>
>Thanks again
>>
>Phil
>>
>>
>"Steve" <so***@private.emailaddresswrote in message
>news:ea*****************@newsread1.news.pas.e arthlink.net...
>>Phil,
>>>
>>Try the suggested query - you should get what you want. You should
>>get a list of all areas; where there is something in the area it
>>will show in the report and if the area is empty, the field for what
>>is in that area will be blank. Just like your example.
>>>
>>PC Datasheet
>>Providing Customers A Resource For Help With Access, Excel And Word
>>Applications
>>re******@pcdatasheet.com
>>>
>>>
>>>
>>>
>>"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
>>news:46**********************@ptn-nntp-reader02.plus.net...
>>>Thanks Steve.
>>>>
>>>The records in the Space table don't actually exist. I just want a
>>>blank line on the report to show that they are available to put
>>>equipment in.
>>>>
>>>Phil
>>>>
>>>>
>>>"Steve" <so***@private.emailaddresswrote in message
>>>news:HZ*****************@newsread1.news.pas .earthlink.net...
>>>>In your query you use for the recordsource of the report, double
>>>>click on the relationship line between SpaceAreaID and SpaceTypeID
>>>>and change it from a Type 1 to a Type 2 or 3. Choose the one that
>>>>says Include all the records in SpaceAreas and only the related
>>>>records in Space.
>>>>>
>>>>PC Datasheet
>>>>Providing Customers A Resource For Help With Access, Excel And
>>>>Word Applications
>>>>re******@pcdatasheet.com
>>>>>
>>>>>
>>>>"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
>>>>news:46**********************@ptn-nntp-reader02.plus.net...
>>>>>>I have a table of SpaceAreas eg Food Store, Garden Shed etc with
>>>>>>the first and last bin for each Space Area defined. eg Food Store
>>>>>>First Space 1, last space 26 and Gargen Shed First space 1, last
>>>>>>Space 50.
>>>>>SpaceAreas Table is
>>>>>SpaceAreaID Auto
>>>>>SpaceAreaDesc Text
>>>>>FirstSpace Integer
>>>>>LastSpace Integer
>>>>>>
>>>>>I have a secont table of the Equipment Stored in each space
>>>>>>
>>>>>Space Table is
>>>>>SpaceID Auto
>>>>>SpaceTypeID Long Points to the SpaceArea
>>>>>SpaceNo Integer
>>>>>EquipmentID Long Point to the equipment
>>>>>>
>>>>>No trouble printing a report of what is where, but I want a
>>>>>report of All the locations ie
>>>>>FoodStore 1 -
>>>>>FoodStore 2 Butter
>>>>>...
>>>>>FoodStore 26
>>>>>Garden Shed 1 -
>>>>>Garden Shed 2 Mower
>>>>>Garden Shed 3 -
>>>>>...
>>>>>Garden Shed 50 Fork
>>>>>And what is in them. Many will be empty
>>>>>>
>>>>>I am trying to avoid populating the Space Table with blank data
>>>>>>
>>>>>Any thoughts please
>>>>>>
>>>>>Thanks
>>>>>>
>>>>>Phil
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>




May 2 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Brad Tobin | last post: by
38 posts views Thread by Xah Lee | last post: by
11 posts views Thread by kenneth | last post: by
10 posts views Thread by Bing Wu | last post: by
5 posts views Thread by Yasaswi Pulavarti | last post: by
2 posts views Thread by shagy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.