CODEWITHSUNDEEP
sqldatabasems-access
aloeveraking24
aloeveraking24

Reputation: 23

Access- calculate a field based on any matching fields in another table

I am trying to keep track of available space in a warehouse using Access. In table A I have a field of 'width used' and 'shelf id' and I'd like it to sum 'width used' based on table B where 'shelf id' of the box matches 'shelf id' in table A it would then sum the 'box width' (which could be multiple boxes as there are multiple boxes on each shelf) from table B and put it in table A.

Example for the sake of clarity:

Table B:
BoxID    Box Width    ShelfID
1        24           2
2        24           2

Table A:
ShelfID    Used Width
2          48

Thank you for any and all help! I have tried building a query and using the lookup wizard, it never works the way I want it to because I'm doing something wrong. I haven't messed with object dependencies.. I'm going wrong somewhere and I don't know where. So far I've established the ShelfID as the foreign key in Table B but that's as far as I've gotten.

EDIT: The SQL part isn't where I'm struggling, it's how to do this in Access specifically that I can't figure out. I'm trying to get that specific field (width used) to draw information from another table and I'm not sure which feature or where in Access to do this- lookup wizard.. object dependencies relationships... is it a macro? I don't use Access often and this is a bit more complicated than things I have used it for in the past.

Upvotes: 1

Views: 234

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Is what you want a simple aggregation query?

select shelfId, sum([Box Width]) as used_width
from b
group by shelfId;

To insert the value:

insert into a(shelfId, [Used Width])
    select shelfId, sum([Box Width]) as used_width
    from b
    group by shelfId;

Normally, you would not put the result in a table. You might define a view for the summary, so the values are always up-to-date.

Upvotes: 1

Related Questions