Query Speed

[ErikFM via AccessMonster.com]

Hi there!

I am setting up a database for our branch offices to use in a distributed
(FE/BE) format, and am wondering which would be better for speed:

1. A distinct table for each branch, with a UNION ALL query forming the basis
for regional/countrywide reports.

2. One massive data table that queries would launch SQL against.

Thanks!

 

[Jerry Whittle]

One table - please! Union queries are slow and inefficient. Union All is
somewhat faster but there's always a chance of duplicate data being retrieved.

 

[ErikFM via AccessMonster.com]

Thanks, Jerry!

One question... what if I want unique receipt numbers by branch?

For instance

Receipt Number Branch

1 Houston
2 Houston
3 Houston
1 Dallas
2 Dallas
3 Dallas

Should I have an AutoNumber on the Master Table, and then in the form have
some VBA alchemy to scan for the MAX(Receipt Number) WHERE Branch = [Branch],
then set the next Receipt # to that number +1?

Auto Number Receipt Number Branch

1 1 Houston
2 2 Houston
3 3 Houston
4 1 Dallas
5 2 Dallas
6 3 Dallas

One table - please! Union queries are slow and inefficient. Union All is
somewhat faster but there's always a chance of duplicate data being retrieved.

Hi there!

[quoted text clipped - 7 lines]

Thanks!

 

[Jerry Whittle]

The autonumber PK is a good idea. Adding +1 to the highest number sounds like
the way to go. One fly in the ointment is if multiple people can be adding
similar records at the same time.

Depending on when the +1 is figured, like at the start of the record entry,
more than one record could have the same Branch/Receipt Number. You
probably also want to make the Branch and Receipt Number field combination a
unique index.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks, Jerry!

One question... what if I want unique receipt numbers by branch?

For instance

Receipt Number Branch

1 Houston
2 Houston
3 Houston
1 Dallas
2 Dallas
3 Dallas

Should I have an AutoNumber on the Master Table, and then in the form have
some VBA alchemy to scan for the MAX(Receipt Number) WHERE Branch = [Branch],
then set the next Receipt # to that number +1?

Auto Number Receipt Number Branch

1 1 Houston
2 2 Houston
3 3 Houston
4 1 Dallas
5 2 Dallas
6 3 Dallas

One table - please! Union queries are slow and inefficient. Union All is
somewhat faster but there's always a chance of duplicate data being retrieved.

Hi there!

[quoted text clipped - 7 lines]

Thanks!

--



.

 

[ErikFM via AccessMonster.com]

Thanks, Gerry!

I did something similar in house for small team of 4 analysts... time to ramp
up to the tune of 40 branch offices! =)

The autonumber PK is a good idea. Adding +1 to the highest number sounds like
the way to go. One fly in the ointment is if multiple people can be adding
similar records at the same time.

Depending on when the +1 is figured, like at the start of the record entry,
more than one record could have the same Branch/Receipt Number. You
probably also want to make the Branch and Receipt Number field combination a
unique index.

Thanks, Jerry!

[quoted text clipped - 31 lines]

 

[ErikFM via AccessMonster.com]

Thanks, Jerry!

I did something similar in house for small team of 4 analysts... time to ramp
up to the tune of 40 branch offices! =)

The autonumber PK is a good idea. Adding +1 to the highest number sounds like
the way to go. One fly in the ointment is if multiple people can be adding
similar records at the same time.

Depending on when the +1 is figured, like at the start of the record entry,
more than one record could have the same Branch/Receipt Number. You
probably also want to make the Branch and Receipt Number field combination a
unique index.

Thanks, Jerry!

[quoted text clipped - 31 lines]