building relationship between tables

[SharonMee]

Hello All:

I am thinking of building an access database, because in one of my tables in excel, the list keeps on growing, and it's making all my vlookups very slow.

1)I have 3 tables. The first one, "State" contains the raw data. In excel, I concatenate the Invoice # and the Bill Date (=A2&text(B2,"mm/dd/yyyy") in the State table and then in the extra columns I created in the State table, my vlookup inserts the value it gets using the Invoice &Bill Date by searching in the Check table and the Express table. The information is either in the Check Table or the Express table, not both. The check and Express tables also have separate columns for the Invoice and Bill Dates, so in excel, I concatenate these fields in these 2 tables for my vlookup to work.

Question:

Since I am new at building a database, I want to know the best way to search the data in my State table to see if there is the same invoice and bill date in the Check and Wire tables using Access.
I think creating a query will do it, can anyone help in building this query.

2) My State table has lots and lots of duplicates, when building my access database,

Question:
is it better I create a query to eliminate the duplicates first and leave just 1 value of a particular Invoice & Bill date combo but with the most recent one (I can tell which is the most recent data with another date - Transaction in State since date) or do Number (1) first and then eliminate the duplicates?


Thanks so much for helping, I hope I can get this off the ground.

SharonMee

 

[SkipVought]

Sharon,

If you are not familiar, check this out 'Fundamentals Of Relational Database Design'

To find duplicates...
[tt]
Select S.*
From State S
Having Count(YourKeyField)>1
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SharonMee]

Hi Skip, Hi all,

I went through the document you sent to me, and it does not answer these 2 questions:

Do I have to concatenate the 2 columns in my table, in order for my query to work well, or do I have to link the tables by both the Invoice # and the Bill Date. I would guess the former is more appropriate.

My other question is: Do I get rid of the duplicates first (by creating a query to eliminate duplicates) or do I find similar data in the State tables against the Check and Express tables and then eliminate the duplicates.

I just want to make sure I am designing this database as best as possible.

Thanks again.

 

[SkipVought]

You have not posted your query, so I can't determine if it is critical to concatenate. More often you can use a compound Where clause
[tt]
Where Field1=value1 And Field2=value2
[tt]
rather than
[tt]
Where Field1&Field2=value1&Value2
[/tt]
which is frought with problems if the values are numeric.

Regarding duplicates, use a query to report duplicate records. Check them to be absolutely sure that all can be deleted but one. Then run a Delete query. It takes detailed analysis to be sure that you don't delete essential data.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SharonMee]

Hi Skip,
Thanks for being patient:

Some of the following fields exist in my tables:

State table
ID (auto) this is my primary key for this table
Invoice
Bill Date
e.t.c.

Check table
ID (auto) this is my primary key for this table
Invoice
Bill Date
Check Date
Delivery date
e.t.c.

Express table
ID (auto) this is my primary key for this table
Invoice
Bill Date
Check Date
e.t.c.

The check and express tables don't have the same columns, so i can't place the table on top of each other. Also, the information in these tables are not represented the same way.

This means (I think) I need to create 2 separate queries
1) for the common invoice & bill date between the State table and the Check table
2) for the common invoice & bill date between the State table and the Express table

I don't know what you mean by
[tt] what is [tt]?
Where Field1=value1 And Field2=value2

for the where clause, in my sql do you want to me say:

State.Invoice = Check.Invoice AND State.Bill Date = Check.Bill Date

Thanks for helping me again.

 

[SkipVought]

What is the purpose for each table?

In each table how many rows would have have for each of the stated key values?

For instance in the
State Table
Purpose?
# rows per Invoice: One or more than one?
# rows per Bill Date: One or more than one?

It is important to look at structure before pressing on with data.


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SharonMee]

Hi all:

State Table
Purpose? Holds all the invoices processed and waiting to be paid. It is cumulative, contains all the invoices processed since January, and the list keeps growing
# rows per Invoice: One or more than one? Presently has about 22000 rows
# rows per Bill Date: One or more than one? same as above.

The table is laid out like this;

ID Name Invoice Bill Date Transaction Date
1 Hortons 0010023 02/17/2004 02/22/2004


Express table
Purpose? Holds the cheques paid by express
The invoice and bill date here should always be in the State table. Information in this table would not be in the Check table
# rows per Invoice: One or more than one? Presently has about 200 rows
# rows per Bill Date: One or more than one? same as above.

The layout is similar to the State table, but has some other information. But it has the Invoice and Bill date columns.

Check table
Purpose? Holds the cheques paid manually
The invoice and bill date here should always be in the State table. Information in this table would not be in the express table
# rows per Invoice: One or more than one? Presently has about 2200 rows
# rows per Bill Date: One or more than one? same as above.
The layout is similar to the State table, but has some other information. But it has the Invoice and Bill date columns.

Thanks for taking time out to help.

 

[SkipVought]

Sharon,

I asked for the number or rows per invoice and NOT the number of rows per table.

For instance you posted a sample row in the State Table
[tt]
ID Name Invoice Bill Date Transaction Date
1 Hortons 0010023 02/17/2004 02/22/2004

# rows per Invoice: One or more than one?
# rows per Invoice & Bill Date: One or more than one?
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SharonMee]

oohhhh, sorry,

In the state table, the number of rows per invoice varies... for sure. some could be 2, other could be 10...
Same as Invoice&Billdate.

Thanks again

 

[SkipVought]

Here's a query that should select the ID's that need to be deleted in State
[tt]
SELECT Max(s.ID) AS MaxOfID
FROM State AS s
WHERE (((s.Invoice) In (select s.invoice from state s group by s.invoice HAVING count(s.invoice)>1 )));
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[willir]

Skip - Great job on this.

Sharon
Approaching this from another end.

A few things...

I see you are using Invoice number and assign an ID autonumber to your records. You should use one, specifically the ID number.

Example:[tt] Invoice Table
InvID InvNum BillDate CustomerNum
1 A2423 07/01/04 1000
2 A2424 07/01/04 1001
3 A2425 07/02/04 1002[/tt]

I seems like your Express and Check table can be consolidated to one. The only main difference is that payment type. Using one table instead of various tables goes a long way when reconcilliating checks and invoices.

Example:[tt] Payment Table
PayID InvID PayType CheckDate
1 1 EXPRESS 07/01/04
2 2 CHECK 07/10/04
3 3 EXPRESS 07/02/04
[/tt]

By reference the InvID (or whatever field name you use, you have access to to Invoice table -- no need to duplicate the entry.

Hopefully, you see by using a PayType field, you should be able to consolidate much of the information into one table ... and again avoid duplication.

By-the-way, this is part of "normalization" - states one and two per the document link Skip provided.

...Moving on
But what to do about other fields such as the DeliveryDate?

Well, I would have thought the DeliveryDate would sit on the order table, not an invoicing table.

Access is a different type of tool than a spreadsheet program. It is a very powerful tool because it allows you to remove redundancy (duplication of work), and minimize maintenance. In a sense it is like your VLookup tables to the nth degree.

You should probably spend a bit of time going over the link Skip provided. In a sense, I suspect you are trying to make Access work like Excel, and you may not be taking advantage of the power of Access and a relational database.

I am still a little confused about your term "State" when it looks like an invoice table.

Let us know if you want some direction in designing your tables so that they are properly normalized, and the relationships are properly created.

Richard

 

[BNPMike]

SkipVought

Select S.*
From State S
Having Count(YourKeyField)>1

Using HAVING without eg COUNT doesn't look right to me, and I couldn't get it to work either...