PS. You should probably use a different field name than "Description". This
word is considered a reserved word in Access:
List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/?id=286335
Other words to avoid, many of which are the same, are shown in this KB
article:
List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266
Also, the LEFT JOIN that I mentioned in my last reply might actually be
RIGHT JOIN. Not that it matters that much, since the SQL statement will be
written under the covers for you, if you first establish the relationships.
If you have already established relationships using INNER JOIN, and you wish
to keep them that way, then another option is to change the join type in the
query itself. You can do this by carefully double-clicking on the join lines
displayed in query design view.
Tom
_____________________________________________
:
Hi Jessica,
Your query is using INNER JOIN to join lots of tables. This means that the
join fields on each side of every relation must include matching values, in
order for the query to return any records. You have two options. Either make
sure to add data to all fields that are involved in a join, or try changing
the INNER JOINs to LEFT JOIN, where the join lines radiate outward from the
central table.
To use the second option, use Tools > Relationships... to establish
relationships if you have not already done so. Set the option to enforce
referential integrity. Click on the Join Type button. Select "Include all
records from 'UPC' and only those records from 'InsertTableNameHere' where
the joined fields are equal."
You should see arrowheads on the join lines that point away from your
central UPC table. This should allow a query to return records from the UPC
table, even if matching values are not present in the related tables. Then
try creating a new query that involves the same tables. Your existing query
will still include the INNER JOIN statements, so it will be easier to create
a new query from scratch. The new query will inherit the relationships that
you established using the Tools > Relationships... view.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Hi Tom Thank you for your reply. I have a main table which has a UPC
number as its primary key. What I am trying to do is create a report
that list all of the fields in my main table and fills in the fields
with data that has been entered in the table. The problem is I am using
a query and it is not showing any records and I think its because not
one record has data in every field. When I base the query on fields
where I know records have data in it works. The reason I want all the
fields on the report is so that the user knows that material is not
needed. Here is my SQL query
SELECT Container.Description, Tray.Description, [Slip
Sheets].Description, Inserts.Description, [Pallet Tag].Description,
[Tier Sheets].Description, [Cover Sheet].Description, Wrap.Description,
Caps.Description, Pallets.PalletPKNumber, Pallets.PalletDescription,
[Pallet Patterns].PalletPattern
FROM Materials AS [Tier Sheets] INNER JOIN (Materials AS [Pallet Tag]
INNER JOIN (Materials AS Inserts INNER JOIN (Materials AS [Slip Sheets]
INNER JOIN (Materials AS [Cover Sheet] INNER JOIN (Materials AS Wrap
INNER JOIN (Materials AS Caps INNER JOIN (Materials AS Tray INNER JOIN
(Materials AS [Container] INNER JOIN (Expirations INNER JOIN ([Pallet
Patterns] INNER JOIN (Pallets INNER JOIN UPC ON Pallets.PalletID =
UPC.PalletID) ON [Pallet Patterns].PatternId = UPC.PalletPattern) ON
Expirations.ExpirationID = UPC.ExpirationID) ON Container.PKNumber =
UPC.ContainerPKNumber) ON Tray.PKNumber = UPC.TrayPKNumber) ON
Caps.PKNumber = UPC.CapPKNumber) ON Wrap.PKNumber = UPC.WrapPKNumber) ON
[Cover Sheet].PKNumber = UPC.CoverSheetPKNumber) ON [Slip
Sheets].PKNumber = UPC.SlipSheetPKNumber) ON Inserts.PKNumber =
UPC.InsertPKNumber) ON [Pallet Tag].PKNumber = UPC.PalletTag) ON [Tier
Sheets].PKNumber = UPC.TierSheetPKNumber;
Thank you
Jess
__________________________________________
Tom Wickerath wrote:
Hi Jessica,
Is it normal that a main table has blank fields or should I create
more tables to eliminate blank fields.
This depends on the nature of the data. If the blank fields represent
repeating groups of fields, then you should definately add a new table. An
example would be having field names like this:
JanuarySales
FebruarySales
MarchSales
etc.
A table should represent a single subject. If the current table design
requires you to add a new field to accomodate similar data to what you
already have, then this is a red flag indicating a design problem. You don't
want to have many-to-many (M:N) relationships within a single table. Taking
ice creme, for example, a table should not attempt to include fields that
describe attributes about a particular flavor (name, color, etc.) plus the
ingrediants of each flavor. You would essentially have a built-in M:N
relationship if you attempted to do so.
I am running into problems creating a query because I need to pull data
from all the fields in the main table and I get no records.
Post the SQL (Structured Query Language) statement for your query. Open the
query in design view. Then click on View > SQL View. Select and copy the
entire SQL statement. Post it in a reply.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Hello All
Is it normal that a main table has blank fields or should I create more
tables to eliminate blank fields. I am running into problems creating a
query because I need to pull data from all the fields in the main table
and I get no records.
TIA,
Jess
