In a pickle!

G

Guest

Hiya

I have got so far with designing my database, forms and reports and now have
a problem. I am not sure whether i need to jiggle around with the tables and
relationships or whether is a report problem. I will try to explain in as
best detail as poss...

I have a Room Booking Table using RBID as the Primary field (Client ID, date
of booking etc.)

I have a room Requirements table using RBID which could have mutliple dates
and multiples rooms against these dates e.g. 08/05/06 - Room 1, 09/05/06 -
Room 1, 10/05/06 - Room1, 10/05/06 - Room3

I have Resources Rqd table using RBID which could have multi dates and multi
resources (08/05/06 - Flipchart, 09/05/06 - Flipchart, 10/05/06 - Projector,
10/05/06 - Other

I have a Catering table using RBID which would only have one entry per date
(08/05/06 - Pub, 09/05/06 - Pub, 10/05/06 - Buffet)

All is working well as far as input is concerned but its the output i am
having terrible problems with. I am trying to create a report to be a Room
Booking form which will have all the details on one page, this will the be
printed and signed etc.

When I try to report on the room, resources & catering requirements ideally
I would like a list that shows as:-

08/05/06 Room 1 Pub
09/05/06 Room 1 Pub
10/05/06 Room 1 Buffet
10/05/06 Room 3 Buffet

what i am getting is a HUGE list of items with a record for each room
booking a record for each resource and another for each catering requirement
- repeated about 4 times.

Should I have formed some sort of relationship on the dates as well as RBID?
Have I designed this database badly - do i need to start again?
Is it just a grouping option in a query or on the report to get what i need?

I hope this makes sense

Thanks so much for your time.

Kerry
 
D

Douglas J Steele

What's the SQL of the query you're running?

(If you're unfamiliar with how to get the SQL, open the query in Design
view, then select SQL View from the View menu. Copy everything that's in the
SQL window, and paste it back here)
 
G

Guest

Hi Doug

I have tried a few ways - this is the most recent - i have a make table
which pulls the room booking & the resource bookings into a new table then a
query on the new table and adding in the catering data

SELECT [tbl Active RB2].RBID, [tbl Active RB2].[Client Name], [tbl Active
RB2].Division, [tbl Active RB2].[Contact name], [tbl Active RB2].[Address 1],
[tbl Active RB2].[Address 2], [tbl Active RB2].[Address 3], [tbl Active
RB2].[Address 4], [tbl Active RB2].[Address 5], [tbl Active RB2].Postcode,
[tbl Active RB2].[Contact Tel No], [tbl Active RB2].[Contact Fax No], [tbl
Active RB2].[Contact Email], [tbl Active RB2].[Event Duration], [tbl Active
RB2].[Technical/Training], [tbl Active RB2].Meeting, [tbl Active RB2].[Room
Hire Date], [tbl Active RB2].[Room name], [tbl Active RB2].[Hire Start Time],
[tbl Active RB2].[Hire End Time], [tbl Active RB2].[Ref Rqd?], [tbl Active
RB2].[Lunch Rqd?], [tbl Active RB2].[Additional Costs], [tbl Active
RB2].[Total Cost of Cat], [tbl Active RB2].[Total Cost of Rooms], [tbl Active
RB2].[Total Cost of Res], [tbl Active RB2].[Grand Total Charge], [tbl Active
RB2].[Lunch Choice], [tbl Active RB2].[Other Detail], [tbl Resources/Hire
Items].[Cost Name], [tbl RH Resources].[Other Detail]
FROM [tbl Resources/Hire Items] INNER JOIN ([tbl Active RB2] INNER JOIN [tbl
RH Resources] ON [tbl Active RB2].RBID=[tbl RH Resources].RBID) ON [tbl
Resources/Hire Items].[Res ID]=[tbl RH Resources].[Res ID]
GROUP BY [tbl Active RB2].RBID, [tbl Active RB2].[Client Name], [tbl Active
RB2].Division, [tbl Active RB2].[Contact name], [tbl Active RB2].[Address 1],
[tbl Active RB2].[Address 2], [tbl Active RB2].[Address 3], [tbl Active
RB2].[Address 4], [tbl Active RB2].[Address 5], [tbl Active RB2].Postcode,
[tbl Active RB2].[Contact Tel No], [tbl Active RB2].[Contact Fax No], [tbl
Active RB2].[Contact Email], [tbl Active RB2].[Event Duration], [tbl Active
RB2].[Technical/Training], [tbl Active RB2].Meeting, [tbl Active RB2].[Room
Hire Date], [tbl Active RB2].[Room name], [tbl Active RB2].[Hire Start Time],
[tbl Active RB2].[Hire End Time], [tbl Active RB2].[Ref Rqd?], [tbl Active
RB2].[Lunch Rqd?], [tbl Active RB2].[Additional Costs], [tbl Active
RB2].[Total Cost of Cat], [tbl Active RB2].[Total Cost of Rooms], [tbl Active
RB2].[Total Cost of Res], [tbl Active RB2].[Grand Total Charge], [tbl Active
RB2].[Lunch Choice], [tbl Active RB2].[Other Detail], [tbl Resources/Hire
Items].[Cost Name], [tbl RH Resources].[Other Detail];
 
D

Douglas J Steele

You haven't given enough details.

In your original post, you talked about tables [Room Booking], [Room
Requirements], [Resources Rqd] and [Catering]

In your query, you're using tables [tbl Resources/Hire Items], [tbl Active
RB2] and [tbl RH Resources]

