Query Results

[Fie]

Hey..


I have 2000 records in my b.dase and when i run the query i only
produces 1800, I have just relised why this is... basically i have 3
tables


1: School Details:- which contains -- School Name, Cost Centre, No
pupils & Mgnt Code
2: Meal Uptake:- which contains --- no days, no meals, weekending date
& School name
3: Meal Numbers:- which contrains --- no tickets, returned tickets,
cash banked, free meals


Table 1 School details is default values that are always related to the
school and unlike to change but need to be displayed on reports, and
the No pupils figure is need to do a calculation which i have in the
query. When i run the query without including table 1, it displays 2000
records and when i include it, it only produces 1800 records... is
there anyway i can get round this..??
would it work if i just left it out the query and when iam designing
the report but Cost Centre, No pupils & Mgnt code in report and do
calculaitons in report???

any1 any ideas??

fiona

 

[BillCo]

sounds like there are orphaned records in your db - i.e. meal uptakes
where there is no matching school!

what you need to do to show all the records is a left join...
- in the design view of the query double click on the line going
between school details and meal uptake. select the option that says
"show all records from meal update and matching records from school
details". this will convert the join from the default inner join to a
left join and display all meal update records regardless of whether
there is a match in the school details table.

only problem is that you will have 200 records where there is no "No
pupils" field for your calculation - so that wont work out there

would it work if i just left it out the query and when iam
designing the report but Cost Centre, No pupils & Mgnt
code in report and do calculaitons in report???

sorry - i didnt understand that bit

 

[Fie]

yup that works displays 2000 records now...
So why does it not display pupil no. name of school etc
when they have been entered into the meal uptake table??

 

[Fie]

just a thought... if i was to instead of having the School Details
table..
in my ComboBox in my Meal Uptake for for selecting a school... make it
have columns
for School Name, Cost Centre, No pupils & Mgnt Code. Would i be able to
extract them
from the combo box and display them in a Report??? and if i can how do
i do this..???

 

[BillCo]

The current design would be better, so you're better off fixing the
referential integrity... i.e. find the 200 records in the Meal Uptake
table that have no related records in the School Details table and find
out why:
- is there a school missing from the school details table?
- Has the spelling of a school name changed in the school details
table?
- Were there records entered into the Meal Uptake table where no
school was entered, or the school name is spelt differently to to how
it's spelt in the School details table?

a quick way to find out is to run this query (create new query, go into
sql view and paste this - make sure i got the spelling of all the table
names and field names correct):

SELECT
[School name]
FROM
[Meal Uptake] LEFT JOIN [School Details] ON [Meal Uptake].[School
name] = [School Details].[School name]
WHERE
[School Details].[School name] Is Null
GROUP BY
[Meal Uptake].[School name]


this will give you a list of all of the school names in the the meal
uptake table where there is no corresponding record in the school
details table. You must manually update the school details table to
ensure that there is a record with the same school name (spelling
essential) matching each result here.

A blank in the results will represent records where there is no school
name entered in the uptake table. In this case you must update all the
records in the uptake table to add a school name.

Good luck!

 

[Fie]

hey... thanks for all your help...

spoke to my boss about it and database aint gonna be put in place till
start of new financial year, so all the data already in it will be
deleted yippee
making my life easier will keep everything how it is cause i know it
all works
cheers

fie x

 

[BillCo]

Thats great -
do yourself a favour though - actually two favours:
1. make a copy of the database before you delete any data!!!!!
2. if you have time to, learn about referential integrity, but either
way - define the relationships in the relationship window before you
proceed to fill the data again. Cascade updates and deletions etc. and
ensure that you never wind up with orphaned records again!

All the best!

B