Views

[mhaccessnov]

Hello,

I hope to explain this in such a way that no ambiguity is present afterwards. Ok, I have a database that has two tables in it with one relationship (1 to many).

However, I am trying to get a view... where the main information that was imported into "Table #1" is all there with no new records (just the static list of 500 records) with a couple other fields showing who has or owns a specific item...

So, one view of information from table x with some identifying information as to who has that item.

Now, everthing I am trying is only showing me the information with "those other fields associated" (who owns it, yada yada)... However, I am not seeing the whole list. If I were to assign every item a person then this query would show me the list ... I want a view that can show me each item and who has it (more static in nature)... (not duplicate item numbers just the most current view of who has it... but complete list)...

:banghead:


Thanks,
Mike T

 

[ByteMyzer]

Let's say your two tables are tblPerson and tblItem, and let's say the joining field is PersonID. Finally, let's say that you are trying to create a view of tblItem, with some related information from tblPerson. In that case, your query would probably be something like the following:

SELECT ...
FROM tblItem
INNER JOIN tblPerson ON tblItem.PersonID = tblPerson.PersonID

However, as you have no doubt discovered, this does not let you see the records from tblItem which have not been assigned to a person.

You will need to revise your query to something like the following:

SELECT ...
FROM tblItem
[COLOR="Red"][B]LEFT[/B][/COLOR] JOIN tblPerson ON tblItem.PersonID = tblPerson.PersonID

 

[mhaccessnov]

Hello ByteMyzer,

So, how would I pick the most current "data" or date of item borrowed by said person? I would think with MAX(Date) but not sure how to do that SQL Syntax correctly... In either case, thanks so much for the help.