What are the keys of each, and their relationships?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Kerry Purdy said:
Hi Doug

I have tried a few ways - this is the most recent - i have a make table
which pulls the room booking & the resource bookings into a new table then a
query on the new table and adding in the catering data

SELECT [tbl Active RB2].RBID, [tbl Active RB2].[Client Name], [tbl Active
RB2].Division, [tbl Active RB2].[Contact name], [tbl Active RB2].[Address 1],
[tbl Active RB2].[Address 2], [tbl Active RB2].[Address 3], [tbl Active
RB2].[Address 4], [tbl Active RB2].[Address 5], [tbl Active RB2].Postcode,
[tbl Active RB2].[Contact Tel No], [tbl Active RB2].[Contact Fax No], [tbl
Active RB2].[Contact Email], [tbl Active RB2].[Event Duration], [tbl Active
RB2].[Technical/Training], [tbl Active RB2].Meeting, [tbl Active RB2].[Room
Hire Date], [tbl Active RB2].[Room name], [tbl Active RB2].[Hire Start Time],
[tbl Active RB2].[Hire End Time], [tbl Active RB2].[Ref Rqd?], [tbl Active
RB2].[Lunch Rqd?], [tbl Active RB2].[Additional Costs], [tbl Active
RB2].[Total Cost of Cat], [tbl Active RB2].[Total Cost of Rooms], [tbl Active
RB2].[Total Cost of Res], [tbl Active RB2].[Grand Total Charge], [tbl Active
RB2].[Lunch Choice], [tbl Active RB2].[Other Detail], [tbl Resources/Hire
Items].[Cost Name], [tbl RH Resources].[Other Detail]
FROM [tbl Resources/Hire Items] INNER JOIN ([tbl Active RB2] INNER JOIN [tbl
RH Resources] ON [tbl Active RB2].RBID=[tbl RH Resources].RBID) ON [tbl
Resources/Hire Items].[Res ID]=[tbl RH Resources].[Res ID]
GROUP BY [tbl Active RB2].RBID, [tbl Active RB2].[Client Name], [tbl Active
RB2].Division, [tbl Active RB2].[Contact name], [tbl Active RB2].[Address 1],
[tbl Active RB2].[Address 2], [tbl Active RB2].[Address 3], [tbl Active
RB2].[Address 4], [tbl Active RB2].[Address 5], [tbl Active RB2].Postcode,
[tbl Active RB2].[Contact Tel No], [tbl Active RB2].[Contact Fax No], [tbl
Active RB2].[Contact Email], [tbl Active RB2].[Event Duration], [tbl Active
RB2].[Technical/Training], [tbl Active RB2].Meeting, [tbl Active RB2].[Room
Hire Date], [tbl Active RB2].[Room name], [tbl Active RB2].[Hire Start Time],
[tbl Active RB2].[Hire End Time], [tbl Active RB2].[Ref Rqd?], [tbl Active
RB2].[Lunch Rqd?], [tbl Active RB2].[Additional Costs], [tbl Active
RB2].[Total Cost of Cat], [tbl Active RB2].[Total Cost of Rooms], [tbl Active
RB2].[Total Cost of Res], [tbl Active RB2].[Grand Total Charge], [tbl Active
RB2].[Lunch Choice], [tbl Active RB2].[Other Detail], [tbl Resources/Hire
Items].[Cost Name], [tbl RH Resources].[Other Detail];




