View Full Version : I still need an Access Boffin...!

21-03-2002, 10:00 PM
Hi guys,

I've explained this prolem once before, and got a few pointers and a few references to books. The one book in particular is not in my local library, but I've read several more from cover to cover, yet none seem to canvas what I need to know... :(

Is it perhaps not possible???

Here's the problem:

I have a series of tables named after the type of computer component that they contain info about. Eg CPU, Motherboard, Memory, etc. These tables contain (using the CPU table as an example) CPUName, CPUCost, and a few other things unrelated.

I also have a table called 'Computers' which contains columns called things like CPU, Motherboard, Memory, HardDrive etc etc...

Next, there is a query called 'Computers Query' which has a big pile of lookups which just point each field at the corresponding field on the 'Computers' Table. Essentially the Query and the table look the same.

I want to add a column to the Computers Query (or the table if that's what is required) called 'SystemCost', which adds up the costs of the components in the preceeding columns.

Here's the sticking point:

The components names are listed in the table which corresponds to the component type (Eg 'CPU'), and that is the table containing the cost of the component. The table 'Computers' contains the names of the components, and the query needs to get the name of the component from 'Computers', and then get the price OF THAT COMPONENT, and print that in the 'SystemCost' Column.

The tricky part seems to be getting the query to get both the name of the component AND the cost of that one in particular.

How can I acheive this?

Thanks in advance!



22-03-2002, 10:50 AM
It's done in the query design grid by entering an 'expression' Expn:Total=[CostA+CostB....] You then have a calculated value, be wary of adding the 'Name' fields as they will complicate things, better to add them later on in the report. Also watch out for fields with null values or no data entered as the screwup the calculations.

22-03-2002, 05:42 PM

But how do I specify which cost to line up with the component in question?

CostA+CostB will take the first cost in the tableA and add it to the first cost in TableB, and supposing my the component I want the cost of is in the third row, what do I do?