Records disappear when creating a query

[fsmooth]

I'm currently making a inventory database for my work and am running smack into a brick wall.

I have the following tables:

Hardware (all computer equipment)
HDSize (for each hard drive allows to fill in a size)
Maintenance (who our vendor is, contract info, etc.)
SoftwareOS
SoftwareOther
SystemInfo (network information)
UserInfo

The hardware is my main table and has a record number as the primary key and is an autonumber (which is how the relationships are setup to the other tables). I have 5 lookup tables (EquipmentType, EquipmentManufacturer, CPUType, MemoryType & HDAmount) that are stickly used to pull information from. There are a few more that go to the other tables, but not going to put all of it here. I created a query with just the hardware table and everything works like normal. As soon as I add information from one of the other tables, the record/fields disappear. I'm trying to bring information from all my main tables (ones listed above, not the lookup tables) into one query for data entry purposes. I have tried everything I can think of. What am I not doing, or doing incorrectly?

 

[Darrylles]

Hiya,

More info required.

What are the relationships between tables?

You say the pk is: 'how the relationships are setup to the other tables'.

Describe the hardware table, and one other table (i.e. all fields in each), then describe which fields are linked.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[fsmooth]

The recordnumber is the primary key, but only in the hardware table. I originally tried to make the recordnumber in every table the primary key but then doesn't allow them to work together for some reason (i would get some table with a record # of 15 and other 50...etc). In the other tables (not the hardware table) the recordnumber is not a primary key (as I said) and is also not an autonumber.

Here are my tables:

tblHardware (primary key is RecordNum
-------------------------------------
*RecordNum
*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

tblCPUType - no primary key and this is a lookup table
---------------------------
*CPUType
*AMD Athlon
*AMD Athlon Thunderbird
*AMD Athlon XP
*AMD Duron
*AMD K6
*Celeron
*Pentium
*PII
*PIII
*PIIII
*Xeon

here is one more table:

tblMaintenance - no primary key
-------------------------------
*RecordNum
*MaintVendor ----> pulls from lookup table "tblMaintVendor"
*MaintStart
*MaintEnd
*DateOut
*RepairVendorName ----> pulls from lookup table "tblRepairVendor"
*ExpectedDateIn
*OfficialDateIn
*RepairCost
*ProblemDescript
*ResolutionDescript

Let me know if you need any more information.

 

[Darrylles]

Hi fs,

The following is an example of relational tables - not meant to be a complete solution for you, but a pointer to how to design a relational database correctly.
(You will get relational db purists who disagree, but I feel that this is the best way).

Regardless of whether or not the table is a 'lookup', you should always LINK the lookup record via keys to the Hardware table, not store the ACTUAL value of the lookup record in the Hardware table (this means that if you ever need to change the lookup value; you only change it once - in the lookup table and not numerous times in the Hardware table).

[tt]
tblHardware tblCPU
----------- ------
HW_pk autonum |----1 CPU_pk autonum
CPU_fk long M----| CPU_Type text
Maint_fk long M---|
Equip_fk long M-| | tblMaintenance
| | --------------
| |-----1 Maint_pk autonum
| Maint_start date
| Maint_end date
|
| tblEquipment
| ------------
|-------1 Equip_pk autonum
Type string
[/tt]

The '1' and the 'M' refer to a '1 to many' relationship, it means that the record from the '1' table may be referred to by 'M'any records from the related table.
"1 to many" actually means 1 related to 0,1 or Many records.

pk = primary key, fk = foreign key.

The 'foreign key' always holds a primary key value of a related table.

One question, your tblCPUType, are they fields within the table, or are they records?
If they are fields, then get rid of this definitely.

You'll probably have questions - just post 'em.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[fsmooth]

ok...I have the following questions:

1) What is a foreign key, and how do I make something become a foreign key?

2) For the 1 to many relationships, I am using the autonumbers in tblCPU, tblMaintenance, tblEquipment to the long integer in tblHardware, is that correct?

3) What is the purpose (this is mainly so I can understand this more) of using autonumbers in all of the tables?