Douglas J Steele said:
What's the SQL of the query you're running?

(If you're unfamiliar with how to get the SQL, open the query in Design
view, then select SQL View from the View menu. Copy everything that's in the
SQL window, and paste it back here)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


now
have tables
and ID,
date
Click to expand...
Click to expand...
 
G

Guest

Apologies, I have played around so much with this. I have copied in my
original query SQL. Thank You.

Tables in Query are:-
Room Bookings Table linked to Client data using Client ID
Room Bookings Table linked to RH Resources using RBID
Room Bookings linked to Catering using RBID
RH Resources linked to Resource/Hire Items using Res ID (statis data of
costs etc.)
tbl Catering linked to Resource Fixed Cost using Res ID (static data of
costs etc.)

SQL Code:-
SELECT [tbl RH Bookings].RBID, [tbl Client Data].[Client Name], [tbl Client
Data].Division, [tbl Client Data].[Contact name], [tbl Client Data].[Address
1], [tbl Client Data].[Address 2], [tbl Client Data].[Address 3], [tbl Client
Data].[Address 4], [tbl Client Data].[Address 5], [tbl Client Data].Postcode,
[tbl RH Bookings].[Technical/Training], [tbl RH Bookings].Meeting, [tbl RH
Bookings].[Ref Rqd?], [tbl RH Bookings].[Lunch Rqd?], [tbl RH Bookings].[Hire
Start Date], [tbl RH Bookings].[Hire Duration], [tbl RH Bookings].[Additional
Costs], [tbl RH Bookings].[Catering Cost PP], [tbl RH Bookings].[Additional
Costs Notes], [tbl RH Bookings].[Total Cost of Rooms], [tbl RH
Bookings].[Total Cost of Res], [tbl RH Bookings].[Grand Total Charge], [tbl
RH Resources].[Hire Date], [tbl RH Resources].[Other Detail], [tbl
Catering].[Hire/Training Date], [tbl Catering].[Lunch Choice], [tbl
Catering].[Other Detail], [tbl RH Resources].[Hire Date], [tbl Resources/Hire
Items].[Cost Name], [tbl RH Resources].[Other Detail]
FROM [tbl Resources/Fixed Costs] INNER JOIN ([tbl Resources/Hire Items]
INNER JOIN ([tbl Client Data] INNER JOIN (([tbl RH Bookings] INNER JOIN [tbl
RH Resources] ON [tbl RH Bookings].RBID = [tbl RH Resources].RBID) INNER JOIN
[tbl Catering] ON [tbl RH Bookings].RBID = [tbl Catering].RBID) ON [tbl
Client Data].[C ID] = [tbl RH Bookings].[Client ID]) ON [tbl Resources/Hire
Items].[Res ID] = [tbl RH Resources].[Res ID]) ON [tbl Resources/Fixed
Costs].[Res ID] = [tbl Catering].[Res ID]
ORDER BY [tbl RH Bookings].RBID;



Douglas J Steele said:
You haven't given enough details.

In your original post, you talked about tables [Room Booking], [Room
Requirements], [Resources Rqd] and [Catering]

In your query, you're using tables [tbl Resources/Hire Items], [tbl Active
RB2] and [tbl RH Resources]

What are the keys of each, and their relationships?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Kerry Purdy said:
Hi Doug

I have tried a few ways - this is the most recent - i have a make table
which pulls the room booking & the resource bookings into a new table then a
query on the new table and adding in the catering data

