NewUser Simple Question

[KKawahata]

I am not now nor am I slated to become a MS Access power user however
my boss has asked me to attempt to create a working database to cut
down on the amount of time we spend updating the cornicopia of excel
spreadsheets we currently use. So far I've made good progress but now
I'm stumped and could use some help.

Right now I am unsure how to make a Query to display all data from my
MainTable that fits a certain criteria (InProgress) that includes the
last payment made including date which is located in another table
(PaymentsTable).

The tables are organized so that most of the data concerning our
projects is located in the MainTable but individual payments made out
to the various projects are tracked in a seperate table. When I try
and make a Query to recall the data I want to be able to see all of the
data from the MainTable as well as the relevent data from the
PaymentTable with blanks in the paymentdates and paymentamt cells if
there haven't been any payments made yet, right now I can either pull
up all of the maintable info without the payment info or I can pull up
the maintable info with the paymentinfo only if it has payment info. I
hope this makes sense I'm currently stumped and I don't really know
where to look online.

 

[Jeff Boyce]

While it would certainly be more helpful to us in helping you if we knew a
bit more about your data structure, there's a good chance that you are using
"equi-" joins between the tables in your query.

Instead, create a new query. Add in the main table and the payments table.
Join the two on the field(s) that are common (i.e., the primary/foreign
key). Now select the join line and change the property of that join to
include all from the main table, and any related records from the payment
table.

Or have I mis-guessed?

 

[mscertified]

What you need is called a LEFT JOIN, that is a join that incudes all the
matching records in your main table whether or not there is match in your
second table.
Look it up in Access help.

David

 

[TC]

It's hard to comment, IMHO, unless you list the main fields in each
table, and most importantly, clearly state the primary key field(s) of
each table.

HTH,
TC

 

[KKawahata]

Thank you very much you all have helped me greatly. This is a great
group.