Problem with disappearing fields in query

[fsmooth]

I have the following tables & columns in each:

pk=primary key

tblHardware
-------------------------------------
*RecordNum (auto number) ----> pk
*EquipType ----> pulls from lookup table "tblEquipType"
*EquipManufacturer ----> pulls from lookup table "tblEquipManufacturer"
*EquipModelNum
*EquipSerialNum
*OrderDate
*PONum
*EquipCost
*CPUType ----> pulls from lookup table "tblCPUType"
*CPUSpeed
*MemType ----> pulls from lookup table "tblMemType"
*MemAmount
*HDAmount ----> pulls from lookup table "tblHDAmount"
*FDD
*CDROM
*CDBurn
*Soundcard
*Modem
*CRTManufacturer ----> pulls from lookup table "tblEquipManufacturer"
*CRTModelNum
*CRTSerialNum


tblHDSize
----------------------------
*RecordNum (long integer)
*HD01Size
*HD02Size
*HD03Size
*HD04Size
*HD05Size
*HD06Size
*HD07Size
*HD08Size
*HD09Size
*HD10Size


tblMaintenance
---------------------------
*RecordNum (long integer)
*MaintVendor ----> pulls from lookup "tblMaintVendor"
*MaintStart
*MaintEnd
*DateOut
*RepairVendorName ----> pulls from lookup "tblRepairVendor"
*ExpectedDateIn
*OfficialDateIn
*RepairCost
*ProblemDescript
*ResolutionDescript


tblSoftwareOS
----------------------------
*RecordNum (long integer)
*OSManufacturer ----> pulls from lookup table "tblOSManufacturer"
*OSVersion ----> pulls from lookup table "tblOSVersion"
*OSKey


tblSotwareOther
----------------------------
*RecordNum (long integer)
*SWManufacturer ----> pulls from lookup table "tblSWManufacturer"
*SWProductName ----> pulls from lookup table "tblSWProduct"
*SWVersionNum
*SWRegNum


tblSystemInfo
----------------------------
*RecordNum (long integer)
*IPAddress
*HostName
*VirusProtection (yes/no)


tblUserInfo
----------------------------
*RecordNum (long integer)
*SystemUserLastName
*SystemUserFirstName
*InternetUser
*InternetPass
*EmailUser
*EmailPass
*Location ----> pulls from lookup table "tblLocation"


The following are my lookup tables:

tblCPUType
-------------------------
*AutoNum (auto number) ----> pk
*CPUType


tblEquipManufacturer
-------------------------
*AutoNum (auto number) ----> pk
*EquipManufacturer


tblEquipType
-------------------------
*AutoNum (auto number) ----> pk
*EquipType


tblHDAmount
--------------------------
*AutoNum (auto number) ----> pk
*HDAmount


tblLocation
--------------------------
*AutoNum (auto number) ----> pk
*Location


tblMaintVendor
---------------------------
*AutoNum (auto number) ----> pk
*MaintVendor


tblMemType
---------------------------
*AutoNum (auto number) ----> pk
*MemType


tblOSManufacturer
---------------------------
*AutoNum (auto number) ----> pk
*OSManufacturer


tblOSVersion
---------------------------
*AutoNum (auto number) ----> pk
*OSVersion


tblRepairVendor
---------------------------
*AutoNum (auto number) ----> pk
*RepairVendor


tblSWManufacturer
---------------------------
*AutoNum (auto number) ----> pk
*SWManufacturer


tblSWProduct
---------------------------
*AutoNum (auto number) ----> pk
*SWProduct


From my understanding, queries are use for both lookup and data entry. When I create a query to include all items so that data can be entered, all fields disappear. I have tried both from the hardware table and from the actually lookup tables, with no change. I have even tried to add information a bit at a time with the same problems. At one point I did get them all in 1 query, but was unable to enter data in anything that was not a part of tblHardware (not lookup table). The main table is tblHardware and all the long integer "RecordNum" are linked from tblHardware "RecordNum" and are a many to 1 relationship. Not sure what else to try here.

 

[Steve101]

Suggest you publish the SQL from just one query that is not doing what you expect it to do. This will make it much easier for someone to explain what's happening.
Cheers,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[nberryman]

How are the relationships set up?

Tools, Relationships. Double click the join and check the options.

Hope that helps

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[fsmooth]

tblHardware is the main table...

tblHDSize, tblMaintenance, tbsSoftwareOS, tblSoftwareOther, tblSystemInfo, tblUserInfo have "RecordNum" as the relationship and are all set up to Enforce Referential Integrity, Cascade Updated Related Fields & Cascade Deleted Related Records.


Steve101:

I've set this query up in the wizard, so I'm not using any direct SQL. How would I set that up?

 

[Steve101]

In the query window, select a query, click on the design button, then select the View, SQL View option. Hilite all of the SQL text, click on the Edit, Cut menu options. Then Edit, Paste this into Tek-Tips. Perhaps that will yield a clue.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

SELECT tblHardware.RecordNum, tblHardware.EquipType, tblHardware.EquipManufacturer, tblHardware.EquipModelNum, tblHardware.EquipSerialNum, tblHardware.OrderDate, tblHardware.PONum, tblHardware.CPUType, tblHardware.CPUSpeed, tblHardware.MemType, tblHardware.MemAmount, tblHardware.HDAmount, tblHDsize.HD01Size, tblHDsize.HD02Size, tblHDsize.HD03Size, tblHDsize.HD04Size, tblHDsize.HD05Size, tblHDsize.HD06Size, tblHDsize.HD07Size, tblHDsize.HD08Size, tblHDsize.HD09Size, tblHDsize.HD10Size, tblHardware.FDD, tblHardware.CDROM, tblHardware.CDBurn, tblHardware.Soundcard, tblHardware.Modem, tblHardware.CRTManufacturer, tblHardware.CRTModelNum, tblHardware.CRTSerialNum, tblHardware.EquipCost, tblSoftwareOS.OSManufacturer, tblSoftwareOS.OSVersion, tblSoftwareOS.OSKey, tblSoftwareOther.SWManufacturer, tblSoftwareOther.SWProductName, tblSoftwareOther.SWVersionNum, tblSoftwareOther.SWRegNum, tblSystemInfo.RecordNum, tblSystemInfo.IPAddress, tblSystemInfo.HostName, tblSystemInfo.VirusProtection, tblUserInfo.SystemUserLastName, tblUserInfo.SystemUserFirstName, tblUserInfo.InternetUser, tblUserInfo.InternetPass, tblUserInfo.EmailUser, tblUserInfo.EmailPass, tblUserInfo.Location, tblHardware.EquipType, tblHardware.EquipType, tblHardware.EquipType, tblHardware.EquipManufacturer
FROM (((((tblHardware INNER JOIN tblHDsize ON tblHardware.RecordNum = tblHDsize.RecordNum) INNER JOIN tblMaintenance ON tblHardware.RecordNum = tblMaintenance.RecordNum) INNER JOIN tblSoftwareOS ON tblHardware.RecordNum = tblSoftwareOS.RecordNum) INNER JOIN tblSoftwareOther ON tblHardware.RecordNum = tblSoftwareOther.RecordNum) INNER JOIN tblSystemInfo ON tblHardware.RecordNum = tblSystemInfo.RecordNum) INNER JOIN tblUserInfo ON tblHardware.RecordNum = tblUserInfo.RecordNum;

 

[fsmooth]

that is everything listed there. Is that all the information you needed me to post?