SELECT [tbl Active RB2].RBID, [tbl Active RB2].[Client Name], [tbl Active
RB2].Division, [tbl Active RB2].[Contact name], [tbl Active RB2].[Address 1],
[tbl Active RB2].[Address 2], [tbl Active RB2].[Address 3], [tbl Active
RB2].[Address 4], [tbl Active RB2].[Address 5], [tbl Active RB2].Postcode,
[tbl Active RB2].[Contact Tel No], [tbl Active RB2].[Contact Fax No], [tbl
Active RB2].[Contact Email], [tbl Active RB2].[Event Duration], [tbl Active
RB2].[Technical/Training], [tbl Active RB2].Meeting, [tbl Active RB2].[Room
Hire Date], [tbl Active RB2].[Room name], [tbl Active RB2].[Hire Start Time],
[tbl Active RB2].[Hire End Time], [tbl Active RB2].[Ref Rqd?], [tbl Active
RB2].[Lunch Rqd?], [tbl Active RB2].[Additional Costs], [tbl Active
RB2].[Total Cost of Cat], [tbl Active RB2].[Total Cost of Rooms], [tbl Active
RB2].[Total Cost of Res], [tbl Active RB2].[Grand Total Charge], [tbl Active
RB2].[Lunch Choice], [tbl Active RB2].[Other Detail], [tbl Resources/Hire
Items].[Cost Name], [tbl RH Resources].[Other Detail]
FROM [tbl Resources/Hire Items] INNER JOIN ([tbl Active RB2] INNER JOIN [tbl
RH Resources] ON [tbl Active RB2].RBID=[tbl RH Resources].RBID) ON [tbl
Resources/Hire Items].[Res ID]=[tbl RH Resources].[Res ID]
GROUP BY [tbl Active RB2].RBID, [tbl Active RB2].[Client Name], [tbl Active
RB2].Division, [tbl Active RB2].[Contact name], [tbl Active RB2].[Address 1],
[tbl Active RB2].[Address 2], [tbl Active RB2].[Address 3], [tbl Active
RB2].[Address 4], [tbl Active RB2].[Address 5], [tbl Active RB2].Postcode,
[tbl Active RB2].[Contact Tel No], [tbl Active RB2].[Contact Fax No], [tbl
Active RB2].[Contact Email], [tbl Active RB2].[Event Duration], [tbl Active
RB2].[Technical/Training], [tbl Active RB2].Meeting, [tbl Active RB2].[Room
Hire Date], [tbl Active RB2].[Room name], [tbl Active RB2].[Hire Start Time],
[tbl Active RB2].[Hire End Time], [tbl Active RB2].[Ref Rqd?], [tbl Active
RB2].[Lunch Rqd?], [tbl Active RB2].[Additional Costs], [tbl Active
RB2].[Total Cost of Cat], [tbl Active RB2].[Total Cost of Rooms], [tbl Active
RB2].[Total Cost of Res], [tbl Active RB2].[Grand Total Charge], [tbl Active
RB2].[Lunch Choice], [tbl Active RB2].[Other Detail], [tbl Resources/Hire
Items].[Cost Name], [tbl RH Resources].[Other Detail];




Douglas J Steele said:
What's the SQL of the query you're running?

(If you're unfamiliar with how to get the SQL, open the query in Design
view, then select SQL View from the View menu. Copy everything that's in the
SQL window, and paste it back here)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hiya

I have got so far with designing my database, forms and reports and now
have
a problem. I am not sure whether i need to jiggle around with the tables
and
relationships or whether is a report problem. I will try to explain in as
best detail as poss...

I have a Room Booking Table using RBID as the Primary field (Client ID,
date
of booking etc.)

I have a room Requirements table using RBID which could have mutliple
dates
and multiples rooms against these dates e.g. 08/05/06 - Room 1, 09/05/06 -
Room 1, 10/05/06 - Room1, 10/05/06 - Room3

