developing a system for work and need some help please.

[themastertaylor]

Hi, I work for a construction company and part of my job is sourcing
materials. currently we have a spreadsheet based system whereby each
site has a worksheet in the workbook with the standard types of stone
in the first column, then across the top we have the main 6 or 7
suppliers and input their price for each material into the grid.

I want to develop a system in access basically so that i can search
for situations where a supplier hasn't quoted for a site, produce
reports of the prices we pay for a certain material on different sites
etc. whilst still being able to produce a single page comparison as i
could in excel.

I've not really used access since my a level coursework at school and
had a bash at it earlier. i worked on the basis of 3 tables one for
the quotes with all the materials in, and the quote number as the
primary key, one for the sites with the name and job reference number
(p key) and a link table in the middle with the 2 primary keys.
however having created the relationshipsi couldn't have more than one
quote associated with a site.

any pointers on a better method of achieving this would be
appreciated. in addition to make things a little trickier the same
suppliers provide both dry stone and asphalt products - the asphalt is
usually under a different quote number and sourced from a different
depot as such it will probably need a seperate table but be displayed
on my final comparison report underneath the drystone from the same
supplier.

[Keith Wilby]

<th************ *@hotmail.comwr ote in message
news:11******** **************@ y66g2000hsf.goo glegroups.com.. .

>
I've not really used access since my a level coursework at school and
had a bash at it earlier. i worked on the basis of 3 tables one for
the quotes with all the materials in, and the quote number as the
primary key, one for the sites with the name and job reference number
(p key) and a link table in the middle with the 2 primary keys.

I presume you mean two FKs in the join table.

however having created the relationshipsi couldn't have more than one
quote associated with a site.

The three table setup should allow for a M:M relationship but do you really
have a M:M? A site can have many quotes but can a quote cover many sites?
I would have thought you have a 1:M - one site with many quotes, so if you
set up a 1:M between your site and quotes tables, that should do the trick.

>
any pointers on a better method of achieving this would be
appreciated. in addition to make things a little trickier the same
suppliers provide both dry stone and asphalt products - the asphalt is
usually under a different quote number and sourced from a different
depot as such it will probably need a seperate table but be displayed
on my final comparison report underneath the drystone from the same
supplier.

You can use the same table but flag the records accordingly, dry stone or
asphalt.

HTH - Keith.
www.keithwilby.com

[rkc]

th************* @hotmail.com wrote:

Hi, I work for a construction company and part of my job is sourcing
materials. currently we have a spreadsheet based system whereby each
site has a worksheet in the workbook with the standard types of stone
in the first column, then across the top we have the main 6 or 7
suppliers and input their price for each material into the grid.

I want to develop a system in access basically so that i can search
for situations where a supplier hasn't quoted for a site, produce
reports of the prices we pay for a certain material on different sites
etc. whilst still being able to produce a single page comparison as i
could in excel.

I've not really used access since my a level coursework at school and
had a bash at it earlier. i worked on the basis of 3 tables one for
the quotes with all the materials in, and the quote number as the
primary key, one for the sites with the name and job reference number
(p key) and a link table in the middle with the 2 primary keys.
however having created the relationshipsi couldn't have more than one
quote associated with a site.

any pointers on a better method of achieving this would be
appreciated. in addition to make things a little trickier the same
suppliers provide both dry stone and asphalt products - the asphalt is
usually under a different quote number and sourced from a different
depot as such it will probably need a seperate table but be displayed
on my final comparison report underneath the drystone from the same
supplier.

Look at the Northwind sample database that has shipped with every
version of Access since at least 97. You have products, suppliers
orders, and customers. The fact that a customer(site) can place more
than one order doesn't make it complicated.

[themastertaylor]

On 19 Mar, 11:09, "Keith Wilby" <[e-mail] wrote:

<themastertay.. [e-mail] ote in message

news:11******** **************@ y66g2000hsf.goo glegroups.com.. .

I've not really used access since my a level coursework at school and
had a bash at it earlier. i worked on the basis of 3 tables one for
the quotes with all the materials in, and the quote number as the
primary key, one for the sites with the name and job reference number
(p key) and a link table in the middle with the 2 primary keys.

I presume you mean two FKs in the join table.

however having created the relationshipsi couldn't have more than one
quote associated with a site.

The three table setup should allow for a M:M relationship but do you really
have a M:M? A site can have many quotes but can a quote cover many sites?
I would have thought you have a 1:M - one site with many quotes, so if you
set up a 1:M between your site and quotes tables, that should do the trick.

any pointers on a better method of achieving this would be
appreciated. in addition to make things a little trickier the same
suppliers provide both dry stone and asphalt products - the asphalt is
usually under a different quote number and sourced from a different
depot as such it will probably need a seperate table but be displayed
on my final comparison report underneath the drystone from the same
supplier.

