the sql and structure

  • Thread starter Thread starter Duncan
  • Start date Start date
D

Duncan

left out one other table:
Office Table
OfficeID
CorpID
OfficeName
BuildingID
OfficeAddress
OfficeCity
OfficeState

Hope all that helps some.
-----Original Message-----
the query was created by Access: Here is the SQL:
PARAMETERS [Enter Lease ID #:] Long;
TRANSFORM Sum([Rent by Month].[RentAmount]) AS
SumOfRentAmount
SELECT [Leases].[LeaseID], [Leases].[Lessee], [Leases].
[Lessor], [Leases].[OfficeID], [Offices].[CorpID],
[Offices].[OfficeName], [Offices].[OfficeAddress],
[Offices].[OfficeCity], [Offices].[OfficeState], [Leases].
[Property Description], [Leases].[Begin Date], [Leases].
[End Date], [Leases].[Renewable], [Leases].[PaymentTerm],
[Leases].[Utilities], [Leases].[UtilitiesComments],
[Leases].[Insurance], [Leases].[LiabilityInsurance],
[Leases].[PropertyInsurance], [Leases]. [InsuranceDetails],
[Leases].[AdditionalComments], [Rent by Month].[Year],
[Leases].[InsuranceDetails], Sum([Rent by Month].
[RentAmount]) AS [Total Of RentAmount]
FROM (Offices INNER JOIN Leases ON [Offices].[OfficeID]=
[Leases].[OfficeID]) INNER JOIN [Rent by Month] ON
[Leases].[LeaseID]=[Rent by Month].[LeaseID]
WHERE ((([Leases].[LeaseID])=[Enter Lease ID #:])) Or
((([Enter Lease ID #:]) Is Null))
GROUP BY [Leases].[LeaseID], [Leases].[Lessee], [Leases].
[Lessor], [Leases].[OfficeID], [Offices].[CorpID],
[Offices].[OfficeName], [Offices].[OfficeAddress],
[Offices].[OfficeCity], [Offices].[OfficeState], [Leases].
[Property Description], [Leases].[Begin Date], [Leases].
[End Date], [Leases].[Renewable], [Leases].[PaymentTerm],
[Leases].[Utilities], [Leases].[UtilitiesComments],
[Leases].[Insurance], [Leases].[LiabilityInsurance],
[Leases].[PropertyInsurance], [Leases]. [InsuranceDetails],
[Leases].[AdditionalComments], [Rent by Month].[Year],
[Leases].[InsuranceDetails]
ORDER BY [Leases].[LeaseID], [Rent by Month].[Year], [Rent
by Month].[Month]
PIVOT [Rent by Month].[Month];

THe structure is this:
Leases Table Rent by Month Table
LeaseID LeaseID
Lessee Month
Lessor Year
OfficeID RentAmount
PropDescrip
BeginDate
EndDate
Renewable
PaymentTerm
Utilities
Utilities Comments
Insurance
Liability
Property
Insurance Details
Comments


-----Original Message-----
Please share your table structure and SQL view.

--
Duane Hookom
MS Access MVP





.
Click to expand...
.
Click to expand...
 
When your issue occurs, is it because a particular LeaseID didn't collect
any rent during a month? If you want all months to show, you could add all
twelve months into the Column Headings property of the crosstab.

--
Duane Hookom
MS Access MVP


Duncan said:
left out one other table:
Office Table
OfficeID
CorpID
OfficeName
BuildingID
OfficeAddress
OfficeCity
OfficeState

Hope all that helps some.
-----Original Message-----
the query was created by Access: Here is the SQL:
PARAMETERS [Enter Lease ID #:] Long;
TRANSFORM Sum([Rent by Month].[RentAmount]) AS
SumOfRentAmount
SELECT [Leases].[LeaseID], [Leases].[Lessee], [Leases].
[Lessor], [Leases].[OfficeID], [Offices].[CorpID],
[Offices].[OfficeName], [Offices].[OfficeAddress],
[Offices].[OfficeCity], [Offices].[OfficeState], [Leases].
[Property Description], [Leases].[Begin Date], [Leases].
[End Date], [Leases].[Renewable], [Leases].[PaymentTerm],
[Leases].[Utilities], [Leases].[UtilitiesComments],
[Leases].[Insurance], [Leases].[LiabilityInsurance],
[Leases].[PropertyInsurance], [Leases]. [InsuranceDetails],
[Leases].[AdditionalComments], [Rent by Month].[Year],
[Leases].[InsuranceDetails], Sum([Rent by Month].
[RentAmount]) AS [Total Of RentAmount]
FROM (Offices INNER JOIN Leases ON [Offices].[OfficeID]=
[Leases].[OfficeID]) INNER JOIN [Rent by Month] ON
[Leases].[LeaseID]=[Rent by Month].[LeaseID]
WHERE ((([Leases].[LeaseID])=[Enter Lease ID #:])) Or
((([Enter Lease ID #:]) Is Null))
GROUP BY [Leases].[LeaseID], [Leases].[Lessee], [Leases].
[Lessor], [Leases].[OfficeID], [Offices].[CorpID],
[Offices].[OfficeName], [Offices].[OfficeAddress],
[Offices].[OfficeCity], [Offices].[OfficeState], [Leases].
[Property Description], [Leases].[Begin Date], [Leases].
[End Date], [Leases].[Renewable], [Leases].[PaymentTerm],
[Leases].[Utilities], [Leases].[UtilitiesComments],
[Leases].[Insurance], [Leases].[LiabilityInsurance],
[Leases].[PropertyInsurance], [Leases]. [InsuranceDetails],
[Leases].[AdditionalComments], [Rent by Month].[Year],
[Leases].[InsuranceDetails]
ORDER BY [Leases].[LeaseID], [Rent by Month].[Year], [Rent
by Month].[Month]
PIVOT [Rent by Month].[Month];

THe structure is this:
Leases Table Rent by Month Table
LeaseID LeaseID
Lessee Month
Lessor Year
OfficeID RentAmount
PropDescrip
BeginDate
EndDate
Renewable
PaymentTerm
Utilities
Utilities Comments
Insurance
Liability
Property
Insurance Details
Comments


-----Original Message-----
Please share your table structure and SQL view.

--
Duane Hookom
MS Access MVP


This is the problem I am having. I am running a crosstab
query that draws data from two tables - Lease and Rent by
Month. The query gives me a break down of each month and
yearly total from the Rent by Month table and all other
fields from the Lease table. However, on about 5% of the
results, it will only return some on the monthly totals,
but always the correct sum. In other words, it will only
display figures for January and August rather than all 12
months. There is no pattern and it is generally sporadic
on which months it will return for these 5 records out of
about 90. I can't seem to find any solution. The query
works perfect for the other 85.
Any suggestions? Thanks.


.
Click to expand...
.
Click to expand...
Click to expand...
 
There is information in the table for the leases. The
query just does not display them. Today I check the
database and only two arent working properly. Perhaps this
Adding the months into the column headings property
doesn't work either. Perhaps this is some kind of bug in
Access...? It just doesn't make sense.
Thanks for the help though. Its greatly appreciated.
-----Original Message-----
When your issue occurs, is it because a particular LeaseID didn't collect
any rent during a month? If you want all months to show, you could add all
twelve months into the Column Headings property of the crosstab.

--
Duane Hookom
MS Access MVP


Duncan said:
left out one other table:
Office Table
OfficeID
CorpID
OfficeName
BuildingID
OfficeAddress
OfficeCity
OfficeState

Hope all that helps some.
-----Original Message-----
the query was created by Access: Here is the SQL:
PARAMETERS [Enter Lease ID #:] Long;
TRANSFORM Sum([Rent by Month].[RentAmount]) AS
SumOfRentAmount
SELECT [Leases].[LeaseID], [Leases].[Lessee], [Leases].
[Lessor], [Leases].[OfficeID], [Offices].[CorpID],
[Offices].[OfficeName], [Offices].[OfficeAddress],
[Offices].[OfficeCity], [Offices].[OfficeState], [Leases].
[Property Description], [Leases].[Begin Date], [Leases].
[End Date], [Leases].[Renewable], [Leases]. [PaymentTerm],
[Leases].[Utilities], [Leases].[UtilitiesComments],
[Leases].[Insurance], [Leases].[LiabilityInsurance],
[Leases].[PropertyInsurance], [Leases]. [InsuranceDetails],
[Leases].[AdditionalComments], [Rent by Month].[Year],
[Leases].[InsuranceDetails], Sum([Rent by Month].
[RentAmount]) AS [Total Of RentAmount]
FROM (Offices INNER JOIN Leases ON [Offices].[OfficeID] =
[Leases].[OfficeID]) INNER JOIN [Rent by Month] ON
[Leases].[LeaseID]=[Rent by Month].[LeaseID]
WHERE ((([Leases].[LeaseID])=[Enter Lease ID #:])) Or
((([Enter Lease ID #:]) Is Null))
GROUP BY [Leases].[LeaseID], [Leases].[Lessee], [Leases].
[Lessor], [Leases].[OfficeID], [Offices].[CorpID],
[Offices].[OfficeName], [Offices].[OfficeAddress],
[Offices].[OfficeCity], [Offices].[OfficeState], [Leases].
[Property Description], [Leases].[Begin Date], [Leases].
[End Date], [Leases].[Renewable], [Leases]. [PaymentTerm],
[Leases].[Utilities], [Leases].[UtilitiesComments],
[Leases].[Insurance], [Leases].[LiabilityInsurance],
[Leases].[PropertyInsurance], [Leases]. [InsuranceDetails],
[Leases].[AdditionalComments], [Rent by Month].[Year],
[Leases].[InsuranceDetails]
ORDER BY [Leases].[LeaseID], [Rent by Month].[Year], [Rent
by Month].[Month]
PIVOT [Rent by Month].[Month];

THe structure is this:
Leases Table Rent by Month Table
LeaseID LeaseID
Lessee Month
Lessor Year
OfficeID RentAmount
PropDescrip
BeginDate
EndDate
Renewable
PaymentTerm
Utilities
Utilities Comments
Insurance
Liability
Property
Insurance Details
Comments



-----Original Message-----
Please share your table structure and SQL view.

--
Duane Hookom
MS Access MVP


This is the problem I am having. I am running a crosstab
query that draws data from two tables - Lease and Rent
by
Month. The query gives me a break down of each month and
yearly total from the Rent by Month table and all other
fields from the Lease table. However, on about 5% of the
results, it will only return some on the monthly totals,
but always the correct sum. In other words, it will only
display figures for January and August rather than all
12
months. There is no pattern and it is generally sporadic
on which months it will return for these 5 records out
of
about 90. I can't seem to find any solution. The query
works perfect for the other 85.
Any suggestions? Thanks.


.

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


.
Click to expand...
 
I expect that you are filtering your records by using INNER JOINs.
Pull every table out except the [Lease By Month] and try it. All that extra
stuff just complicates a basic crosstab. You can add them back in by
combining your crosstab query with the other tables later.

PARAMETERS [Enter Lease ID #:] Long;
TRANSFORM Sum([Rent by Month].[RentAmount]) AS
SumOfRentAmount
SELECT [Rent By Month].[LeaseID], Sum([RentAmount]) AS [Total Of RentAmount]
FROM [Rent by Month]
WHERE [LeaseID]=[Enter Lease ID #:]
GROUP BY [LeaseID]
ORDER BY [LeaseID], [Year], [Month]
PIVOT [Month];


--
Duane Hookom
MS Access MVP


Duncan said:
There is information in the table for the leases. The
query just does not display them. Today I check the
database and only two arent working properly. Perhaps this
Adding the months into the column headings property
doesn't work either. Perhaps this is some kind of bug in
Access...? It just doesn't make sense.
Thanks for the help though. Its greatly appreciated.
-----Original Message-----
When your issue occurs, is it because a particular LeaseID didn't collect
any rent during a month? If you want all months to show, you could add all
twelve months into the Column Headings property of the crosstab.

--
Duane Hookom
MS Access MVP


Duncan said:
left out one other table:
Office Table
OfficeID
CorpID
OfficeName
BuildingID
OfficeAddress
OfficeCity
OfficeState

Hope all that helps some.

-----Original Message-----
the query was created by Access: Here is the SQL:
PARAMETERS [Enter Lease ID #:] Long;
TRANSFORM Sum([Rent by Month].[RentAmount]) AS
SumOfRentAmount
SELECT [Leases].[LeaseID], [Leases].[Lessee], [Leases].
[Lessor], [Leases].[OfficeID], [Offices].[CorpID],
[Offices].[OfficeName], [Offices].[OfficeAddress],
[Offices].[OfficeCity], [Offices].[OfficeState], [Leases].
[Property Description], [Leases].[Begin Date], [Leases].
[End Date], [Leases].[Renewable], [Leases]. [PaymentTerm],
[Leases].[Utilities], [Leases].[UtilitiesComments],
[Leases].[Insurance], [Leases].[LiabilityInsurance],
[Leases].[PropertyInsurance], [Leases].
[InsuranceDetails],
[Leases].[AdditionalComments], [Rent by Month].[Year],
[Leases].[InsuranceDetails], Sum([Rent by Month].
[RentAmount]) AS [Total Of RentAmount]
FROM (Offices INNER JOIN Leases ON [Offices].[OfficeID] =
[Leases].[OfficeID]) INNER JOIN [Rent by Month] ON
[Leases].[LeaseID]=[Rent by Month].[LeaseID]
WHERE ((([Leases].[LeaseID])=[Enter Lease ID #:])) Or
((([Enter Lease ID #:]) Is Null))
GROUP BY [Leases].[LeaseID], [Leases].[Lessee], [Leases].
[Lessor], [Leases].[OfficeID], [Offices].[CorpID],
[Offices].[OfficeName], [Offices].[OfficeAddress],
[Offices].[OfficeCity], [Offices].[OfficeState], [Leases].
[Property Description], [Leases].[Begin Date], [Leases].
[End Date], [Leases].[Renewable], [Leases]. [PaymentTerm],
[Leases].[Utilities], [Leases].[UtilitiesComments],
[Leases].[Insurance], [Leases].[LiabilityInsurance],
[Leases].[PropertyInsurance], [Leases].
[InsuranceDetails],
[Leases].[AdditionalComments], [Rent by Month].[Year],
[Leases].[InsuranceDetails]
ORDER BY [Leases].[LeaseID], [Rent by Month].[Year],
[Rent
by Month].[Month]
PIVOT [Rent by Month].[Month];

THe structure is this:
Leases Table Rent by Month Table
LeaseID LeaseID
Lessee Month
Lessor Year
OfficeID RentAmount
PropDescrip
BeginDate
EndDate
Renewable
PaymentTerm
Utilities
Utilities Comments
Insurance
Liability
Property
Insurance Details
Comments



-----Original Message-----
Please share your table structure and SQL view.

--
Duane Hookom
MS Access MVP


This is the problem I am having. I am running a
crosstab
query that draws data from two tables - Lease and Rent
by
Month. The query gives me a break down of each month
and
yearly total from the Rent by Month table and all other
fields from the Lease table. However, on about 5% of
the
results, it will only return some on the monthly
totals,
but always the correct sum. In other words, it will
only
display figures for January and August rather than all
12
months. There is no pattern and it is generally
sporadic
on which months it will return for these 5 records out
of
about 90. I can't seem to find any solution. The query
works perfect for the other 85.
Any suggestions? Thanks.


.

.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top