I have Resources Rqd table using RBID which could have multi dates and
multi
resources (08/05/06 - Flipchart, 09/05/06 - Flipchart, 10/05/06 -
Projector,
10/05/06 - Other

I have a Catering table using RBID which would only have one entry per
date
(08/05/06 - Pub, 09/05/06 - Pub, 10/05/06 - Buffet)

All is working well as far as input is concerned but its the output i am
having terrible problems with. I am trying to create a report to be a
Room
Booking form which will have all the details on one page, this will the be
printed and signed etc.

When I try to report on the room, resources & catering requirements
ideally
I would like a list that shows as:-

08/05/06 Room 1 Pub
09/05/06 Room 1 Pub
10/05/06 Room 1 Buffet
10/05/06 Room 3 Buffet

what i am getting is a HUGE list of items with a record for each room
booking a record for each resource and another for each catering
requirement
- repeated about 4 times.

Should I have formed some sort of relationship on the dates as well as
RBID?
Have I designed this database badly - do i need to start again?
Is it just a grouping option in a query or on the report to get what i
need?

I hope this makes sense

Thanks so much for your time.

Kerry
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J Steele

I'm going to be tied up for the next few hours. If no one else pipes in with
suggestions, I'll take a look at it this evening.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Kerry Purdy said:
Apologies, I have played around so much with this. I have copied in my
original query SQL. Thank You.

Tables in Query are:-
Room Bookings Table linked to Client data using Client ID
Room Bookings Table linked to RH Resources using RBID
Room Bookings linked to Catering using RBID
RH Resources linked to Resource/Hire Items using Res ID (statis data of
costs etc.)
tbl Catering linked to Resource Fixed Cost using Res ID (static data of
costs etc.)

SQL Code:-
SELECT [tbl RH Bookings].RBID, [tbl Client Data].[Client Name], [tbl Client
Data].Division, [tbl Client Data].[Contact name], [tbl Client Data].[Address
1], [tbl Client Data].[Address 2], [tbl Client Data].[Address 3], [tbl Client
Data].[Address 4], [tbl Client Data].[Address 5], [tbl Client Data].Postcode,
[tbl RH Bookings].[Technical/Training], [tbl RH Bookings].Meeting, [tbl RH
Bookings].[Ref Rqd?], [tbl RH Bookings].[Lunch Rqd?], [tbl RH Bookings].[Hire
Start Date], [tbl RH Bookings].[Hire Duration], [tbl RH Bookings].[Additional
Costs], [tbl RH Bookings].[Catering Cost PP], [tbl RH Bookings].[Additional
Costs Notes], [tbl RH Bookings].[Total Cost of Rooms], [tbl RH
Bookings].[Total Cost of Res], [tbl RH Bookings].[Grand Total Charge], [tbl
RH Resources].[Hire Date], [tbl RH Resources].[Other Detail], [tbl
Catering].[Hire/Training Date], [tbl Catering].[Lunch Choice], [tbl
Catering].[Other Detail], [tbl RH Resources].[Hire Date], [tbl Resources/Hire
Items].[Cost Name], [tbl RH Resources].[Other Detail]
FROM [tbl Resources/Fixed Costs] INNER JOIN ([tbl Resources/Hire Items]
INNER JOIN ([tbl Client Data] INNER JOIN (([tbl RH Bookings] INNER JOIN [tbl
RH Resources] ON [tbl RH Bookings].RBID = [tbl RH Resources].RBID) INNER JOIN
[tbl Catering] ON [tbl RH Bookings].RBID = [tbl Catering].RBID) ON [tbl
Client Data].[C ID] = [tbl RH Bookings].[Client ID]) ON [tbl Resources/Hire
Items].[Res ID] = [tbl RH Resources].[Res ID]) ON [tbl Resources/Fixed
Costs].[Res ID] = [tbl Catering].[Res ID]
ORDER BY [tbl RH Bookings].RBID;



Douglas J Steele said:
You haven't given enough details.

In your original post, you talked about tables [Room Booking], [Room
Requirements], [Resources Rqd] and [Catering]

In your query, you're using tables [tbl Resources/Hire Items], [tbl Active
RB2] and [tbl RH Resources]

What are the keys of each, and their relationships?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Kerry Purdy said:
Hi Doug

I have tried a few ways - this is the most recent - i have a make table
which pulls the room booking & the resource bookings into a new table
Click to expand...
then
a
query on the new table and adding in the catering data

