多表查询 [英] multiple table query

问题描述

首先，我想说我已经使用这个板来解决我访问过的许多问题，我感谢所有贡献的人。我试图自己解决我的问题，但我很难过并且感谢你能给予的任何帮助。


这是一个预订的数据库


我正在尝试对两个表运行查询。第一张桌子（家庭）有以下字段


家庭

邻居

街道

nmbsnglebedsavail

Nmbrdoulbedsavail

nmbrtriplebedsavail


第二张桌子，预订有


家庭< br $>
旅行者

Arrivaldate

Departuredate

类型（什么样的房间）


我把表单设置得很好，所以当我预订并选择单人床时，会从nmbrsnglebedsavail中减去这个数字，它会给我剩余的可用数量。


我的问题在于查询。我怎样才能查询剩余的床位？当我使用我在查询表格中使用的相同公式时，剩余的床从一个记录到下一个记录是静态的，并且不会减去根据每个家庭使用的总床位数。如果我有两个旅行者（两个单独的记录）访问家庭史密斯该查询减去单床。来自Total Avail，但没有将小计带到下一个记录。因此，我在每张唱片上获得相同数量的剩余床位，实际上，它应该将所有单曲记录在一起。从预订表中减去家庭餐桌上的内容。


我希望我有意义......再次感谢大家的帮助！

First, I want to say I have used this board to resolve many issues that I have had with access and I thank everyone who contributes. I have attempted to resolve my issue myself, but I am stumped and would appreciate any help you can give.

This is a database to make reservations

I am trying to run a query against two tables. The first table (families) has the following fields

Family
Neighborhood
Street
nmbsnglebedsavail
Nmbrdoulbedsavail
nmbrtriplebedsavail

The second table, reservations has

Family
Traveler
Arrivaldate
Departuredate
Type (what kind of room)

I set up the form fine, so when I make a reservation and choose a Single Bed, the number is subtracted from the nmbrsnglebedsavail and it gives me the number of available remaining.

My problem is with the query. How can I query on the remaining beds available? When I use the same formula that I used in the form on the query, the beds remaining is static from one record to the next and doesnt subtract the total beds used according to each family. If i have two travelers (two separate records) visiting "family smith" the query subtracts a "single bed" from the Total Avail but does not carry the subtotal to the next record. So I get the same amount of remaining beds on each record, where in fact, it should total all the "singles" from the reservation table and subtract that from the Family table.

I hope I made sense..and again ..thanks for everyone''s help!

推荐答案

您可以显示一些数据并在此处放置您创建的查询...


您想要相应的床位数量有家人的桌子的信息？


我不明白这里旅行者的角色是什么......


我想您填写预订信息，这些数据也会在您的表格中与家人一起转移？我对吗？


出现在这个结构中的pb是你应该连接桌子看看床应该是免费还是被占用


但是当一家人第二次来？你会做什么？


似乎表中缺少一些东西不是吗？

Hi can you show some data and put here the query that you''ve created...

You want the number of beds accordingly the information of the table with famillies?

I don''t understand what is the role of the traveler here...

I think you fill the information for reservations and this data is transfered also in your table with the families? am i right?

The pb in this tructure that appears is that you should link the tables to see when the beds should be free or occupied

But when a family comes for second time??? What do you will do???

It seems there is something missing in the tables isn''t it?

对不起，我没有我觉得我太清楚了。


我正在为那些负责为旅行者寻找住所的人创建一个数据库....在家庭的房子里。我有一个旅行者表，其中包含有关旅行者的信息。我有一张家庭餐桌，上面有关于家庭的信息 - 包括单人床位数，＃ina双人房（2张床）或三人房（每间房3张床......所以可提供6张床） 。


然后我有一张预订表。我为Family查找字段，为Traveler查找字段，为房间类型（单，双或三）查找字段。当我选择一个家庭（查找）然后选择旅行者（查找），然后选择房间类型（单人，双人等）。我想根据我选择的设置类型扣除可用的床位总数。哦，不止一个旅行者可以在一个家庭的房子里


我希望我没有让你更困惑。


我知道我需要在查询中做某种类型的计算，但我不知道如何根据Family的床位情况进行计算。

Sorry, I didnt think I was too clear,

I am creating a database for someone who is in charge of finding places for Travelers to stay....in the houses of Families. I have a Traveler table with Information about the Traveler. I have a Family table with info about the family - this includes Number of Beds available in a single seting, # ina double setting (2 beds) or a triple room (3 beds per room....so 6 beds could be available).

I then have a table for reservations. I lookup field for the Family, I lookup field for the Traveler and a Look up field for type of room (single, double or triple). When I choose a family (lookup) then a traveler (lookup) , then the type of room (single, double,etc). I would like that the total number of beds available be deducted depending upon the type of setting I select. Oh, and more than one traveler can be at a family''s house

I hope I have not confused you more.

I know I need to do some type of calculatoin in the query , but I do not know how to confinethat calcution according to the Family''s beds availability.

您可以显示一些数据并在此处输入您创建的查询...


你想要床的数量相应的家庭餐桌的信息吗？


我不明白这里旅行者的角色是什么......


我想您填写预订信息，这些数据也会在您的表格中与家人一起转移？我对吗？


出现在这个结构中的pb是你应该连接桌子看看床应该是免费还是被占用


但是当一家人第二次来？你会做什么？


似乎表中缺少一些东西不是吗？

Hi can you show some data and put here the query that you''ve created...

You want the number of beds accordingly the information of the table with famillies?

I don''t understand what is the role of the traveler here...

I think you fill the information for reservations and this data is transfered also in your table with the families? am i right?

The pb in this tructure that appears is that you should link the tables to see when the beds should be free or occupied

But when a family comes for second time??? What do you will do???

It seems there is something missing in the tables isn''t it?

所以您需要的信息是从可用家庭获取信息，以及当前的预订，家庭有什么样的床... ... />

在打开此查询之前，似乎应该引入作为时间间隔的句点？

So the information you need is to get information from the availables families, and current reservations, what families have the wanted kind of bed...

It seem that the period as interval should be introduced before opening this query?

这篇关于多表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！