Any Access Gurus around here?

[arisythila]

Hey guys. Im doing a database for my father. Heres what Im tring to do.

I have to be able to pull reports from the database.(This is not the hard part).

Basically, There is 10 dates within 1 file that I need to search for. Now I just want to display that date. Im not sure how to just display that date, within the file, and not any of the others. I can post pictures if you need a visual.

~Mike

[eshbach]

Make a new Query and switch to SQL mode, then type:

SELECT [date field name in brackets] FROM [table field name in brackets] WHERE (condition).

then save the query and run it.

[arisythila]

Hrmmm, not sure I understand.

SELECT [Corrdate01, Corrdate02] FROM [Contacts] WHERE (??)

Corrdate01-10 are in the Contacts database

I need to display Lastname, Firstname, and Filenumber.

I was never good at SQL.

~Mike

[eshbach]

can you post a screen of your table layout and point out exactly what you want to be displayed?

[arisythila]

Yes.

http://img205.imageshack.us/img205/3688/screen1er2.th.png (http://img205.imageshack.us/my.php?image=screen1er2.png)

I need it to search for the Corr Dates, there is 10 in each file.

There is usually only 1 that will be in the file, but if there is 2, or 3, it needs to pull up each one individually.

Here is roughly what the report will look like.

http://img230.imageshack.us/img230/1489/reportpb9.th.png (http://img230.imageshack.us/my.php?image=reportpb9.png)

IT will look alot nicer, but this is all I have right now.

Thanks for the help.

~Mike

[eshbach]

Well, I meant the table view like this:

link due to size (http://www.baughman-eshbach.com/temp/access.JPG)

[arisythila]

It would take me 13 images to show you that. I have alot of different fields.

Here is a Microsoft Document Imaging

http://linknet.krl.org/~arisythila/access/Contacts0003.mdi

I can also toss this into the directory.

http://linknet.krl.org/~arisythila/access/HANKData.mdb

~Mike

[eshbach]

It would take me 13 images to show you that. I have alot of different fields.

Here is a Microsoft Document Imaging

http://linknet.krl.org/~arisythila/access/Contacts0003.mdi

I can also toss this into the directory.

http://linknet.krl.org/~arisythila/access/HANKData.mdb

~Mike

Ok that's perfect.

From looking at it, i'm assuming the data you want is in the table called "contacts".

Now, SQL is easy. If you want to display corrdate1 for "H & K", your query would be:

SELECT [Corrdate01] FROM [Contacts] WHERE [FirstName] LIKE 'H & K';

is that what you're wanting to do?

[arisythila]

I basically am going to do another form, that will have an beginning date, and an end date. So you can do a

BegDate 02/02/06
EndDate 06/02/06

And it will pull up all the Corrispondance dates from that range.

So lets say, Bob Thompson is set to corrispond for 03/02/06, and 04/02/06

It will want to print up a report that looks like this:

FileNumber.....LastName........FirstName.......... CorrDate
1234.............Thompson.......Bob............... ....03/02/06
1234.............Thompson.......Bob............... ....04/02/06
4321.............Bushea...........Bobby........... .....02/02/06

ect ect ect... (with out the periods, just did that for spacing.)

This is what Im looking for. Since I have more than one corrispondance date. I need it to tell me name of the person, and when the date is, so my father knows when to give the person a call to see if they will renew thier policy.

Does this make more sense?

~Mike

[eshbach]

Ok, I think, to keep this query from getting too complicated, you could make another table with only two fields. One field would be the FileNumber and the other Field would be a CorrDate. You would have multiple entries per FileNumber if needed.

Then, the query would be pretty simple. Something Like:



SELECT
[Contacts.FileNumber], [Contacts.LastName], [Contacts.FirstName], [CorrDates.CorrDate]
FROM
[Contacts], [CorrDates]
WHERE
[Contacts.FileNumber] = [CorrDates.FileNumber]

[arisythila]

Im actually thinking, It maybe easier to do a check box.

Check for Auto, will run the auto corrispondance date
Check for Home, will run the home owners corrispondance date.

ect ect They could all be seperate. Im not sure if that would work tho.

Or how would you impliment Corrdates.Filenumber into the current contacts database?

~Mike

[ahmad]

Now if I understood this right, why couldn't you just do:

SELECT
FileNumber, LastName, FirstName, CorrDate
FROM
Contacts
WHERE
Corrdate >= BegDate AND Corrdate <= EndDate

This should be allowed in MS-SQL. That will return all Contacts within the specified dates with the columns you chose (FileNumber, LastName etc).

[eshbach]

Now if I understood this right, why couldn't you just do:

SELECT
FileNumber, LastName, FirstName, CorrDate
FROM
Contacts
WHERE
Corrdate >= BegDate AND Corrdate <= EndDate

This should be allowed in MS-SQL. That will return all Contacts within the specified dates with the columns you chose (FileNumber, LastName etc).

yea, that would be fine except there are 10 different corrdate fields in the table...

[ahmad]

yea, that would be fine except there are 10 different corrdate fields in the table...

Ah did not know he wanted to get all of them.

If that is the case then I would add another table that contains the FileNumber and all the Corrdates. Ex:

FileNumber .................. CorrDate
1234 .......................... 05/05/2005
1234 .......................... 06/06/2006
4325 .......................... 04/05/2010

And so on. Then you could just make a quick check on the table and get all the contacts. To get the Contact info you could do another lookup, or you could do a JOIN or a NATURAL JOIN with this new table and Contacts, then do your search.

[eshbach]

Ah did not know he wanted to get all of them.

If that is the case then I would add another table that contains the FileNumber and all the Corrdates. Ex:

FileNumber .................. CorrDate
1234 .......................... 05/05/2005
1234 .......................... 06/06/2006
4325 .......................... 04/05/2010

And so on. Then you could just make a quick check on the table and get all the contacts. To get the Contact info you could do another lookup, or you could do a JOIN or a NATURAL JOIN with this new table and Contacts, then do your search.


yep, i agree. that's pretty much exactly what i suggested a few posts up.

[surenw]

I havent read the entire list of follow up posts, but if you are looking for a corresponding records within a range of values

you could just use :

select [columns]
from [table]
where {something} is between ('date1','date2');