SELECT [tbl Active RB2].RBID, [tbl Active RB2].[Client Name], [tbl Active
RB2].Division, [tbl Active RB2].[Contact name], [tbl Active
Click to expand...
RB2].[Address
1],
[tbl Active RB2].[Address 2], [tbl Active RB2].[Address 3], [tbl Active
RB2].[Address 4], [tbl Active RB2].[Address 5], [tbl Active RB2].Postcode,
[tbl Active RB2].[Contact Tel No], [tbl Active RB2].[Contact Fax No], [tbl
Active RB2].[Contact Email], [tbl Active RB2].[Event Duration], [tbl Active
RB2].[Technical/Training], [tbl Active RB2].Meeting, [tbl Active RB2].[Room
Hire Date], [tbl Active RB2].[Room name], [tbl Active RB2].[Hire Start Time],
[tbl Active RB2].[Hire End Time], [tbl Active RB2].[Ref Rqd?], [tbl Active
RB2].[Lunch Rqd?], [tbl Active RB2].[Additional Costs], [tbl Active
RB2].[Total Cost of Cat], [tbl Active RB2].[Total Cost of Rooms], [tbl Active
RB2].[Total Cost of Res], [tbl Active RB2].[Grand Total Charge], [tbl Active
RB2].[Lunch Choice], [tbl Active RB2].[Other Detail], [tbl Resources/Hire
Items].[Cost Name], [tbl RH Resources].[Other Detail]
FROM [tbl Resources/Hire Items] INNER JOIN ([tbl Active RB2] INNER
Click to expand...
JOIN
[tbl
RH Resources] ON [tbl Active RB2].RBID=[tbl RH Resources].RBID) ON [tbl
Resources/Hire Items].[Res ID]=[tbl RH Resources].[Res ID]
GROUP BY [tbl Active RB2].RBID, [tbl Active RB2].[Client Name], [tbl Active
RB2].Division, [tbl Active RB2].[Contact name], [tbl Active
Click to expand...
RB2].[Address
1],
[tbl Active RB2].[Address 2], [tbl Active RB2].[Address 3], [tbl Active
RB2].[Address 4], [tbl Active RB2].[Address 5], [tbl Active RB2].Postcode,
[tbl Active RB2].[Contact Tel No], [tbl Active RB2].[Contact Fax No], [tbl
Active RB2].[Contact Email], [tbl Active RB2].[Event Duration], [tbl Active
RB2].[Technical/Training], [tbl Active RB2].Meeting, [tbl Active RB2].[Room
Hire Date], [tbl Active RB2].[Room name], [tbl Active RB2].[Hire Start Time],
[tbl Active RB2].[Hire End Time], [tbl Active RB2].[Ref Rqd?], [tbl Active
RB2].[Lunch Rqd?], [tbl Active RB2].[Additional Costs], [tbl Active
RB2].[Total Cost of Cat], [tbl Active RB2].[Total Cost of Rooms], [tbl Active
RB2].[Total Cost of Res], [tbl Active RB2].[Grand Total Charge], [tbl Active
RB2].[Lunch Choice], [tbl Active RB2].[Other Detail], [tbl Resources/Hire
Items].[Cost Name], [tbl RH Resources].[Other Detail];




:

What's the SQL of the query you're running?

(If you're unfamiliar with how to get the SQL, open the query in Design
view, then select SQL View from the View menu. Copy everything
Click to expand...
that's in
the
SQL window, and paste it back here)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hiya

I have got so far with designing my database, forms and reports
Click to expand...
and
now
have
a problem. I am not sure whether i need to jiggle around with the tables
and
relationships or whether is a report problem. I will try to
Click to expand...
explain
in as
best detail as poss...

I have a Room Booking Table using RBID as the Primary field
Click to expand...
(Client
ID,
date
of booking etc.)

I have a room Requirements table using RBID which could have mutliple
dates
and multiples rooms against these dates e.g. 08/05/06 - Room 1, 09/05/06 -
Room 1, 10/05/06 - Room1, 10/05/06 - Room3

