Help with indexes

[PubCrawl]

Hi all, I need to get the "Quantity" field in this table to show the count of the hardware (HWID) only if it occurs mulitple times in a particular computer (ComID).

Datasheet view:

I can't seem to get any result but zero.

The relationships need to look like this:

[CJ_London]

indexing has nothing to do with this and you should not be storing this sort of calculated value in a table - use an aggregate query

SELECT ComID, HWID, Count(HWID) as Quantity
FROM [Computer Parts]
GROUP BY ComID, HWID

[PubCrawl]

Thanks for that, others told it did have something to do with indexing.

However, this is part of a uni assignment which states what tables and queries we need to make. Further, when I do put that code in, it returns all Quantities of 1. Here's some more context to the assignment, if it helps, and you can bothered. I've been stuck on this part for a week, I've gone as far ahead in the assignment I can, but have hit road blocks all the way. I'm not great at this program.


About the computer parts (ComputerParts), he wants to keep:
· ComID: the ID number used to distinguish each PC, which comes from the Computer table (e.g.,
1000)
· HWID: the ID that identifies the specific brand of hardware used to build the PC (e.g., 10000)
· Quantity: the number of the particular hardware component used in the PC (e.g., 1)
· The combination of ComID and HWID will be unique in the table. That is, every hardware used in a
particular computer will be recorded only once and if multiple number of the same hardware (e.g.
RAM) is used then its recorded in the Quantity field.

[Minty]

As per you more detailed description, you have stated the combination of ComID and HWID are unique so the Quantity field cannot be a calculation it is a data fact.

You need to make a compound index on ComID and HWID .
You can then query that data to give you a total number of different Hardware across the entire data set.

[PubCrawl]

I'm so confused. I'm not even sure what a compound index is? Let alone how and where to format it? How do I get the quantity results in that table? The assignment states all of the queries we are meant to do and that one isn't one of them. For some reason the quantity is now showing nothing at all.

[Minty]

As we have stated, the quantity in the table must be a datapoint not a calulation.

Take a step back and lets use some sample data ;

TableCompHardware				TableComp				TableHardware
ComID	HWID	Qty		ComID	CompName			HWID	HWDescription
1	1	1		1	Berts PC			1	2Gb Ram
1	2	1		2	Harrys PC			2	Nvidia Grphx Card 1001
1	3	1						3	Sound Card
2	1	2
2	2	1

As you can see you can't calculate the qty - it a piece of fixed data, there are 2 x 2gb sticks of Ram in Harry's pc.

You had the correct idea with the setting the primary key on the two fields ComID and HWID.

You can use a query to add up the total amounts of hardware across the whole list of computers, but you can't magic that data out of the ether.

[PubCrawl]

I just don't get it. Nothing shows in the quantity bit at all, whereas before it was at least saying zero. Thank you heaps anyway, between this and the rest of the assigment, I guess I'm just fried.

[Minty]

Nothing will show up in the quantity field - it is data that needs entering, just like the computer and hardware ID's.
Enter a number and/or set a default value of 1 as it's the minimum you would record.

[CarlettoFed]

Perhaps if you attach a sample file, replacing any sensitive data will make it easier for you to see how easily your problem is solved.