You can use the same table but flag the records accordingly, dry stone or
asphalt.

HTH - Keith.www.keithwilby.com

thanks for the help got the majority of the system working, i'm now
just struggling to get my report in a tabular form. basically the
idea is to get the report to fit on one page so that it can be
laminated and sent off to the site in question. i can produce a
report that has a new page for each supplier but obviously this isn't
what i need. i don't seem to be able to insert a table or place the
fields for the second supplier - if i copy paste i'm just getting the
first supplier duplicated. probably something really simple!

[themastertaylor]

sorry forgot to say i tried the tabular report option but i have too
many fields for it to be generated for me

[Keith Wilby]

<th************ *@hotmail.comwr ote in message
news:11******** **************@ p15g2000hsd.goo glegroups.com.. .

>
thanks for the help got the majority of the system working,

Excellent.

i'm now
just struggling to get my report in a tabular form. basically the
idea is to get the report to fit on one page so that it can be
laminated and sent off to the site in question. i can produce a
report that has a new page for each supplier but obviously this isn't
what i need. i don't seem to be able to insert a table or place the
fields for the second supplier - if i copy paste i'm just getting the
first supplier duplicated. probably something really simple!

Hmm ... I'm not sure what could be wrong but check that the sorting and
grouping isn't causing it. Also make sure that the detail area of the
report is as small as is feasible.

Regards,
Keith.
www.keithwilby.com

[themastertaylor]

On 19 Mar, 16:06, "Keith Wilby" <[e-mail] wrote:

<themastertay.. [e-mail] ote in message

news:11******** **************@ p15g2000hsd.goo glegroups.com.. .

thanks for the help got the majority of the system working,

Excellent.

i'm now
just struggling to get my report in a tabular form. basically the
idea is to get the report to fit on one page so that it can be
laminated and sent off to the site in question. i can produce a
report that has a new page for each supplier but obviously this isn't
what i need. i don't seem to be able to insert a table or place the
fields for the second supplier - if i copy paste i'm just getting the
first supplier duplicated. probably something really simple!

Hmm ... I'm not sure what could be wrong but check that the sorting and
grouping isn't causing it. Also make sure that the detail area of the
report is as small as is feasible.

Regards,
Keith.www.keithwilby.com

I've made a smaller query to establish what the problem is and i think
that it stems from the orientation. my quote table has a lot of
materials on and as such i need them to be listed down the length of
my report, with the suppliers across the top and prices where they
intersect.

what the tabular and column reports are producing is the materials
across the top then the 4 suppliers and their prices along the rows
below. to make it work i need to flip them so that i can print onto
a4 as opposed to a toilet roll - any ideas?

[Larry Linson]

Two methods for "flipping" columns-to-rows in Access are "Crosstab Queries"
and "Pivot Tables". There is good Help for Crosstab Queries in Access 2003,
and in some much earlier versions. I haven't had much to do with the Help
for Pivot Tables, but it's worth a try -- the content in Access 2003 Help is
much better than the previous two versions, but because it is "online
primary", best if you have a high-speed online connection.

It's possible that the Crosstab Query as a RecordSource for a Report, may be
just what you need... lots of rows, and few enough columns to fit on a page.

Larry Linson
Microsoft Access MVP

<th************ *@hotmail.comwr ote in message
news:11******** **************@ d57g2000hsg.goo glegroups.com.. .

On 19 Mar, 16:06, "Keith Wilby" <[e-mail] wrote:

><themastertay. [e-mail] rote in message

news:11******* *************** @p15g2000hsd.go oglegroups.com. ..

thanks for the help got the majority of the system working,

Excellent.

i'm now
just struggling to get my report in a tabular form. basically the
idea is to get the report to fit on one page so that it can be
laminated and sent off to the site in question. i can produce a
report that has a new page for each supplier but obviously this isn't
what i need. i don't seem to be able to insert a table or place the
fields for the second supplier - if i copy paste i'm just getting the
first supplier duplicated. probably something really simple!

Hmm ... I'm not sure what could be wrong but check that the sorting and
grouping isn't causing it. Also make sure that the detail area of the
report is as small as is feasible.

Regards,
Keith.www.keithwilby.com

I've made a smaller query to establish what the problem is and i think
that it stems from the orientation. my quote table has a lot of
materials on and as such i need them to be listed down the length of
my report, with the suppliers across the top and prices where they
intersect.

what the tabular and column reports are producing is the materials
across the top then the 4 suppliers and their prices along the rows
below. to make it work i need to flip them so that i can print onto
a4 as opposed to a toilet roll - any ideas?