I have Resources Rqd table using RBID which could have multi dates and
multi
resources (08/05/06 - Flipchart, 09/05/06 - Flipchart, 10/05/06 -
Projector,
10/05/06 - Other

I have a Catering table using RBID which would only have one entry per
date
(08/05/06 - Pub, 09/05/06 - Pub, 10/05/06 - Buffet)

All is working well as far as input is concerned but its the
Click to expand...
output i
am
having terrible problems with. I am trying to create a report to be a
Room
Booking form which will have all the details on one page, this
Click to expand...
will
the be
printed and signed etc.

When I try to report on the room, resources & catering requirements
ideally
I would like a list that shows as:-

08/05/06 Room 1 Pub
09/05/06 Room 1 Pub
10/05/06 Room 1 Buffet
10/05/06 Room 3 Buffet

what i am getting is a HUGE list of items with a record for each room
booking a record for each resource and another for each catering
requirement
- repeated about 4 times.

Should I have formed some sort of relationship on the dates as well as
RBID?
Have I designed this database badly - do i need to start again?
Is it just a grouping option in a query or on the report to get what i
need?

I hope this makes sense

Thanks so much for your time.

Kerry
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

I see 6 tables in there, and you've only describe 5.

My suggestion would be to start your query over. Start with two tables and
make sure it returns the correct number of rows. Add another table, and make
sure it returns the correct number of rows. Keep going until all of the
tables are there. It would probably be best to do this with a subset of your
data, so that it's smaller and more manageable to work with.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas J Steele said:
I'm going to be tied up for the next few hours. If no one else pipes in
with
suggestions, I'll take a look at it this evening.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Kerry Purdy said:
Apologies, I have played around so much with this. I have copied in my
original query SQL. Thank You.

Tables in Query are:-
Room Bookings Table linked to Client data using Client ID
Room Bookings Table linked to RH Resources using RBID
Room Bookings linked to Catering using RBID
RH Resources linked to Resource/Hire Items using Res ID (statis data of
costs etc.)
tbl Catering linked to Resource Fixed Cost using Res ID (static data of
costs etc.)

SQL Code:-
SELECT [tbl RH Bookings].RBID, [tbl Client Data].[Client Name], [tbl Client
Data].Division, [tbl Client Data].[Contact name], [tbl Client Data].[Address
1], [tbl Client Data].[Address 2], [tbl Client Data].[Address 3], [tbl Client
Data].[Address 4], [tbl Client Data].[Address 5], [tbl Client Data].Postcode,
[tbl RH Bookings].[Technical/Training], [tbl RH Bookings].Meeting, [tbl
RH
Bookings].[Ref Rqd?], [tbl RH Bookings].[Lunch Rqd?], [tbl RH Bookings].[Hire
Start Date], [tbl RH Bookings].[Hire Duration], [tbl RH Bookings].[Additional
Costs], [tbl RH Bookings].[Catering Cost PP], [tbl RH Bookings].[Additional
Costs Notes], [tbl RH Bookings].[Total Cost of Rooms], [tbl RH
Bookings].[Total Cost of Res], [tbl RH Bookings].[Grand Total Charge], [tbl
RH Resources].[Hire Date], [tbl RH Resources].[Other Detail], [tbl
Catering].[Hire/Training Date], [tbl Catering].[Lunch Choice], [tbl
Catering].[Other Detail], [tbl RH Resources].[Hire Date], [tbl Resources/Hire
Items].[Cost Name], [tbl RH Resources].[Other Detail]
FROM [tbl Resources/Fixed Costs] INNER JOIN ([tbl Resources/Hire Items]
INNER JOIN ([tbl Client Data] INNER JOIN (([tbl RH Bookings] INNER JOIN [tbl
RH Resources] ON [tbl RH Bookings].RBID = [tbl RH Resources].RBID) INNER JOIN
[tbl Catering] ON [tbl RH Bookings].RBID = [tbl Catering].RBID) ON [tbl
Client Data].[C ID] = [tbl RH Bookings].[Client ID]) ON [tbl Resources/Hire
Items].[Res ID] = [tbl RH Resources].[Res ID]) ON [tbl Resources/Fixed
Costs].[Res ID] = [tbl Catering].[Res ID]
ORDER BY [tbl RH Bookings].RBID;



Douglas J Steele said:
You haven't given enough details.

In your original post, you talked about tables [Room Booking], [Room
Requirements], [Resources Rqd] and [Catering]

In your query, you're using tables [tbl Resources/Hire Items], [tbl Active
RB2] and [tbl RH Resources]

What are the keys of each, and their relationships?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug

I have tried a few ways - this is the most recent - i have a make table
which pulls the room booking & the resource bookings into a new table then
a
query on the new table and adding in the catering data

SELECT [tbl Active RB2].RBID, [tbl Active RB2].[Client Name], [tbl Active
RB2].Division, [tbl Active RB2].[Contact name], [tbl Active RB2].[Address
1],
[tbl Active RB2].[Address 2], [tbl Active RB2].[Address 3], [tbl Active
RB2].[Address 4], [tbl Active RB2].[Address 5], [tbl Active RB2].Postcode,
[tbl Active RB2].[Contact Tel No], [tbl Active RB2].[Contact Fax No], [tbl
Active RB2].[Contact Email], [tbl Active RB2].[Event Duration], [tbl
Active
RB2].[Technical/Training], [tbl Active RB2].Meeting, [tbl Active
RB2].[Room
Hire Date], [tbl Active RB2].[Room name], [tbl Active RB2].[Hire
Start
Time],
[tbl Active RB2].[Hire End Time], [tbl Active RB2].[Ref Rqd?], [tbl Active
RB2].[Lunch Rqd?], [tbl Active RB2].[Additional Costs], [tbl Active
RB2].[Total Cost of Cat], [tbl Active RB2].[Total Cost of Rooms],
[tbl
Active
RB2].[Total Cost of Res], [tbl Active RB2].[Grand Total Charge], [tbl
Active
RB2].[Lunch Choice], [tbl Active RB2].[Other Detail], [tbl Resources/Hire
Items].[Cost Name], [tbl RH Resources].[Other Detail]
FROM [tbl Resources/Hire Items] INNER JOIN ([tbl Active RB2] INNER JOIN
[tbl
RH Resources] ON [tbl Active RB2].RBID=[tbl RH Resources].RBID) ON [tbl
Resources/Hire Items].[Res ID]=[tbl RH Resources].[Res ID]
GROUP BY [tbl Active RB2].RBID, [tbl Active RB2].[Client Name], [tbl
Active
RB2].Division, [tbl Active RB2].[Contact name], [tbl Active RB2].[Address
1],
[tbl Active RB2].[Address 2], [tbl Active RB2].[Address 3], [tbl Active
RB2].[Address 4], [tbl Active RB2].[Address 5], [tbl Active RB2].Postcode,
[tbl Active RB2].[Contact Tel No], [tbl Active RB2].[Contact Fax No], [tbl
Active RB2].[Contact Email], [tbl Active RB2].[Event Duration], [tbl
Active
RB2].[Technical/Training], [tbl Active RB2].Meeting, [tbl Active
RB2].[Room
Hire Date], [tbl Active RB2].[Room name], [tbl Active RB2].[Hire
Start
Time],
[tbl Active RB2].[Hire End Time], [tbl Active RB2].[Ref Rqd?], [tbl Active
RB2].[Lunch Rqd?], [tbl Active RB2].[Additional Costs], [tbl Active
RB2].[Total Cost of Cat], [tbl Active RB2].[Total Cost of Rooms],
[tbl
Active
RB2].[Total Cost of Res], [tbl Active RB2].[Grand Total Charge], [tbl
Active
RB2].[Lunch Choice], [tbl Active RB2].[Other Detail], [tbl Resources/Hire
Items].[Cost Name], [tbl RH Resources].[Other Detail];




:

What's the SQL of the query you're running?

(If you're unfamiliar with how to get the SQL, open the query in Design
view, then select SQL View from the View menu. Copy everything that's in
the
SQL window, and paste it back here)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hiya

I have got so far with designing my database, forms and reports and
now
have
a problem. I am not sure whether i need to jiggle around with
the
tables
and
relationships or whether is a report problem. I will try to explain
in as
best detail as poss...

I have a Room Booking Table using RBID as the Primary field (Client
ID,
date
of booking etc.)

I have a room Requirements table using RBID which could have mutliple
dates
and multiples rooms against these dates e.g. 08/05/06 - Room 1,
09/05/06 -
Room 1, 10/05/06 - Room1, 10/05/06 - Room3

I have Resources Rqd table using RBID which could have multi
dates and
multi
resources (08/05/06 - Flipchart, 09/05/06 - Flipchart, 10/05/06 -
Projector,
10/05/06 - Other

I have a Catering table using RBID which would only have one
entry per
date
(08/05/06 - Pub, 09/05/06 - Pub, 10/05/06 - Buffet)

All is working well as far as input is concerned but its the output i
am
having terrible problems with. I am trying to create a report to be a
Room
Booking form which will have all the details on one page, this will
the be
printed and signed etc.

When I try to report on the room, resources & catering requirements
ideally
I would like a list that shows as:-

08/05/06 Room 1 Pub
09/05/06 Room 1 Pub
10/05/06 Room 1 Buffet
10/05/06 Room 3 Buffet

what i am getting is a HUGE list of items with a record for each room
booking a record for each resource and another for each catering
requirement
- repeated about 4 times.

Should I have formed some sort of relationship on the dates as well as
RBID?
Have I designed this database badly - do i need to start again?
Is it just a grouping option in a query or on the report to get what i
need?

I hope this makes sense

Thanks so much for your time.

Kerry
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Repeat the value from the previous record 6
OT: Selective HP drivers (apologies for length) 7
Find then highlight in yellow 6
Dlookup 7
Calculating interest due on late loan payments 1
2 dimensional date sort 1
Count based on multiple criteria 2
Problems with a Query Condition to create Barcode, please Help! 2

Top